Of course that hasn't transposed my XML rows into columns, and obviously is fixed with fields anyway.. I have been trying various "Dynamic SQL" approaches where the SQL performs a distinct selection of (in my case) the <Text> node, and then uses some sort of Pivot? but I couldn't seem to find the magic combination to return the results I need as a dynamic set of columns for each row (<Item> within the collection of <Items>).

我相信看到这么多非常相似的例子是可以做到的，但是我又找不到解决方案！

I'm sure it can be done having seen so many very similar examples, however again the solution eludes me!

感谢您的帮助！

推荐答案

解析 XML 相当昂贵，因此您可以创建一个具有名称-值的临时表，而不是解析一次来构建动态查询和一次获取数据列表，然后将其用作动态数据透视查询的来源.dense_rank 用于创建 ID 以进行旋转.为了在动态查询中构建列列表，它使用 for xml path('') 技巧.

Parsing the XML is fairly expensive so instead of parsing once to build a dynamic query and once to get the data you can create a temporary table with a Name-Value list and then use that as the source for a dynamic pivot query. dense_rank is there to create the ID to pivot around. To build the column list in the dynamic query it uses the for xml path('') trick.

此解决方案要求您的表具有主键 (ID).如果您在变量中包含 XML，则可以稍微简化一下.

This solution requires that your table has a primary key (ID). If you have the XML in a variable it can be somewhat simplified.

select dense_rank() over(order by ID, I.N) as ID,
       F.N.value('(Text/text())[1]', 'varchar(max)') as Name,
       F.N.value('(Value/text())[1]', 'varchar(max)') as Value
into #T
from YourTable as T
  cross apply T.XMLCol.nodes('/Items/Item') as I(N)
  cross apply I.N.nodes('FormItem') as F(N)

declare @SQL nvarchar(max)
declare @Col nvarchar(max)

select @Col = 
  (
  select distinct ','+quotename(Name)
  from #T
  for xml path(''), type
  ).value('substring(text()[1], 2)', 'nvarchar(max)')

set @SQL = 'select '+@Col+'
            from #T
            pivot (max(Value) for Name in ('+@Col+')) as P'

exec (@SQL)

drop table #T

SQL 小提琴