TSQL:使用 FOR XML 每元素一行 [英] TSQL: One Row Per Element With FOR XML

问题描述

我有一个 SQL Server 2005 查询，它生成一个大的结果集(高达几 GB):

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>
...

我想更改查询，以便结果集不是一行包含具有多个产品元素的单个文档，而是返回多行，每行包含一个由单个 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>

最后，我想从 C# 使用 IDataReader 使用此查询，而不需要 SQL Server 或我的应用程序将整个结果集加载到内存中.是否可以对 SQL 进行任何更改以启用此方案?

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?

推荐答案

我想你想要这样的东西.(你可以在 AdventureWorks 上运行下面的查询)

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

这篇关于TSQL:使用 FOR XML 每元素一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！