sql - 如何在 T-SQL 中透视 XML 列的属性

Question

我需要对表中的 XML 列执行数据透视，其中 XML 包含具有多个属性的多个元素。每个元素中的属性始终相同，但元素的数量会有所不同。让我举个例子...

FormEntryId |               FormXML                                    | DateCreated
====================================================================================
1           |<Root>                                                    | 10/15/2009
            |  <Form>                                                  |
            |    <FormData FieldName="Username" FieldValue="stevem" /> |
            |    <FormData FieldName="FirstName" FieldValue="Steve" /> |
            |    <FormData FieldName="LastName" FieldValue="Mesa" />   |
            |  </Form>                                                 |
            |</Root>                                                   |
            |                                                          |
------------------------------------------------------------------------------------
2           |<Root>                                                    | 10/16/2009
            |  <Form>                                                  |
            |    <FormData FieldName="Username" FieldValue="bobs" />   |
            |    <FormData FieldName="FirstName" FieldValue="Bob" />   |
            |    <FormData FieldName="LastName" FieldValue="Suggs" />  |
            |    <FormData FieldName="NewField" FieldValue="test" />   |
            |  </Form>                                                 |
            |</Root>                                                   |

我需要为每个不同的 FieldName 属性值(在本例中为 Username、FirstName、LastName 和 NewField)创建一个结果集，并将其相应的 FieldValue 属性作为值。我上面给出的示例的结果如下所示:

FormEntryId | Username | FirstName | LastName | NewField | DateCreated
======================================================================
1           | stevem   | Steve     | Mesa     | NULL     | 10/15/2009
----------------------------------------------------------------------
2           | bobs     | Bob       | Suggs    | test     | 10/16/2009

我想出了一个方法来用静态列来完成这个

SELECT
    FormEntryId,
    FormXML.value('/Root[1]/Form[1]/FormData[@FieldName="Username"][1]/@FieldValue','varchar(max)') AS Username,
    FormXML.value('/Root[1]/Form[1]/FormData[@FieldName="FirstName"][1]/@FieldValue','varchar(max)') AS FirstName,
    FormXML.value('/Root[1]/Form[1]/FormData[@FieldName="LastName"][1]/@FieldValue','varchar(max)') AS LastName,
    FormXML.value('/Root[1]/Form[1]/FormData[@FieldName="NewField"][1]/@FieldValue','varchar(max)') AS NewField,
    DateCreated
FROM FormEntry

不过，我想看看是否有一种方法可以根据不同的“FieldName”属性值集使列动态化。

Answer 1

看看this dynamic pivot最近this one - 您基本上需要能够 SELECT DISTINCT FieldName 才能使用此技术动态构建查询。

这是针对您的特定问题的完整答案(请注意，在知道列应该以什么顺序显示时，从不同的属性生成列表时存在列顺序弱点):

DECLARE @template AS varchar(MAX)
SET @template = 'SELECT 
    FormEntryId
    ,{@col_list}
    ,DateCreated 
FROM FormEntry'

DECLARE @col_template AS varchar(MAX)
SET @col_template = 'FormXML.value(''/Root[1]/Form[1]/FormData[@FieldName="{FieldName}"][1]/@FieldValue'',''varchar(max)'') AS {FieldName}'

DECLARE @col_list AS varchar(MAX)

;WITH FieldNames AS (
    SELECT DISTINCT FieldName
    FROM FormEntry
    CROSS APPLY (
        SELECT X.FieldName.value('@FieldName', 'varchar(255)')
        FROM FormXML.nodes('/Root[1]/Form[1]/FormData') AS X(FieldName)
    ) AS Y (FieldName)
)
SELECT @col_list = COALESCE(@col_list + ',', '') + REPLACE(@col_template, '{FieldName}', FieldName)
FROM FieldNames

DECLARE @sql AS varchar(MAX)
SET @sql = REPLACE(@template, '{@col_list}', @col_list)

EXEC (@sql)