I want to insert this XML file (API Response) into a database table as columns. when the response consist of two different items for unique order id, order id column should be inserted for both different items.
Here is the result that should be like this :
row #1 order id: i6tp-pucp-dsrx-1gg7-ikef item_no: 2304
row #2 order id: i6tp-pucp-dsrx-1gg7-ikef item_no: 10914
Here is the response which I get from an API in XML form:
<?xml version="1.0" encoding="UTF-8" standalone="yes"? ><ns1:result xmlns:ns1="url.xsd">
<ns1:orders>
<ns1:order>
<ns1:code>i6tp-pucp-dsrx-1gg7-ikef</ns1:code>
<ns1:status>CONFIRMED</ns1:status>
<ns1:date>2022-11-30T20:50:36.920</ns1:date>
<ns1:deliveryAddress>
<ns1:firstName>fname</ns1:firstName>
<ns1:lastName>lastname</ns1:lastName>
<ns1:streetname>streetname </ns1:streetname>
<ns1:streetnumber>stno</ns1:streetnumber>
<ns1:town>town</ns1:town>
<ns1:postalCode>PLZ</ns1:postalCode>
<ns1:gender>FEMALE</ns1:gender>
<ns1:deliveryAddressType>address</ns1:deliveryAddressType>
</ns1:deliveryAddress>
<ns1:paymentAddress>
<ns1:firstName>fname</ns1:firstName>
<ns1:lastName>lastname</ns1:lastName>
<ns1:streetname>streetname </ns1:streetname>
<ns1:streetnumber>stno</ns1:streetnumber>
<ns1:town>town</ns1:town>
<ns1:postalCode>PLZ</ns1:postalCode>
<ns1:gender>FEMALE</ns1:gender>
</ns1:paymentAddress>
<ns1:currency>EUR</ns1:currency>
<ns1:entries>
<ns1:entry>
<ns1:sku>2304</ns1:sku>
<ns1:quantity>1</ns1:quantity>
<ns1:basePrice>18.49</ns1:basePrice>
<ns1:totalBasePrice>18.49</ns1:totalBasePrice>
<ns1:merchantSubTotal>18.49</ns1:merchantSubTotal>
<ns1:totalPrice>18.49</ns1:totalPrice>
<ns1:name>ArtNo1</ns1:name>
<ns1:taxClass>at-vat-full</ns1:taxClass>
<ns1:warehouse>001-default-warehouse</ns1:warehouse>
</ns1:entry>
<ns1:entry>
<ns1:sku>10914</ns1:sku>
<ns1:quantity>1</ns1:quantity>
<ns1:basePrice>49.99</ns1:basePrice>
<ns1:totalBasePrice>49.99</ns1:totalBasePrice>
<ns1:merchantSubTotal>49.99</ns1:merchantSubTotal>
<ns1:totalPrice>49.99</ns1:totalPrice>
<ns1:name>Artno2</ns1:name>
<ns1:taxClass>at-vat-full</ns1:taxClass>
<ns1:warehouse>001-default-warehouse</ns1:warehouse>
</ns1:entry>
</ns1:entries>
<ns1:cancelOrders/>
<ns1:returnOrders/>
<ns1:delayOrders/>
<ns1:consignments/>
<ns1:totalBasePrice>68.48</ns1:totalBasePrice>
<ns1:merchantDiscountTotal>0</ns1:merchantDiscountTotal>
<ns1:merchantSubTotal>68.48</ns1:merchantSubTotal>
<ns1:marketplaceDiscountTotal>0</ns1:marketplaceDiscountTotal>
<ns1:subtotal>68.48</ns1:subtotal>
<ns1:deliveryCost>0.0</ns1:deliveryCost>
<ns1:paymentCost>0.0</ns1:paymentCost>
<ns1:totalPrice>68.48</ns1:totalPrice>
<ns1:paymentMode>payment</ns1:paymentMode>
<ns1:deliveryMode>E+2</ns1:deliveryMode>
<ns1:additionalDeliveryOption>E2_1</ns1:additionalDeliveryOption>
<ns1:deliveryConfiguration>Post</ns1:deliveryConfiguration>
<ns1:shipmentDate>2022-12-02T07:00:11.680</ns1:shipmentDate>
<ns1:estimatedDeliveryDate>2022-12-06T07:00:11.680</ns1:estimatedDeliveryDate>
<ns1:avisoData>
<ns1:address>
<ns1:line>line1</ns1:line>
<ns1:line>line2</ns1:line>
<ns1:line>line3</ns1:line>
</ns1:address>
<ns1:phone>0123456789</ns1:phone>
<ns1:email>info@gmail.com</ns1:email>
</ns1:avisoData>
</ns1:order>
</ns1:orders></ns1:result>
Try something like this - obviously, as mentioned - I don't know what your XML namespace declaration looks like, so I've just faked it here - adapt as needed....
DECLARE @Data XML =
'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ns1:orders xmlns:ns1="urn:somenamespace">
<ns1:order>
<ns1:code>i6tp-pucp-dsrx-1gg7-ikef</ns1:code>
<ns1:status>CONFIRMED</ns1:status>
<ns1:date>2022-11-30T20:50:36.920</ns1:date>
<ns1:deliveryAddress>
<ns1:firstName>fname</ns1:firstName>
<ns1:lastName>lastname</ns1:lastName>
<ns1:streetname>streetname </ns1:streetname>
<ns1:streetnumber>stno</ns1:streetnumber>
<ns1:town>town</ns1:town>
<ns1:postalCode>PLZ</ns1:postalCode>
<ns1:gender>FEMALE</ns1:gender>
<ns1:deliveryAddressType>address</ns1:deliveryAddressType>
</ns1:deliveryAddress>
<ns1:paymentAddress>
<ns1:firstName>fname</ns1:firstName>
<ns1:lastName>lastname</ns1:lastName>
<ns1:streetname>streetname </ns1:streetname>
<ns1:streetnumber>stno</ns1:streetnumber>
<ns1:town>town</ns1:town>
<ns1:postalCode>PLZ</ns1:postalCode>
<ns1:gender>FEMALE</ns1:gender>
</ns1:paymentAddress>
<ns1:currency>EUR</ns1:currency>
<ns1:entries>
<ns1:entry>
<ns1:sku>2304</ns1:sku>
<ns1:quantity>1</ns1:quantity>
<ns1:basePrice>18.49</ns1:basePrice>
<ns1:totalBasePrice>18.49</ns1:totalBasePrice>
<ns1:merchantSubTotal>18.49</ns1:merchantSubTotal>
<ns1:totalPrice>18.49</ns1:totalPrice>
<ns1:name>ArtNo1</ns1:name>
<ns1:taxClass>at-vat-full</ns1:taxClass>
<ns1:warehouse>001-default-warehouse</ns1:warehouse>
</ns1:entry>
<ns1:entry>
<ns1:sku>10914</ns1:sku>
<ns1:quantity>1</ns1:quantity>
<ns1:basePrice>49.99</ns1:basePrice>
<ns1:totalBasePrice>49.99</ns1:totalBasePrice>
<ns1:merchantSubTotal>49.99</ns1:merchantSubTotal>
<ns1:totalPrice>49.99</ns1:totalPrice>
<ns1:name>Artno2</ns1:name>
<ns1:taxClass>at-vat-full</ns1:taxClass>
<ns1:warehouse>001-default-warehouse</ns1:warehouse>
</ns1:entry>
</ns1:entries>
<ns1:cancelOrders/>
<ns1:returnOrders/>
<ns1:delayOrders/>
<ns1:consignments/>
<ns1:totalBasePrice>68.48</ns1:totalBasePrice>
<ns1:merchantDiscountTotal>0</ns1:merchantDiscountTotal>
<ns1:merchantSubTotal>68.48</ns1:merchantSubTotal>
<ns1:marketplaceDiscountTotal>0</ns1:marketplaceDiscountTotal>
<ns1:subtotal>68.48</ns1:subtotal>
<ns1:deliveryCost>0.0</ns1:deliveryCost>
<ns1:paymentCost>0.0</ns1:paymentCost>
<ns1:totalPrice>68.48</ns1:totalPrice>
<ns1:paymentMode>payment</ns1:paymentMode>
<ns1:deliveryMode>E+2</ns1:deliveryMode>
<ns1:additionalDeliveryOption>E2_1</ns1:additionalDeliveryOption>
<ns1:deliveryConfiguration>Post</ns1:deliveryConfiguration>
<ns1:shipmentDate>2022-12-02T07:00:11.680</ns1:shipmentDate>
<ns1:estimatedDeliveryDate>2022-12-06T07:00:11.680</ns1:estimatedDeliveryDate>
<ns1:avisoData>
<ns1:address>
<ns1:line>line1</ns1:line>
<ns1:line>line2</ns1:line>
<ns1:line>line3</ns1:line>
</ns1:address>
<ns1:phone>0123456789</ns1:phone>
<ns1:email>info@gmail.com</ns1:email>
</ns1:avisoData>
</ns1:order>
</ns1:orders>';
Use this SQL query using the SQL Server XQuery support to get at your data items:
WITH XMLNAMESPACES('urn:somenamespace' AS ns1)
SELECT
OrderNo = xc.value('(ns1:code)[1]', 'varchar(50)'),
ItemNo = xc2.value('(ns1:sku)[1]', 'int')
FROM
@Data.nodes('/ns1:orders/ns1:order') AS XT(XC)
CROSS APPLY
XC.nodes('ns1:entries/ns1:entry') AS XT2(XC2)
This returns a result of:
OrderNo ItemNo
-----------------------------------
i6tp-pucp-dsrx-1gg7-ikef 2304
i6tp-pucp-dsrx-1gg7-ikef 10914