使用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>节点的不同选择，然后使用某种枢轴？但是，我似乎找不到魔术组合，以便为每个行(<Items>集合中的<Item>)返回所需的动态列集作为结果。

我相信它可以做到，因为看到了很多非常相似的例子，但是解决方案仍然让我困惑！

非常感谢您的帮助！

- Dav.id

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

- Mikael Eriksson

---

谢谢！这正是我所需要的。实际上，我有一个比示例更高级别的需求，但那只是为了将<Items>分组在一起... 但关键部分是将可变的XML节点<Items><Item>显示为具有重复<Items>行的列..非常感谢您提供如此详细的示例！ - Dav.id

Answer 2

select Tab.Col.value('(FormItem[Text = "FirstName"]/Value)[1]', 'varchar(32)') as FirstName, 
        Tab.Col.value('(FormItem[Text = "LastName"]/Value)[1]', 'varchar(32)') as LastName, 
        Tab.Col.value('(FormItem[Text = "Age"]/Value)[1]', 'int') as Age
from @Questions.nodes('/Items/Item') Tab(Col)

- muhmud

---

谢谢，很好的例子，实际上以前没怎么见过这种技巧，不过它不能动态地将字段显示为列——如果您事先知道列数，那可能还好，然而@Mikael的上面的例子正是我所需要的。无论如何，感谢您的回复，再次非常简单干净的例子肯定会有用。 - Dav.id

Answer 3

为了完整性和帮助他人，我想添加自己的答案。但这绝对是基于@Mikael的巨大帮助！所以再次强调，这只是为了完整性 - 所有荣誉归功于@Mikael。

基本上，我最终使用了以下存储过程。我需要选择一些数据/筛选条件，并获取一些联接数据，还允许对一些输入参数进行布尔过滤。然后跳转到下一部分，创建我的关系数据和所需XML节点的临时表，通过交叉应用。最后一步是从所选XML节点中旋转结果/动态创建列。

CREATE PROCEDURE [dbo].[usp_RPT_ExtractFlattenentries]
    @CompanyID          int,
    @MainSelector       nvarchar(50) = null,
    @SecondarySelector      nvarchar(255) = null,
    @DateFrom           datetime = '01-jan-2012',
    @DateTo             datetime = '31-dec-2100',
    @SysReference       nvarchar(20) = null
AS
BEGIN
    SET NOCOUNT ON;

    --  Create the table var to hold the XML form data from the entries
    declare @FeedbackXml table (
        ID int identity primary key,
        XMLCol xml,
        CompanyName nvarchar(20),
        SysReference nvarchar(20),
        RecordDate datetime,
        EntryName  nvarchar(255),
        MainSelector nvarchar(50)
    )

    --  STEP 1: Get the raw submission data based on the params passed in
    --  *Note: The double casting is necessary as the "form" field is nvarchar (not varchar) and we need xml in UTF-8 format
    begin
        insert into @FeedbackXml
            (XMLCol, CompanyName, SysReference, RecordDate, EntryName, MainSelector)
        select cast(cast(e.form as nvarchar(max)) as xml), c.name, e.SysReference, e.RecordDate, e.name, e.wizard
        from 
            entries s
        left join
            companies o on e.companies = c.ID
        where 
            (@CompanyID = -1 or @CompanyID = e.companies)
        and
            (@MainSelector is null or @MainSelector = e.wizard)
        and
            (@SecondarySelector is null or @SecondarySelector = e.name)
        and
            (@SysReference is null or @SysReference = e.SysReference)
        and
            (e.RecordDate >= @DateFrom and e.RecordDate <= @DateTo)
    end

    --  STEP 2: Flatten the required XML structure to provide a base for the pivot, and include other fields we wish to output
    select dense_rank() over(order by ID) as ID,
            T.RecordDate, T.CompanyName, T.SysReference, T.EntryName, T.MainSelector,
            F.N.value('(FieldNameNode/text())[1]', 'nvarchar(max)') as FieldName,
            F.N.value('(FieldNameValue/text())[1]', 'nvarchar(max)') as FieldValue
    into #TempData
    from @FeedbackXml as T
        cross apply T.XMLCol.nodes('/root/companies/') as I(N) -- Xpath to the desired node start point
        cross apply I.N.nodes('company') as F(N) -- The actual node collection that forms the "field name" and "field value" data

    --  STEP 3: Pivot the #TempData table creating a dynamic column structure based on the selected XML nodes in step 2
    declare @SQL nvarchar(max)
    declare @Col nvarchar(max)

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

    set @SQL = 'select CompanyName, SysReference, EntryName, MainSelector, RecordDate, '+@Col+'
                from #TempData
                pivot (max(FieldValue) for FieldName in ('+@Col+')) as P'

    exec (@SQL)
    drop table #TempData

END

我只是为了提供一个完整的观点，并可能帮助其他人才添加这个答案。

再次强调，这只是我的个人看法。

- Dav.id