Как развернуть атрибуты столбца XML в T-SQL

Мне нужно выполнить сводку для столбца 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".

Источник

2 ответа

Решение

Взгляните на этот динамический стержень, а в последнее время - на то, что вам нужно 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)
Источник

Динамический свод не зря встроен в язык. Было бы необходимо просканировать всю таблицу, содержащую потенциальные имена столбцов, прежде чем структура результата была бы известна. В результате структура таблицы динамического сводного оператора будет неизвестна до времени выполнения. Это создает много проблем, касающихся разбора и интерпретации языка.

Если вы решите реализовать динамический поворот самостоятельно, не упустите возможности внедрения SQL. Обязательно применяйте QUOTENAME или эквивалентные значения, которые вы планируете использовать в качестве имен столбцов в своем результате. Также подумайте, какой результат вы хотите получить, если число различных значений в вашем источнике, которые станут именами столбцов, превышает допустимое количество столбцов набора результатов.

Источник
Другие вопросы по тегам