使用SQL将XML结构转置/展平为列 [英] Using SQL to transpose/flatten XML structure to columns

问题描述

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

I am using SQL Server (2008/2012) and I know there are similar answers from lots of searching, however I can't seem to find the appropriate example/pointers for my case.

我在SQL Server表中有一个XML列，用于保存以下数据:

I have an XML column in a SQL Server table holding this data:

<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个)表单项.

So even though the structure of <FormItem> is going to be the same, I can have multiple (most commonly no more than 20-30) sets of form items..

我本质上是试图从SQL中以以下格式返回查询，即基于/FormItem/Text的动态列:

I am essentially trying to return a query from SQL in the format below, i.e. dynamic columns based on /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          

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

So, at the moment I had the following:

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?但我似乎找不到神奇的组合来将我需要的结果作为每行的动态列集(在<Items>集合中的<Item>)返回.

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是相当昂贵的，因此可以解析一个具有Name-Value的临时表，而不是解析一次以创建动态查询并获取数据.列表，然后将其用作动态数据透视查询的源.
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提琴

这篇关于使用SQL将XML结构转置/展平为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！