sql-server - 使用 SQL 将 XML 结构转置/展平为列

Question

我正在使用 SQL Server (2008/2012)，并且我知道从大量搜索中得到了类似的答案，但是我似乎找不到适合我的案例的示例/指针。

我在 SQL Server 表中有一个 XML 列保存此数据:

<Items>
 <Item>
  <FormItem>
    <Text>FirstName</Text>
    <Value>My First Name</Value>
  </FormItem>
  <FormItem>
    <Text>LastName</Text>
    <Value>My Last Name</Value>
  </FormItem>
  <FormItem>
    <Text>Age</Text>
    <Value>39</Value>
  </FormItem>
 </Item>
 <Item>
  <FormItem>
    <Text>FirstName</Text>
    <Value>My First Name 2</Value>
  </FormItem>
  <FormItem>
    <Text>LastName</Text>
    <Value>My Last Name 2</Value>
  </FormItem>
  <FormItem>
    <Text>Age</Text>
    <Value>40</Value>
  </FormItem>
 </Item>
</Items>

因此，即使 <FormItem> 的结构相同，我也可以拥有多组(通常不超过 20-30 组)表单项..

我实际上是在尝试以下面的格式从 SQL 返回查询，即基于/FormItem/Text 的动态列:

FirstName         LastName         Age    ---> More columns as new `<FormItem>` are returned
My First Name     My Last Name     39          Whatever value etc..
My First Name 2   My Last Name 2   40          

所以，目前我有以下内容:

select 
    Tab.Col.value('Text[1]','nvarchar(100)') as Question,
    Tab.Col.value('Value[1]','nvarchar(100)') as Answer
from
    @Questions.nodes('/Items/Item/FormItem') Tab(Col)

当然，这还没有将我的 XML 行转置为列，并且显然已经用字段固定了。我一直在尝试各种“动态 SQL”方法，其中 SQL 执行(在我的例子中)<Text> 的不同选择节点，然后使用某种 Pivot？但我似乎找不到神奇的组合来返回我需要的结果作为每行的一组动态列( <Item> 集合中的 <Items> )。

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

感谢收到的任何帮助!

Answer 1

解析 XML 的成本相当高，因此与其解析一次以构建动态查询，再解析一次以获取数据，不如创建一个包含名称-值列表的临时表，然后将其用作动态数据透视查询的源。
dense_rank 用于创建要旋转的 ID。
为了在动态查询中构建列列表，它使用了 for xml path('') 技巧。

此解决方案要求您的表具有主键 (ID)。如果您在变量中有 XML，它可以稍微简化。

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 Fiddle