Help for FOR XML PATH

• john.parker 39819

  Grasshopper

  Points: 18

  More actions

  September 13, 2011 at 10:18 am

  #257169

  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

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  September 13, 2011 at 10:31 am

  #1380802

  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?

• john.parker 39819

  Grasshopper

  Points: 18

  More actions

  September 13, 2011 at 11:34 am

  #1380850

  Thank You That was exactly what I was looking for.

  -John