TSQL: One Row Per Element With FOR XML

Question

I have a SQL Server 2005 query that generates a large result set (up to several gigabytes):

SELECT * FROM Product FOR XML PATH('Product')

Running the query generates a single row containing a document with many product elements:

Row 1:
<Product>
  <Name>Product One</Name>
  <Price>10.00</Price>
</Product>
<Product>
  <Name>Product Two</Name>
  <Price>20.00</Price>
</Product>
...

I would like to change the query so that instead of a result set with one row containing a single document with multiple product elements, it returns multiple rows each with a single document consisting of a sing Product element:

Row 1:
<Product>
  <Name>Product One</Name>
  <Price>10.00</Price>
</Product>

Row 2:
<Product>
  <Name>Product Two</Name>
  <Price>20.00</Price>
</Product>

In the end, I would like to consume this query from C# with an IDataReader without either SQL Server or my application having the entire result set loaded in to memory. Are there any changes I could make to the SQL to enable this scenario?

Answer 1

I think you want something like this.(you can run below query on AdventureWorks)

SELECT ProductID
      ,( SELECT * FROM Production.Product AS b WHERE a.ProductID= b.ProductID FOR XML PATH('Name') ) AS RowXML
FROM  Production.Product AS a