5

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

4

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 on Items/Item.

Also using query('').value('.') is not a good idea. Better to make sure you only get one value out of the value() function by using [1].

One extra thing for performance is to specify the text() node in the value().

select I.X.value('(Details/ItemID/text())[1]', 'int') as ItemID,
       I.X.value('(Details/Store/text())[1]', 'int') as Store,
       I.X.value('(Details/Title/text())[1]', 'nvarchar(100)') as Title,
       I.X.value('(Details/SKU/text())[1]', 'nvarchar(100)') as SKU,
       I.X.value('(Quantity/text())[1]', 'int') as Quantity,
       I.X.value('(Price/text())[1]', 'decimal(11,2)') as Quantity,
       O.X.value('(OrderID/text())[1]', 'int') as OrderID,
       O.X.value('(AmountPaid/text())[1]', 'decimal(11,2)') as AmountPaid,
       O.X.value('(UserID/text())[1]', 'nvarchar(100)') as UserID,
       O.X.value('(ShippingCosts/text())[1]', 'decimal(11,2)') as ShippingCosts
from @XML.nodes('/Orders/Order') as O(X)
  cross apply O.X.nodes('Items/Item') as I(X);
  • Sorry, how to update a table with the above code? – Joe Oct 3 '17 at 18:27
  • Do you want to update an order in a target table if it exist, matched on orderid and add a row if ordered is missing in the target table? Or should there be a match on itemid instead? Or perhaps both? What is the structure of the target table? Are there more than one table that should be updated? – Mikael Eriksson Oct 3 '17 at 18:33
  • Actually I thought I need only to add a Where..... at the bottom, but it is little more complicated: what need to do is exactly as you wrote, therefore it is better if I create a new question and I send you the link, hoping not stress you too much – Joe Oct 3 '17 at 18:55
  • I agree, that is better. – Mikael Eriksson Oct 3 '17 at 18:58
  • I'm back with the updated question: HERE if you have a little time to have a look, thanks. – Joe Oct 4 '17 at 23:22
7
+100

Bad news: the local-name(.) plus @XML.nodes('//*[count(child::*) = 0]') approach in your script completely flattens out the XML document, and will need to be reworked if you have any multiple-X-per-Y structures in your document. If the dynamic SQL is a requirement, then please provide an example to test that aspect more easily.

Another approach might be to build queries you need manually. If your fundamental problem is including parent info, then you might modify my demo SELECT query below.

(N.B. Mikael Eriksson's answer has an improved query. Please refer to that.)

Key ideas:

. is the context node, and .. gives you the parent

.nodes is a different kind of XML function that belongs in the FROM section of the query and is usually seen with CROSS APPLY. It returns a pointer per match, and is what allows working with multiple rows. Read more here.

.query().value is one of several methods for letting SQL Server know the value method only has one piece of data to work with (fixing the "requires a singleton" error)

DECLARE @XML xml = 
'<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>'

SELECT 
    x.value('./ItemID[1]','int') AS ItemID,
    x.value('./Store[1]','int') AS Store,
    x.value('./Title[1]','nvarchar(100)') AS Title,
    x.value('./SKU[1]','nvarchar(100)') AS SKU,
    x.value('../Quantity[1]','int') AS Qty,
    x.value('../Price[1]','decimal(11,2)') AS Price,
    x.query('//OrderID[1]').value('.','int') AS OrderID,
    x.query('//AmountPaid[1]').value('.','decimal(11,2)') AS AmountPaid,
    x.query('//UserID[1]').value('.','nvarchar(100)') AS UserID,
    x.query('//ShippingCosts[1]').value('.','decimal(11,2)') AS ShippingCosts
FROM @XML.nodes('//Item/Details') i(x)
  • Hi! Sorry for late answer, but first I tried to understand what you did. Actually, since this is not the main routine and the reference table is always the same I already thought to build the query manually, but although I knew the use of . and .. I did not knew the x.query method, therefore I couldn't build the query manually. – Joe Sep 16 '17 at 12:02
4

Let's try to create temporary denormalized table with all your data. Some fields are discarded but you can easily add them by yourself. You will be able to insert in your production tables grouping results at your own taste.

    declare @xml varchar(max)
    declare @idoc int

    set @xml='<?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>'

    print 'prepare xml document'
    exec sp_xml_preparedocument @idoc output, @xml

    print 'create temporary table'
    CREATE TABLE #import (OrderID int, UserID varchar(500), AmountPaid numeric(18,2), AmountPaidCurrencyID char(3),  ShippingAddress_Name varchar(500), ShippingCosts numeric(18,2), ItemID int, Store int, Title varchar(500), SKU varchar(500), Quantity numeric(18,4), CurrencyID char(3), CurrencyPrice numeric(18,2))
    CREATE INDEX #ix_import ON #import (OrderID)

    print 'insert temporary table'
    insert into #import (  OrderID,  UserID,  AmountPaid,  AmountPaidCurrencyID,  ShippingAddress_Name,  ShippingCosts,  ItemID,  Store,  Title,  SKU,  Quantity,  CurrencyID,  CurrencyPrice)
    SELECT               a.OrderID,a.UserID,a.AmountPaid,a.AmountPaidCurrencyID,a.ShippingAddress_Name,a.ShippingCosts,b.ItemID,b.Store,b.Title,b.SKU,b.Quantity,b.CurrencyID,b.CurrencyPrice
    FROM        OPENXML (@idoc, '/Orders/Order',2)
                WITH    (
                         OrderID int                        'OrderID'
                        ,UserID varchar(500)                'UserID'
                        ,AmountPaidCurrencyID char(3)       'AmountPaid/@currencyID'
                        ,AmountPaid numeric(18,2)           'AmountPaid'
                        ,ShippingAddress_Name varchar(500)  'ShippingAddress/Name'
                        ,ShippingCosts numeric(18,2)        'ShippingCosts'
                        ) a

    LEFT JOIN   OPENXML (@idoc, '/Orders/Order/Items/Item',2)
                WITH    (
                         OrderID int                '../../OrderID'
                        ,ItemID int                 'Details/ItemID'
                        ,Store int                  'Details/Store'
                        ,Title varchar(500)         'Details/Title'
                        ,SKU varchar(500)           'Details/SKU'
                        ,Quantity numeric(18,4)     'Quantity'
                        ,CurrencyID char(3)         'Price/@currencyID'
                        ,CurrencyPrice numeric(18,2)'Price'
                        ) b ON a.OrderID=b.OrderID

    print 'remove xml document'
    exec sp_xml_removedocument @idoc

    -- 
    select * from #import

    print 'drop temporary table'
    DROP TABLE #import

Here is result set:

    OrderID UserID  AmountPaid  AmountPaidCurrencyID    ShippingAddress_Name    ShippingCosts   ItemID  Store   Title       SKU Quantity    CurrencyID  CurrencyPrice
    334     marc58  17.10       EUR                     Marc Juppe              4.50            3664    47      MCPU DDA010 mmx 1.0000      EUR         6.20
    334     marc58  17.10       EUR                     Marc Juppe              4.50            3665    45      MCPU DFZ42  mmy 2.0000      EUR         3.20
  • Hi! Thanks for the complete answer and the actual solution to my question. But it will takes me some days to fully understand what's behind all steps. For this specific case I think I'll use Forrest's solution since yours seems me too complicated not only for me but also for the machine and will use too much resources. But I learned a lot from your solution. thanks a lot – Joe Sep 16 '17 at 12:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.