如何在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”属性值的不同集合使列具有动态性。

- Stephen Mesa

---

加1个赞，代码格式非常漂亮。 - Lukasz Lysik

Answer 1

请参考这个动态旋转表，以及最近的这个 - 你基本上需要能够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)

- Cade Roux

---

哇，这非常令人印象深刻！我已经用我的示例数据使其正常运行，但我想象中还需要进行许多注入攻击的预防和考虑，就像 Steve 指出的那样。感谢你的帮助！ - Stephen Mesa

是的，你可以在SQL中使用AS QUOTENAME({FieldName})，但我不确定如何开始转义XQuery部分：@FieldName="{FieldName}"。 - Cade Roux

Answer 2

动态透视表没有内置到语言中，这是有充分理由的。在确定结果结构之前，需要扫描包含潜在列名的整个表。因此，在运行时之前，动态透视表语句的表结构是未知的。这会导致许多关于解析和语言解释的问题。

如果您决定自己实现动态透视表，请注意 SQL 注入的机会。请确保对您计划在结果中用作列名的值应用 QUOTENAME 或等效方法。还要考虑一下，如果源中将成为列名的不同值的数量超过结果集允许的列数，您想要什么结果。

- Steve Kass