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?

Brott

Can you show by means of a sample XML what your desired output should look like? What exactly do you mean by each Product element is returned on its own row ? – Grania

@marc_s, I've edited the question to (hopefully) make that more clear. Normally a FOR XML will return a single row containing a single XML document with all of the elements inside it. I'm wanting to instead break that document over several rows, one per Product element. The kicker is that it needs to happen in such a way that the entire document doesn't need to be rendered in memory first, as happens with .nodes(). – Brott

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

Pardner

This was the key: running a subquery with a FOR XML, but not using FOR XML on the full query. – Brott

Answer 2

I Think This will give you good result,

SELECT top 3 Productid,Name, XmlColumn from Production.Product a cross apply ( select  top 1  a.* from Production.Product b FOR XML PATH('test')) as outputdata(XmlColumn)

Hummock

Answer 3

If you know the column names, I'd rather avoid the nested select to the table

SELECT ProductID,(SELECT Name, Price FOR XML RAW('Product'),ELEMENTS) AS RowXML
FROM  Production.Product AS a

Preceptor

it s the faster, in case you have to read from a view to don t recall more 1 time... – Hummer

Answer 4

if PRICE and NAME are in one table Product

SELECT * FROM Product FOR XML AUTO, ELEMENTS

or, if not, you can create a view vw_Product which will return only 2 columns that you want and then write

SELECT * FROM vw_Product as Product FOR XML AUTO, ELEMENTS

you can use XmlReader to read from the results of this query row by row, to avoid loading big XML document in memory. See http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executexmlreader.aspx

in your query you used PATH('Product'), an this is why it produced a single node with all products.

Turne