I have a XML response with this structure but with some 150 different nodes:
<?xml version="1.0" encoding="UTF-8"?>
<Orders>
<Order>
<OrderID>334</OrderID>
<AmountPaid currencyID="EUR">17.10</AmountPaid>
<UserID>marc58</UserID>
<ShippingAddress>
<Name>Marc Juppé</Name>
<Address>Rue</Address>
<City>Paris</City>
<StateOrProvince></StateOrProvince>
<Country>FR</Country>
<Phone>333333333</Phone>
<PostalCode>22222</PostalCode>
</ShippingAddress>
<ShippingCosts>4.50</ShippingCosts>
<Items>
<Item>
<Details>
<ItemID>3664</ItemID>
<Store>47</Store>
<Title>MCPU DDA010</Title>
<SKU>mmx</SKU>
</Details>
<Quantity>1</Quantity>
<Price currencyID="EUR">6.2</Price>
</Item>
<Item>
<Details>
<ItemID>3665</ItemID>
<Store>45</Store>
<Title>MCPU DFZ42</Title>
<SKU>mmy</SKU>
</Details>
<Quantity>2</Quantity>
<Price currencyID="EUR">3.2</Price>
</Item>
</Items>
</Order>
</Orders>
I need to store this info into 3 different tables and for Item
table, I need to create a record for each different <Item>
, but inserting also the Order Node details; like this:
|ItemID|Store|Title |SKU|Quantity|Price|OrderID|AmountPaid|UserID|ShippingCost|
|3664 | 47|DDA010|mmx| 1| 6.2| 334| 17.10|marc58| 4.50|
|3665 | 45|DFZ42 |mmy| 2| 3.2| 334| 17.10|marc58| 4.50|
To write in the different tables the required info "automatically", I built with great help of the community this query:
Set @T1='Orders'
Set @F1='OrderID'
Set @V=''
SELECT
@C= IIF (CHARINDEX('['+T.X.value('local-name(.)', 'nvarchar(100)')+']',@C)=0, CONCAT( ISNULL(@C + ',','') , QUOTENAME(T.X.value('local-name(.)', 'nvarchar(100)'))), @C),
@D= IIF (CHARINDEX('['+T.X.value('local-name(.)', 'nvarchar(100)')+']',@CP)=0, CONCAT( ISNULL(@D + ',N','') , '''', T.X.value(N'text()[1]', 'nvarchar(max)'),''''), @D),
@U= IIF (CHARINDEX('['+T.X.value('local-name(.)', 'nvarchar(100)')+']',@CP)=0, CONCAT( ISNULL(@U + ',','') , QUOTENAME( T.X.value('local-name(.)', 'nvarchar(100)')) ,'=', '''',T.X.value(N'text()[1]', 'nvarchar(max)'),''''), @U),
@V= IIF(T.X.value('local-name(.)', 'nvarchar(100)') =@F2, T.X.value('text()[1]', 'nvarchar(100)'), @V),
FROM @XML.nodes('//*[count(child::*) = 0]') AS T(X)
WHERE T.X.value(N'local-name(.)', 'nvarchar(500)')
IN (select name from db1.sys.columns where [object_id]=OBJECT_ID(@T1)and is_identity=0)
SELECT @C = STUFF(@C, 1, 1, '');
SELECT @D = STUFF(@D, 1, 1, '');
SELECT @U = STUFF(@U, 1, 1, '');
SET @S=N'IF NOT EXISTS (SELECT 1 FROM '+@T1+' WHERE '+@F1+' = '''+@V+''')
INSERT INTO '+@T1+' ('+@C+') VALUES ('+@D+''')
ELSE UPDATE '+@T1+' SET '+@U+''' WHERE '+@F2+'='''+@V+''''
Print @S
EXEC sp_executesql .....
Set @T1="Users"
......
OK, better to use a loop here
This query, although can be surely improved and optimized, has been working well until now, since there was only 1 Item Node, but now, with more Item Nodes it returns only the first one.
I tried to modify the FROM Clause trying to refer to the Item Collection, but without success, but I think that even if I succeed iterating the Item(s) nodes, I have no Idea how to get the Order Node details, that are Parent of Item node…
Can you suggest a solution?
Thanks
Best Answer
The query you got from Forrest can be improved a bit.
Using the parent axis is almost always a really bad idea in xQuery in SQL Server. You can avoid that by shredding on
Orders/Order
first and then use a cross apply to shred onItems/Item
.Also using
query('').value('.')
is not a good idea. Better to make sure you only get one value out of thevalue()
function by using[1]
.One extra thing for performance is to specify the
text()
node in thevalue()
.