To demonstrate the use of the XQuery methods in this chapter, we will create a table in the WideWorldImporters database, called Sales. CustomerOrderSummary. This table can be created using the script

Sales.CustomerOrderSummary.sql

USE WideWorldImporters
GO
CREATE TABLE Sales.CustomerOrderSummary
(
        ID INT NOT NULL IDENTITY,
        CustomerID INT NOT NULL,
        OrderSummary XML
);
INSERT INTO Sales.CustomerOrderSummary (CustomerID,
OrderSummary)
SELECT
        CustomerID,
        (
SELECT
CustomerName 'OrderHeader/CustomerName'
, OrderDate 'OrderHeader/OrderDate' , OrderID 'OrderHeader/OrderID'
,(

SELECT

LineItems2.StockItemID
  '@ProductID'
, StockItems.StockItem
Name '@ProductName'
, LineItems2.UnitPrice
'@Price'
, Quantity '@Qty'
FROM Sales.OrderLines LineItems2 INNER JOIN Warehouse.StockItems StockItems
ON LineItems2.StockItemID
= StockItems.StockItemID WHERE LineItems2.OrderID =
      Base.OrderID
      FOR XML PATH('Product'), TYPE
) 'OrderDetails'

FROM
 (

SELECT DISTINCT
          Customers.CustomerName
        , SalesOrder.OrderDate
        , SalesOrder.OrderID
FROM Sales.Orders SalesOrder
INNER JOIN Sales.OrderLines LineItem
        ON SalesOrder.OrderID =
        LineItem.OrderID
INNER JOIN Sales.Customers Customers
        ON Customers.CustomerID =
        SalesOrder.CustomerID
WHERE customers.CustomerID = OuterCust.
CustomerID
) Base
FOR XML PATH('Order'), ROOT ('SalesOrders'), TYPE
        ) AS OrderSummary
FROM Sales.Customers OuterCust ;