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

我正在使用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>的集合中).

我确信看到这么多非常相似的例子可以做到,但是我的解决方案再次出现了！

任何帮助感激不尽！

解决方法

此解决方案要求您的表具有主键(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

（编辑：核心网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!