Converting XML data dump to SQL

Converting XML data dump to SQL

Forgive me if this is a naive message or treated elsewhere. I have a
broad undersanding of XML concepts, but am pretty shallow when it
comes to details.

I have an XML data dump of a products database. I would like to create
a relational database from it (e.g., in  MySQL,  MS SQL Server 2000 or
2005, or even Access, I don't much care.)

Is this a trivial task, or moderate? (I can conceive how to do it in a
program, so I know it can't be  enormously difficult)  Are there ready
made tools available (at low or no cost)  to do this, or do I need to
write a program? I have Visual Studio 2005 available, and am
proficient in VB and C#.  I know that with a few lines of code, I can
create an XML reader and use it to infer the XML Schema.  Is there an
easy way to use that to generate the SQL Data Definition statements
(CREATE TABLE, etc.) to create the database tables?

The data appears to have about half a dozen tables in it that would
have to be linked by foreign keys.  If there are no ready made tools
to do this, is there an easy way to read through the XML records and
write the data out to the SQL tables and properly link all the
entries?

And if this is not the right group, please feel free to point me to
the most appropriate one.

Thanks in advance for your assistance.

date: Wed, 25 Jun 2008 17:02:59 -0700 (PDT) author: daveh551

Re: Converting XML data dump to SQL

"daveh551"  wrote in message 
news:b11a6560-8bc1-432a-9b27-d55b5a58287b@l42g2000hsc.googlegroups.com...
> Forgive me if this is a naive message or treated elsewhere. I have a
> broad undersanding of XML concepts, but am pretty shallow when it
> comes to details.
>
> I have an XML data dump of a products database. I would like to create
> a relational database from it (e.g., in  MySQL,  MS SQL Server 2000 or
> 2005, or even Access, I don't much care.)
>
> Is this a trivial task, or moderate? (I can conceive how to do it in a
> program, so I know it can't be  enormously difficult)  Are there ready
> made tools available (at low or no cost)  to do this, or do I need to
> write a program? I have Visual Studio 2005 available, and am
> proficient in VB and C#.  I know that with a few lines of code, I can
> create an XML reader and use it to infer the XML Schema.  Is there an
> easy way to use that to generate the SQL Data Definition statements
> (CREATE TABLE, etc.) to create the database tables?
>
> The data appears to have about half a dozen tables in it that would
> have to be linked by foreign keys.  If there are no ready made tools
> to do this, is there an easy way to read through the XML records and
> write the data out to the SQL tables and properly link all the
> entries?
>
> And if this is not the right group, please feel free to point me to
> the most appropriate one.
>
> Thanks in advance for your assistance.
It depends somewhat on the schema of the files you have although they could 
be transformed first if necessary.
SQL Server has XMLBulkLoad and Access has an import from XML facility.
I'm not sure if XMLBulkLoad runs in pre-2005 versions though.


-- 

Joe Fawcett (MVP - XML)
http://joe.fawcett.name

date: Thu, 26 Jun 2008 08:38:18 +0100 author: Joe Fawcett am

Re: Converting XML data dump to SQL

"daveh551"  wrote in message
news:b11a6560-8bc1-432a-9b27-d55b5a58287b@l42g2000hsc.googlegroups.com...
> Forgive me if this is a naive message or treated elsewhere. I have a
> broad undersanding of XML concepts, but am pretty shallow when it
> comes to details.
>
> I have an XML data dump of a products database. I would like to create
> a relational database from it (e.g., in  MySQL,  MS SQL Server 2000 or
> 2005, or even Access, I don't much care.)
>
> Is this a trivial task, or moderate? (I can conceive how to do it in a
> program, so I know it can't be  enormously difficult)

> Are there ready made tools available (at low or no cost)  to do this,


Take a look at  Log Parser?   Looks like you should be able to do a conversion
in a one-liner with it from either cmd.exe or PowerShell.

<title>ScriptCenter Tools: New Features of Log Parser 2.2</title>
http://www.microsoft.com/technet/scriptcenter/tools/logparser/lpfeatures.mspx

<quotes>
New Input Formats
       XML - Reads XML files (requires the Microsoft® XML Parser (MSXML))


SQL Engine Improvements

</quotes>


> or do I need to
> write a program? I have Visual Studio 2005 available, and am
> proficient in VB and C#.  I know that with a few lines of code, I can
> create an XML reader and use it to infer the XML Schema.  Is there an
> easy way to use that to generate the SQL Data Definition statements
> (CREATE TABLE, etc.) to create the database tables?
>

> The data appears to have about half a dozen tables in it that would
> have to be linked by foreign keys.  If there are no ready made tools
> to do this, is there an easy way to read through the XML records and
> write the data out to the SQL tables and properly link all the
> entries?


Oh.   "half a dozen tables"?   That sounds more complicated...   ; )


>
> And if this is not the right group, please feel free to point me to
> the most appropriate one.
>
> Thanks in advance for your assistance.


Good luck

Robert Aldwinckle
---

date: Thu, 26 Jun 2008 17:26:04 -0400 author: Robert Aldwinckle

Re: Converting XML data dump to SQL

Thanks to both of you for your responses. There is good information
there, but I don't think it really does quite what I want.

Here's a somewhat truncated version of one of the records from the
dump I'm trying to work with:

<products>
	<product>
		<sku>SN3030</sku>
		<name>30" 030 Snake Chain Necklace</name>
		<kind>basic</kind>
		<summary><![CDATA[]]></summary>
		<weight>5.0</weight>
		<description><![CDATA[]]></description>

                ...

		<cost>13.8</cost>
		<price>
			<priceset>US</priceset>
			<price>41.4</price>
			<saleprice>41.4</saleprice>
			<suggestedprice>41.4</suggestedprice>
			<available>true</available>
			<featured>false</featured>
			<onsale>false</onsale>
			<taxexempt>false</taxexempt>
			<lineitemdiscount>false</lineitemdiscount>
			<shippingexempt>false</shippingexempt>
			<fixedquantity>Fixed Quantity</fixedquantity>
			<usemap>false</usemap>
		</price>
		<category>
			<path>Necklaces / Long Necklaces</path>
			<position>20</position>
		</category>
		<category>
			<path>Chains</path>
			<position>70</position>
		</category>
		<category>
			<path>Chains / Silver Chains</path>
			<position>10</position>
		</category>
		<category>
			<path>Necklaces</path>
			<position>60</position>
		</category>
		<downloadurl></downloadurl>
		<ignoredescriptionlines>false</ignoredescriptionlines>
		<inclusivevariationconflicts></inclusivevariationconflicts>
		<drawmultiplevariations>false</drawmultiplevariations>
	</product>

BTW, this is the export of StoreOnline product database.  It would be
desireable to be able to write the database back out in the same
format, though that is a secondary goal.

You see that there is a parent datarow, product, with some child rows,
including price, and categories, which may occur multiple times per
product row.  I tried doing an XML import into Access, and it did
correctly read in all the tables, but it did nothing to establish any
foreign key relations between the rows.  So, for example, I have an
entry in the price table for $41.40,etc., but nothing to tie that
entry to product SKU SN3030.  Also, some of the tables, such as
category, only have a limited number of entries, but in parsing the
XML input, it creates one row in the category table every time that
entry occurs, and, again, of course, nothing linking any of those rows
to the product that they represent.

Just for point of reference, the tables represented here are product,
price, category, variation (such as size, color, etc.) (which also has
another child table called choice), feature, and link.

Since the XML parser seems to do a pretty good job of inferring the
schema, I was hoping there might be a program around that I could feed
in the XML file and get back something analogous to a MySQLDump output
- a set of SQL CREATE TABLE statements, each followed by INSERT INTO
table...

I guess that was hoping for a little too much! LOL I guess I'll have
to use the schema that it created and the XML parser to write a
program to read the data and write it out to the DB, creating the
Foreign keys along the way.

date: Tue, 1 Jul 2008 14:31:11 -0700 (PDT) author: daveh551