Insert order details from XML into SQL database table-CodePudding

I want to insert this XML file into a database table. It has two different items and orderno (code) should be inserted for both rows.

Here is the xml file - it should be like this :

row #1  orderno: i6tp-pucp-dsrx-1gg7-ikef  itemno: 2304
row #2  orderno: i6tp-pucp-dsrx-1gg7-ikef  itemno: 10914

XML:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<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>[email protected]</ns1:email>
        </ns1:avisoData>
    </ns1:order>
</ns1:orders>

CodePudding user response：

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>[email protected]</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