asked
how to create order with multiple order lines with xml path?
I wanted to create xml output as formatted below for order processing: James Hendergart SO43659 23153.2339 Mountain-100 Black, 42 2024.994000 Mountain-100 Black, 44 6074.982000 Mountain-100 Black, 48 2024.994000 42525 Austell Road Austell Using the AdventureWorks2008R2 database I created the following script: Use AdventureWorks2008R2 go WITH XMLNAMESPACES ('urn:schemas-microsoft-com:datatypes' As dt) SELECT prsn.FirstName As [CustomerInfo/FirstName] ,prsn.LastName As [CustomerInfo/LastName] ,soh.SalesOrderNumber As [OrderHdrInfo/SalesOrderNo] ,soh.TotalDue As [OrderHdrInfo/TotalDue] ,prod.[Name] As [OrderDtlItems/OrderDtlItem/ProductName] ,sod.LineTotal As [OrderDtlItems/OrderDtlItem/LineTotal] ,addr.AddressLine1 As [ShipToInfo/ShipToAddr1] ,addr.City As [ShipToInfo/ShipToCity] FROM Person.Person prsn Inner Join Sales.Customer cust On prsn.BusinessEntityID = cust.PersonID Inner Join Sales.SalesOrderHeader soh On cust.CustomerID = soh.CustomerID Inner Join (Select Top 3 * From Sales.SalesOrderDetail) sod On soh.SalesOrderID = sod.SalesOrderID Inner Join Production.Product prod On sod.ProductID = prod.ProductID Inner Join Person.[Address] addr On soh.ShipToAddressID = addr.AddressID Inner Join Person.StateProvince sp On addr.StateProvinceID = sp.StateProvinceID Where prsn.BusinessEntityID = 1045 and soh.SalesOrderID = 43659 Order By prod.[Name] For XML Path('CustomerOrder') Instead of getting the desired output of one order with three detail lines, I get three orders, one for each detail line. How do I get the three OrderDtlItem nodes to fall within the OrderDtlItems node?
@JimO24 - you've asked a couple of questions now, and commented that the answers have helped - could you please accept those answers? Not only does it show users that the answer is right for the question, but it will add to your karma score too. Thanks!
One way to get the shape you need is to include the hierarchy in the select itself. Remove the joins to Product and SalesOrderDetail and include the info you need from them to the select list matching by the SalesOrderID. Here is the your query slightly restated to return desired shape:
;with xmlnamespaces ('urn:schemas-microsoft-com:datatypes' as dt)
select
prsn.FirstName As [CustomerInfo/FirstName],
prsn.LastName As [CustomerInfo/LastName],
soh.SalesOrderNumber As [OrderHdrInfo/SalesOrderNo],
soh.TotalDue As [OrderHdrInfo/TotalDue],
addr.AddressLine1 As [ShipToInfo/ShipToAddr1],
addr.City As [ShipToInfo/ShipToCity],
(
select top 3
prod.[Name] as [OrderDtlItem/ProductName],
s.LineTotal as [OrderDtlItem/LineTotal]
from Production.Product prod inner join Sales.SalesOrderDetail s
on prod.ProductID = s.ProductID
where
s.SalesOrderID = soh.SalesOrderID
for xml path(''), root('OrderDtlItems'), type
),
addr.AddressLine1 As [ShipToInfo/ShipToAddr1],
addr.City As [ShipToInfo/ShipToCity]
from Person.Person prsn inner Join Sales.Customer cust
on prsn.BusinessEntityID = cust.PersonID
inner Join Sales.SalesOrderHeader soh
on cust.CustomerID = soh.CustomerID
inner Join Person.[Address] addr
on soh.ShipToAddressID = addr.AddressID
inner Join Person.StateProvince sp
on addr.StateProvinceID = sp.StateProvinceID
where prsn.BusinessEntityID = 1045 and soh.SalesOrderID = 43659
for xml path('CustomerOrder');
The above returns the shape you need: James Hendergart SO43659 23153.2339 42525 Austell Road Austell Mountain-100 Black, 42 2024.994000 Mountain-100 Black, 44 6074.982000 Mountain-100 Black, 48 2024.994000 42525 Austell Road Austell Hope this helps. Oleg
9 People are following this question.
