--Putting It All Together
SELECT
CustomerName 'OrderHeader/CustomerName'
, OrderDate 'OrderHeader/OrderDate'
, OrderID 'OrderHeader/OrderID'
,(
SELECT
LineItems2.StockItemID '@ProductID'
, StockItems.StockItemName '@ProductName'
, LineItems2.UnitPrice '@Price'
, Quantity '@Qty'
FROM Sales.OrderLines LineItems2
INNER JOIN Warehouse.StockItems StockItems
ON LineItems2.StockItemID = Stock
Items.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.CustomerName = 'Agrita Abele'
) Base
FOR XML PATH('Order'), ROOT ('SalesOrders') ;
Listing pulls together the aspects that we have discussed and returns an XML document in the required format.
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.