Sql-server – SQL Server 2014 – UPSERT XML Multiple rows

sql server 2014xml

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

Best Answer

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