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
You can take this output in a variable to check for existence.
Here is the result set I get: