XML Import 1:n relationship
-
Hi All,
I have the code below which is for importing xml files
It results in
customernumber accountnumer tp zip
--------------- ------------------------------ ---------- ----------
113 26110328983 31 10217
521 78136363 22 25311
(2 row(s) affected)
order_number remarks
--------------- ------------------------------
42 rush order
219 special package
1320
190 birthday wrap
11 birthday wrap
1181
7 special
(7 row(s) affected)
However what I would like (very much) is the following
nr customernumber accountnumer tp zip
------------------- --------------- ---------- ----------
1 113 26110328983 31 10217
2 521 78136363 22 25311
nr order_number remarks
------------------- -----------------
1 42 rush order
1 219 special package
1 1320
1 190 birthday wrap
2 11 birthday wrap
2 1181
2 7 special
so that the order details are connected (through the field 'nr') to the order header.
Any help is very much appreciated.
Kind regards,
John
DECLARE @invoer varchar(4096)
DECLARE @hendel int
SET @invoer =
'<root>
<customer>
<customernumber>113</customernumber>
<accountnumer>26110328983</accountnumer>
<tp>31</tp>
<zip>10217</zip>
<orders>
<order>
<order_number>42</order_number>
<remarks>rush order</remarks>
</order>
<order>
<order_number>219</order_number>
<remarks>special package</remarks>
</order>
<order>
<order_number>1320</order_number>
<remarks></remarks>
</order>
<order>
<order_number>190</order_number>
<remarks>birthday wrap</remarks>
</order>
</orders>
</customer>
<customer>
<customernumber>521</customernumber>
<accountnumer>78136363</accountnumer>
<tp>22</tp>
<zip>25311</zip>
<orders>
<order>
<order_number>11</order_number>
<remarks>birthday wrap</remarks>
</order>
<order>
<order_number>1181</order_number>
<remarks></remarks>
</order>
<order>
<order_number>7</order_number>
<remarks>special</remarks>
</order>
</orders>
</customer>
</root>'
EXEC sp_xml_preparedocument @hendel OUTPUT, @invoer
SELECT customernumber, accountnumer, tp, zip
FROM OPENXML(@hendel, '/root/customer', 2)
WITH (customernumber varchar(15), accountnumer varchar(30), tp varchar(10), zip varchar(10))
SELECT order_number, remarks
FROM OPENXML(@hendel, '/root/customer/orders/order', 2)
WITH (order_number varchar(15), remarks varchar(30))
-
Try this
DECLARE @x XML
SET @x=@invoer
SELECT r.value('1+count(for $a in . return $a/../ *[. << $a])','int') AS nr,
r.value('(./customernumber)[1]','VARCHAR(15)') AS customernumber,
r.value('(./accountnumer)[1]','VARCHAR(30)') AS accountnumer,
r.value('(./tp)[1]','VARCHAR(10)') AS tp,
r.value('(./zip)[1]','VARCHAR(10)') AS zip
FROM @x.nodes('/root/customer') x(r)
SELECT r.value('1+count(for $a in . return $a/../ *[. << $a])','int') AS nr,
b.value('(./order_number)[1]','VARCHAR(15)') AS order_number,
b.value('(./remarks)[1]','VARCHAR(30)') AS remarks
FROM @x.nodes('/root/customer') x(r)
CROSS APPLY x.r.nodes('orders/order') a(b)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Dear Mark-101232,
This is a great help, thank you very very much!
Kind regards,
John
-
jjzzmp (11/29/2011)
Dear Mark-101232,This is a great help, thank you very very much!
Kind regards,
John
You're welcome!
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply