SQL Server 2014 - UPSERT XML Multiple rows

Question

We have an input like this

    <?xml version="1.0" encoding="UTF-8"?>
    <Orders xmlns:jjsales:api:sales>
        <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>
             <Lines>
                <Line>
                   <Details>
                        <ItemID>3664</ItemID>
                        <Store>47</Store>
                        <Title>MCPU DDA010</Title>
                        <SKU>mmx</SKU>
                    </Details>
                    <LineID>1111</LineID>
                    <Quantity>1</Quantity>
                    <Price currencyID="EUR">6.2</Price>
                </Line>
                <Line>
                   <Details>
                        <ItemID>3665</ItemID>
                        <Store>45</Store>
                        <Title>MCPU DFZ42</Title>
                        <SKU>mmy</SKU>
                    </Details>
                    <LineID>1112</LineID>
                    <Quantity>2</Quantity>
                    <Price currencyID="EUR">3.2</Price>
                </Line>
            </Lines>
        </Order>
    </Orders>

and we are currently importing data into Sales Table with a query like this:

    INSERT INTO Sales (ItemID,Store,Title,SKU,Quantity,Price,OrderID,AmountPaid,UserID,ShippingCosts)

    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('Lines/Line') as I(X)

but now we need to update records that already exists checking Lines.Line.LineID

Since do not want to use MERGE, we thought it was easy to solve adding the usual

    IF NOT EXISTS (SELECT 1 FROM Sales where LineID=@LineID)
        INSERT INTO Sales () SELECT ....
    ELSE
        UPDATE Sales SET ItemID=..., Store=..., ... WHERE LineID=@LineID

but the value of @LineID in inside the XML and has to be calculated while parsing the XML

And discovered have no idea how to get it

Can suggest how to get the value of LineID while parsing the XML?

Thanks

Community · Accepted Answer · 2020-06-15 09:05:18Z

Can suggest how to get the value of LineID while parsing the XML?

You can take this output in a variable to check for existence.

DECLARE @input 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>
             <Lines>
                <Line>
                   <Details>
                        <ItemID>3664</ItemID>
                        <Store>47</Store>
                        <Title>MCPU DDA010</Title>
                        <SKU>mmx</SKU>
                    </Details>
                    <LineID>1111</LineID>
                    <Quantity>1</Quantity>
                    <Price currencyID="EUR">6.2</Price>
                </Line>
                <Line>
                   <Details>
                        <ItemID>3665</ItemID>
                        <Store>45</Store>
                        <Title>MCPU DFZ42</Title>
                        <SKU>mmy</SKU>
                    </Details>
                    <LineID>1112</LineID>
                    <Quantity>2</Quantity>
                    <Price currencyID="EUR">3.2</Price>
                </Line>
            </Lines>
        </Order>
    </Orders>
'


   SELECT
      LineId = XCol.value('(LineID)[1]','int')
      FROM 
      @input.nodes('/Orders/Order/Lines/Line') AS XTbl(XCol)

Here is the result set I get:

LineId

1111

1112

Sorry but I think I've not understood: once I got these result what shall I do? put them in a temp table and iterate the current query for the 2 (or more) Lines? — Joe
– Joe, Commented Oct 19, 2017 at 22:01
Well, I (tried to) explain it in the question: we need to upsert the sales table, having LineID as key. Therefore, yes, how to get the value of LineID, but inside the procedure of upsert, if it is possible. Thanks — Joe
– Joe, Commented Oct 20, 2017 at 5:44
Yes you can hold the values in a temp table and pass each one in the variable @LineID. — SqlWorldWide
– SqlWorldWide, Commented Oct 20, 2017 at 11:47
hmm.. currently we are parsing the data in the script and passing the "clean" XML to the SP for each Line: if this is the solution better to continue in the old way: I thought there was a more elegant solution. — Joe
– Joe, Commented Oct 20, 2017 at 13:47
You might consider replacing the variable with the select statement I showed (or something that sort). — SqlWorldWide
– SqlWorldWide, Commented Oct 20, 2017 at 13:55

Stack Exchange Network

SQL Server 2014 - UPSERT XML Multiple rows

1 Answer 1

Your Answer

Hot Network Questions

SQL Server 2014 - UPSERT XML Multiple rows

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions