September 13, 2011 at 10:18 am
Hello,
I'm can't seem to figure out how to get my XML to format the way I need it. I created a sample using the adventure work database. Here is how i want my XML to look.
<Order>
<salesorderid>43659</salesorderid>
<CustomerID>676</CustomerID>
<OrderDate>2001-07-01T00:00:00</OrderDate>
<DueDate>2001-07-13T00:00:00</DueDate>
<ShipDate>2001-07-08T00:00:00</ShipDate>
<Status>5</Status>
<ProductIDs>
<ProductID>776</ProductID>
<ProductID>5</ProductID>
<ProductID>778</ProductID>
<ProductID>771</ProductID>
<ProductID>772</ProductID>
<ProductID>773</ProductID>
<ProductID>774</ProductID>
</ProductIDs>
</Order>
and here is how it's looks right now.
<Order>
<salesorderid>43659</salesorderid>
<CustomerID>676</CustomerID>
<OrderDate>2001-07-01T00:00:00</OrderDate>
<DueDate>2001-07-13T00:00:00</DueDate>
<ShipDate>2001-07-08T00:00:00</ShipDate>
<Status>5</Status>
<ProductID>776</ProductID>
</Order>
<Order>
<salesorderid>43659</salesorderid>
<CustomerID>676</CustomerID>
<OrderDate>2001-07-01T00:00:00</OrderDate>
<DueDate>2001-07-13T00:00:00</DueDate>
<ShipDate>2001-07-08T00:00:00</ShipDate>
<Status>5</Status>
<ProductID>777</ProductID>
</Order>
<Order>
<salesorderid>43659</salesorderid>
<CustomerID>676</CustomerID>
<OrderDate>2001-07-01T00:00:00</OrderDate>
<DueDate>2001-07-13T00:00:00</DueDate>
<ShipDate>2001-07-08T00:00:00</ShipDate>
<Status>5</Status>
<ProductID>778</ProductID>
</Order>
my query looks like this:
SELECT Sales.SalesOrderHeader.salesorderid, Sales.SalesOrderHeader.CustomerID,Sales.SalesOrderHeader.OrderDate, Sales.SalesOrderHeader.DueDate, Sales.SalesOrderHeader.ShipDate, Sales.SalesOrderHeader.Status,
Sales.SalesOrderDetail.ProductID
FROM Sales.SalesOrderHeader INNER JOIN
Sales.SalesOrderDetail ON Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID
Where Sales.SalesOrderHeader.salesorderid = 43659 FOR XML PATH ('Order')
Thanks for your help
-John
September 13, 2011 at 10:31 am
You are oging to need to use subqueries to format the XML the way you want it.
The query would look something like:
SELECT Sales.SalesOrderHeader.salesorderid,
Sales.SalesOrderHeader.CustomerID,
Sales.SalesOrderHeader.OrderDate,
Sales.SalesOrderHeader.DueDate,
Sales.SalesOrderHeader.ShipDate,
Sales.SalesOrderHeader.Status,
(SELECT Sales.SalesOrderDetail.ProductID
from Sales.SalesOrderDetail
where
Sales.SalesOrSales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID
FOR XML PATH('Product'), ROOT('ProductIDs'), TYPE) n
FROM Sales.SalesOrderHeader
where Sales.SalesOrderHeader.salesorderid =43659
FOR XML PATH ('Order')
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 13, 2011 at 11:34 am
Thank You That was exactly what I was looking for.
-John
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply