XML : 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?

No comments:

Post a Comment