XML datas to MySQL database

Where to find a comprehensive document on how take datas from XML and put
them in a Mysql Database.
I've many sites sending us XML files. We have to parse datas and save those
datas on Mysql.

The problem is that we have many different file format (each company sends
us it's own XML structure).

How to provide a simple way to get those datas in Mysql, without having to
create a new PHP script for every new site's XML ?

Please help.

Bob

re: XML datas to MySQL database

Bob Bedford wrote:[color=blue]
> Where to find a comprehensive document on how take datas from XML and
> put them in a Mysql Database.
> I've many sites sending us XML files. We have to parse datas and save
> those datas on Mysql.
>
> The problem is that we have many different file format (each company
> sends us it's own XML structure).
>
> How to provide a simple way to get those datas in Mysql, without having
> to create a new PHP script for every new site's XML ?
>
> Please help.
>
> Bob
>[/color]

Bob,

If the XML formats are widely different, you'll probably have to create
different PHP scripts for each one.

The alternative is to build one huge script which would handle all possible
formats. However, that quickly becomes very difficult to manage. If any of the
companies changes their XML format, you'd have to worry about the changes to
your huge script would affect everyone else.

Sometimes it's just simpler to handle different things in different scripts.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

re: XML datas to MySQL database

"Jerry Stuckle" <jstucklex@attglobal.net> a écrit dans le message de news:
7fedndgj9MKTUkbfRVn-sQ@comcast.com...[color=blue]
> Bob Bedford wrote:[color=green]
>> Where to find a comprehensive document on how take datas from XML and put
>> them in a Mysql Database.
>> I've many sites sending us XML files. We have to parse datas and save
>> those datas on Mysql.
>>
>> The problem is that we have many different file format (each company
>> sends us it's own XML structure).
>>
>> How to provide a simple way to get those datas in Mysql, without having
>> to create a new PHP script for every new site's XML ?
>>
>> Please help.
>>
>> Bob
>>[/color]
>
> Bob,
>
> If the XML formats are widely different, you'll probably have to create
> different PHP scripts for each one.
>
> The alternative is to build one huge script which would handle all
> possible formats. However, that quickly becomes very difficult to manage.
> If any of the companies changes their XML format, you'd have to worry
> about the changes to your huge script would affect everyone else.
>
> Sometimes it's just simpler to handle different things in different
> scripts.[/color]
Thanks for the advice.
Anyway for my first scripts I had to manage everything manually. Its' there
any class or code that takes out all datas from a XML and put it in a
simpler structure ? maybe an array, that let me create a simpler script ?

Bob

re: XML datas to MySQL database

Bob Bedford wrote:[color=blue]
>
> Thanks for the advice.
> Anyway for my first scripts I had to manage everything manually. Its'
> there any class or code that takes out all datas from a XML and put it
> in a simpler structure ? maybe an array, that let me create a simpler
> script ?[/color]

Hi Bob,

You could use a dtd parser and then dynamically create a mysql table
with that particular structure. Then after that you can put the data
from the xml files in the right mysql table which belongs to its dtd.
You'd get a column for each xml element. Handling nested xml data would
be a little tougher though.

--
http://www.phpforums.nl

re: XML datas to MySQL database

"Peter van Schie" <vanschie.peter@gmail.com> a écrit dans le message de
news: 42dbdb98$0$14043$dbd4b001@news.wanadoo.nl...[color=blue]
> Bob Bedford wrote:[color=green]
>>
>> Thanks for the advice.
>> Anyway for my first scripts I had to manage everything manually. Its'
>> there any class or code that takes out all datas from a XML and put it in
>> a simpler structure ? maybe an array, that let me create a simpler script
>> ?[/color]
>
> Hi Bob,
>
> You could use a dtd parser and then dynamically create a mysql table with
> that particular structure. Then after that you can put the data from the
> xml files in the right mysql table which belongs to its dtd.
> You'd get a column for each xml element. Handling nested xml data would be
> a little tougher though.[/color]

Thanks for reply Peter,

This is the problem, I've this structure (simplified)

<data1>
<group1>
<field1>
</field1>
<field2>
</field2>
</group1>
<group2>
<field1>
</field1>
</group2>
</data1>

and of course, group number is undefined and field number in each group is
also undefined.
What's the best approach ? for now I use xml_parser_create and check
manually everything.

but if I find a better way to handle datas, probably It will be simpler to
parse and save datas in the database,
Bob

re: XML datas to MySQL database

Bob Bedford wrote:
[color=blue]
> Thanks for reply Peter,
>
> This is the problem, I've this structure (simplified)
>
> <data1>
> <group1>
> <field1>
> </field1>
> <field2>
> </field2>
> </group1>
> <group2>
> <field1>
> </field1>
> </group2>
> </data1>
>
> and of course, group number is undefined and field number in each group
> is also undefined.
> What's the best approach ? for now I use xml_parser_create and check
> manually everything.
>
> but if I find a better way to handle datas, probably It will be simpler
> to parse and save datas in the database,[/color]

Hi Bob,

Well, that's what I meant by nested data. That is a lot trickier.
There are database engines that support native xml data on which you can
use XQuery queries. An example is eXist (http://exist.sourceforge.net/).
But I think you mentioned you want to use MySQL to store the data right?
I did read something about MySQL 5 going to support XPath too, but
that's not 100% sure yet last time I checked.
So for now it's a tough problem to solve with MySQL I'm afraid.

--
http://www.phpforums.nl

re: XML datas to MySQL database

"Peter van Schie" <vanschie.peter@gmail.com> a écrit dans le message de
news: 42dc08cd$0$76226$dbd45001@news.wanadoo.nl...[color=blue]
> Bob Bedford wrote:
>[color=green]
>> Thanks for reply Peter,
>>
>> This is the problem, I've this structure (simplified)
>>
>> <data1>
>> <group1>
>> <field1>
>> </field1>
>> <field2>
>> </field2>
>> </group1>
>> <group2>
>> <field1>
>> </field1>
>> </group2>
>> </data1>
>>
>> and of course, group number is undefined and field number in each group
>> is also undefined.
>> What's the best approach ? for now I use xml_parser_create and check
>> manually everything.
>>
>> but if I find a better way to handle datas, probably It will be simpler
>> to parse and save datas in the database,[/color]
>
> Hi Bob,
>
> Well, that's what I meant by nested data. That is a lot trickier.
> There are database engines that support native xml data on which you can
> use XQuery queries. An example is eXist (http://exist.sourceforge.net/).
> But I think you mentioned you want to use MySQL to store the data right?
> I did read something about MySQL 5 going to support XPath too, but that's
> not 100% sure yet last time I checked.
> So for now it's a tough problem to solve with MySQL I'm afraid.
>
> --
> http://www.phpforums.nl[/color]
Thanks for help Peter,

Probably there is no way to put datas from a XML file directly in a Mysql
database, but does it exist a manner to put datas in an array automatically,
or better in a structure ?

The dream is to have a function that reads a XML file and put it in an array
then use msqlqueries like
insert into mytable
values($struct["group1"]["field1"],$struct["group2"]["field2"]....
does it exists in PHP ?[color=blue]
>[/color]

re: XML datas to MySQL database

Check out the various XML code snippets on the Zend site at
http://www.zend.com/codex.php?CID=15. There may be something there that
satisfies your needs.

--
Tony Marston

http://www.tonymarston.net


"Bob Bedford" <bedford1@notforspammershotmail.com> wrote in message
news:42dcaa9a$0$1162$5402220f@news.sunrise.ch...[color=blue]
>
> "Peter van Schie" <vanschie.peter@gmail.com> a écrit dans le message de
> news: 42dc08cd$0$76226$dbd45001@news.wanadoo.nl...[color=green]
>> Bob Bedford wrote:
>>[color=darkred]
>>> Thanks for reply Peter,
>>>
>>> This is the problem, I've this structure (simplified)
>>>
>>> <data1>
>>> <group1>
>>> <field1>
>>> </field1>
>>> <field2>
>>> </field2>
>>> </group1>
>>> <group2>
>>> <field1>
>>> </field1>
>>> </group2>
>>> </data1>
>>>
>>> and of course, group number is undefined and field number in each group
>>> is also undefined.
>>> What's the best approach ? for now I use xml_parser_create and check
>>> manually everything.
>>>
>>> but if I find a better way to handle datas, probably It will be simpler
>>> to parse and save datas in the database,[/color]
>>
>> Hi Bob,
>>
>> Well, that's what I meant by nested data. That is a lot trickier.
>> There are database engines that support native xml data on which you can
>> use XQuery queries. An example is eXist (http://exist.sourceforge.net/).
>> But I think you mentioned you want to use MySQL to store the data right?
>> I did read something about MySQL 5 going to support XPath too, but that's
>> not 100% sure yet last time I checked.
>> So for now it's a tough problem to solve with MySQL I'm afraid.
>>
>> --
>> http://www.phpforums.nl[/color]
> Thanks for help Peter,
>
> Probably there is no way to put datas from a XML file directly in a Mysql
> database, but does it exist a manner to put datas in an array
> automatically, or better in a structure ?
>
> The dream is to have a function that reads a XML file and put it in an
> array then use msqlqueries like
> insert into mytable
> values($struct["group1"]["field1"],$struct["group2"]["field2"]....
> does it exists in PHP ?[color=green]
>>[/color]
>
>[/color]

re: XML datas to MySQL database

Bob Bedford wrote:
[color=blue]
> Thanks for help Peter,
>
> Probably there is no way to put datas from a XML file directly in a
> Mysql database, but does it exist a manner to put datas in an array
> automatically, or better in a structure ?
>
> The dream is to have a function that reads a XML file and put it in an
> array then use msqlqueries like
> insert into mytable
> values($struct["group1"]["field1"],$struct["group2"]["field2"]....
> does it exists in PHP ?[/color]

Hi Bob,

I see where you want to go, but I don't know of any php library or class
that does just that. Maybe the link Tony posted is helpful. Keep us posted.

--
http://www.phpforums.nl

re: XML datas to MySQL database

You can also try searching for XML at http://www.phpclasses.org/

--
Tony Marston

http://www.tonymarston.net


"Peter van Schie" <vanschie.peter@gmail.com> wrote in message
news:42dd885e$0$93977$dbd45001@news.wanadoo.nl...[color=blue]
> Bob Bedford wrote:
>[color=green]
>> Thanks for help Peter,
>>
>> Probably there is no way to put datas from a XML file directly in a Mysql
>> database, but does it exist a manner to put datas in an array
>> automatically, or better in a structure ?
>>
>> The dream is to have a function that reads a XML file and put it in an
>> array then use msqlqueries like
>> insert into mytable
>> values($struct["group1"]["field1"],$struct["group2"]["field2"]....
>> does it exists in PHP ?[/color]
>
> Hi Bob,
>
> I see where you want to go, but I don't know of any php library or class
> that does just that. Maybe the link Tony posted is helpful. Keep us
> posted.
>
> --
> http://www.phpforums.nl[/color]