MS SQL XML - Import XML file into Multiple SQL Tables

Author

Import XML file into Multiple SQL Tables

guxu@hotmail.com

2005-09-26, 11:23 am

I have a XML file which contains data for multiple tables. It looks
something like this:
<table1>
<tbl1_tag1>...</tbl1_tag1>
<tbl1_tag2>...</tbl1_tag2>
<table2>
<tbl2_tag1>...</tbl2_tag1>
<tbl2_tag2>...</tbl2_tag2>
</table2>
<tbl1_tag3>...</tbl1_tag3>
<tbl1_tag4>...</tbl1_tag4>
<table3>
<tbl3_tag1>...</tbl3_tag1>
<tbl3_tag2>...</tbl3_tag2>
<tbl3_tag3>...</tbl3_tag3>
</table3>
<tbl1_tag4>...</tbl1_tag4>
<tbl1_tag5>...</tbl1_tag5>
</table1>

I need to import the data into 3 differernt SQL database tables with
table colums like:
For table 1: tbl1_tag1, tbl1_tag2, tbl1_tag3, tbl1_tag4, tbl1_tag5
For table 2: tbl2_tag1, tbl2_tag2
For table 3: tbl3_tag1, tbl3_tag2, tbl3_tag3

I tried to use the SqlDataAdpater, but could not get the right data.
Any idea how to solve this problem?

Thanks very much.

Michael Rys [MSFT]

2005-09-27, 3:24 am

I would recommend to look at either the SQLXML 3.0 XML Bulkload that
requires that you generate an XML Schema and add annotations but is the most
performant way or OpenXML that uses a path based approach but takes a bit
more time.

Here is the OpenXML like approach:

declare @h int;

exec sp_xml_preparedocume
nt @h output, N'<table1>

<tbl1_tag1>...</tbl1_tag1>

<tbl1_tag2>...</tbl1_tag2>

<table2>

<tbl2_tag1>...</tbl2_tag1>

<tbl2_tag2>...</tbl2_tag2>

</table2>

<tbl1_tag3>...</tbl1_tag3>

<tbl1_tag4>...</tbl1_tag4>

<table3>

<tbl3_tag1>...</tbl3_tag1>

<tbl3_tag2>...</tbl3_tag2>

<tbl3_tag3>...</tbl3_tag3>

</table3>

<tbl1_tag4>...</tbl1_tag4>

<tbl1_tag5>...</tbl1_tag5>

</table1>'

select * into table1

from OpenXML(@h, '/table1', 2) WITH

(tbl1_tag1 nvarchar(40),

tbl1_tag2 nvarchar(40),

tbl1_tag3 nvarchar(40),

tbl1_tag4 nvarchar(40),

tbl1_tag5 nvarchar(40))

select * from table1

select * into table2

from OpenXML(@h, '/table1/table2', 2) WITH

(tbl2_tag1 nvarchar(40),

tbl2_tag2 nvarchar(40))

select * from table2

select * into table3

from OpenXML(@h, '/table1/table3', 2) WITH

(tbl3_tag1 nvarchar(40),

tbl3_tag2 nvarchar(40),

tbl3_tag3 nvarchar(40))

select * from table3

exec sp_xml_removedocumen
t @h

Best regards
Michael

<guxu@hotmail.com> wrote in message
news:1127744803.125234.268520@g44g2000cwa.googlegroups.com...
>I have a XML file which contains data for multiple tables. It looks
> something like this:
> <table1>
> <tbl1_tag1>...</tbl1_tag1>
> <tbl1_tag2>...</tbl1_tag2>
> <table2>
> <tbl2_tag1>...</tbl2_tag1>
> <tbl2_tag2>...</tbl2_tag2>
> </table2>
> <tbl1_tag3>...</tbl1_tag3>
> <tbl1_tag4>...</tbl1_tag4>
> <table3>
> <tbl3_tag1>...</tbl3_tag1>
> <tbl3_tag2>...</tbl3_tag2>
> <tbl3_tag3>...</tbl3_tag3>
> </table3>
> <tbl1_tag4>...</tbl1_tag4>
> <tbl1_tag5>...</tbl1_tag5>
> </table1>
>
> I need to import the data into 3 differernt SQL database tables with
> table colums like:
> For table 1: tbl1_tag1, tbl1_tag2, tbl1_tag3, tbl1_tag4, tbl1_tag5
> For table 2: tbl2_tag1, tbl2_tag2
> For table 3: tbl3_tag1, tbl3_tag2, tbl3_tag3
>
> I tried to use the SqlDataAdpater, but could not get the right data.
> Any idea how to solve this problem?
>
> Thanks very much.
>

raj

2005-10-03, 7:24 am

guxu@hotmail.com wrote:
> I have a XML file which contains data for multiple tables. It looks
> something like this:
> <table1>
> <tbl1_tag1>...</tbl1_tag1>
> <tbl1_tag2>...</tbl1_tag2>
> <table2>
> <tbl2_tag1>...</tbl2_tag1>
> <tbl2_tag2>...</tbl2_tag2>
> </table2>
> <tbl1_tag3>...</tbl1_tag3>
> <tbl1_tag4>...</tbl1_tag4>
> <table3>
> <tbl3_tag1>...</tbl3_tag1>
> <tbl3_tag2>...</tbl3_tag2>
> <tbl3_tag3>...</tbl3_tag3>
> </table3>
> <tbl1_tag4>...</tbl1_tag4>
> <tbl1_tag5>...</tbl1_tag5>
> </table1>
>
> I need to import the data into 3 differernt SQL database tables with
> table colums like:
> For table 1: tbl1_tag1, tbl1_tag2, tbl1_tag3, tbl1_tag4, tbl1_tag5
> For table 2: tbl2_tag1, tbl2_tag2
> For table 3: tbl3_tag1, tbl3_tag2, tbl3_tag3
>
> I tried to use the SqlDataAdpater, but could not get the right data.
> Any idea how to solve this problem?
>
> Thanks very much.

James

2005-10-31, 11:23 am

Try XMLengine. http://www.talinum.com/products.htm

James

"guxu@hotmail.com" wrote:

> I have a XML file which contains data for multiple tables. It looks
> something like this:
> <table1>
> <tbl1_tag1>...</tbl1_tag1>
> <tbl1_tag2>...</tbl1_tag2>
> <table2>
> <tbl2_tag1>...</tbl2_tag1>
> <tbl2_tag2>...</tbl2_tag2>
> </table2>
> <tbl1_tag3>...</tbl1_tag3>
> <tbl1_tag4>...</tbl1_tag4>
> <table3>
> <tbl3_tag1>...</tbl3_tag1>
> <tbl3_tag2>...</tbl3_tag2>
> <tbl3_tag3>...</tbl3_tag3>
> </table3>
> <tbl1_tag4>...</tbl1_tag4>
> <tbl1_tag5>...</tbl1_tag5>
> </table1>
>
> I need to import the data into 3 differernt SQL database tables with
> table colums like:
> For table 1: tbl1_tag1, tbl1_tag2, tbl1_tag3, tbl1_tag4, tbl1_tag5
> For table 2: tbl2_tag1, tbl2_tag2
> For table 3: tbl3_tag1, tbl3_tag2, tbl3_tag3
>
> I tried to use the SqlDataAdpater, but could not get the right data.
> Any idea how to solve this problem?
>
> Thanks very much.
>
>

Sponsored Links