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 ;
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
Be the first to comment
You can use [html][/html], [css][/css], [php][/php] and more to embed the code. Urls are automatically hyperlinked. Line breaks and paragraphs are automatically generated.