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

我必要在表中的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”属性值集使列成为动态.

办理要领

以下是您的特定题目的完备谜底(请留意,在知道列应表现的次序时,从差异属性天生列表时存在列次序瑕玷：

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)

（编辑：湖南网）

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