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>info@gmail.com</ns1:email>
</ns1:avisoData>
</ns1:order>
</ns1:orders>
1条答案
按热度按时间cbeh67ev1#
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....
Use this SQL query using the SQL Server XQuery support to get at your data items:
This returns a result of: