SELECT
1 AS Tag
, 0 AS Parent
, SalesOrder.OrderID AS
[OrderDetails!1!SalesOrderID]
, SalesOrder.OrderDate AS
[OrderDetails!1!OrderDate]
, SalesOrder.CustomerID AS
[OrderDetails!1!CustomerID]
, NULL AS [SalesPerson!2!SalesPersonName]
, NULL AS [LineItem!3!LineTotal!ELEMENT]
, NULL AS [LineItem!3!ProductName!ELEMENT]
, NULL AS [LineItem!3!OrderQty!ELEMENT]
FROM Sales.Orders SalesOrder
INNER JOIN Sales.Customers Customers
ON Customers.CustomerID = SalesOrder.CustomerID
WHERE customers.CustomerName = 'Agrita Abele'
UNION ALL
SELECT
2 AS Tag
, 1 AS Parent
, SalesOrder.OrderID
, NULL
, NULL
, People.FullName
, NULL
, NULL
, NULL
FROM Sales.Orders SalesOrder
INNER JOIN Sales.Customers Customers
ON Customers.CustomerID = SalesOrder.CustomerID
INNER JOIN Application.People People
ON People.PersonID = SalesOrder.SalespersonPersonID
WHERE customers.CustomerName = 'Agrita Abele'
UNION ALL
SELECT
3 AS Tag
, 1 AS Parent
, SalesOrder.OrderID
, NULL
, NULL
, People.FullName
, LineItem.UnitPrice
, Product.StockItemName
, LineItem.Quantity
FROM Sales.Orders SalesOrder
INNER JOIN Sales.OrderLines LineItem
ON SalesOrder.OrderID = LineItem.OrderID
INNER JOIN Sales.Customers Customers
ON Customers.CustomerID = SalesOrder.CustomerID
INNER JOIN Warehouse.StockItems Product
ON Product.StockItemID = LineItem.StockItemID
INNER JOIN Application.People People
ON People.PersonID = SalesOrder.SalespersonPersonID
WHERE customers.CustomerName = 'Agrita Abele'
ORDER BY
[OrderDetails!1!SalesOrderID]
, [SalesPerson!2!SalesPersonName]
, [LineItem!3!LineTotal!ELEMENT]
FOR XML EXPLICIT, ROOT('SalesOrders') ;
For example, imagine that we want to expand the example in Listing so that we return an OrderDetails element that has two child elements. The first will contain the name of the salesperson who took the order. The second will be a repeating element, detailing the line items within the order. We could achieve this by using the query in Listing
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.