1

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

0

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

|improve this answer|||||
  • 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 Oct 19 '17 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 Oct 20 '17 at 5:44
  • Yes you can hold the values in a temp table and pass each one in the variable @LineID. – SqlWorldWide Oct 20 '17 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 Oct 20 '17 at 13:47
  • You might consider replacing the variable with the select statement I showed (or something that sort). – SqlWorldWide Oct 20 '17 at 13:55

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.