CODEWITHSUNDEEP
sqlsql-serverxmlxpath
Insider Pro
Insider Pro

Reputation: 748

SQL SELECT from XML field as multiple records

I am working on a SELECT query from a table ORDERS, which contains an XML field. Among other data that XML field contains order line items. I know this isn't best design but I have to stick to it. I need to select all order line items.

This gets me the first line items for each order:

SELECT
OrderNumber,
[OrderItems].value('(/items/item/sku/node())[1]', 'varchar(20)') AS SKU
FROM Orders
WHERE OrderDate = '2016-04-09'

Is there a way to get all line items for all invoices?

Upvotes: 2

Views: 68

Answers (1)

har07
har07

Reputation: 89325

Use nodes() with APPLY to shred XML data into multiple records. For example, assuming that you want to shred the XML at <sku> element level, you can do as follow :

SELECT
    OrderNumber,
    SkuColumn.value('.', 'varchar(20)') AS SKU
FROM Orders
    CROSS APPLY OrderItems.nodes('/items/item/sku') SkuTable(SkuColumn)
WHERE OrderDate = '2016-04-09'

Upvotes: 2

Related Questions