Выберите узлы XML в качестве атрибутов

Я предполагаю, что ответ на мой вопрос будет чем-то простым, чего я сам не могу понять. Вот сценарий:

Я использую SQL Server 2008 R2, где в таблице есть столбец XML, в котором данные сохраняются в следующем формате:

<Person>
    <firstName>John</firstName>
    <lastName>Lewis</lastName>
</Person>

Узел Person может иметь любое количество дочерних узлов, для которых имена элементов могут быть разными (заранее неизвестными). Я ищу запрос для возврата XML, который имеет значения для всех узлов в качестве атрибутов.

Таким образом, вывод для приведенного выше XML должен быть:

<Person firstName="John" lastName="Lewis"/>

Я не могу придумать запрос, чтобы получить вышеуказанный результат. Я не хочу использовать такой запрос, как

Select 
      PersonColumn.value('(/Person/firstName)[1]', 'varchar(100)') AS '@firstName'
    , PersonColumn.value('(/Person/lastName)[1]', 'varchar(100)') AS '@lastName'
FROM MyTable
WHERE MyTable.MyPrimaryKey=1
FOR XML PATH('Person'), TYPE

поскольку я не знаю, какие узлы могут быть под узлом Person.


xml tsql sql-server-2008-r2 xquery sqlxml
person Wiz    schedule 21.11.2012    source источник
comment
Вы говорите, что узел "Человек" может иметь любое количество потомков, имена которых могут быть разными. Приведите пример (несколько потомков с разными именами) и то, что вы ожидаете в результате. Приведенный вами пример с одним потомком бесполезен.   -  person RBarryYoung    schedule 21.11.2012
comment
Я имел в виду, что узел Person может иметь не только firstName и lastName. Под ним может быть больше узлов, но ни один из узлов не будет повторяться, то есть firstName появится только один раз. Надеюсь, это прояснило ситуацию. Слово Потомок могло вас запутать. В моем примере показаны 2 потомка человека: firstName и lastName. Возможно, более подходящим термином были бы дочерние узлы.   -  person Wiz    schedule 21.11.2012
comment
Ага, вы думаете, что имена элементов element неизвестны?   -  person RBarryYoung    schedule 21.11.2012
comment
Это было бы тривиально с хранимой процедурой CLR (C #). Это вариант?   -  person Joshua Honig    schedule 21.11.2012
comment
См. Также stackoverflow.com/questions/1580077/   -  person Joshua Honig    schedule 21.11.2012

Ответы (2)


arrow_upward
2
arrow_downward

Я пытался сделать это

select
    PersonColumn.query('
        element Person {
            for $i in /Person/*
            return attribute {local-name($i)} {string($i)}
        }
    ')
from MyTable

но оказывается, что нельзя использовать имена динамических атрибутов

XQuery [MyTable.PersonColumn.query()]: Only constant expressions are supported for the name expression of computed element and attribute constructors.: select PersonColumn.query(' element Person { for $i in /Person/* return attribute {local-name($i)} {string($i)} } ') from MyTable

Так что лучшее, что я могу сделать, это

select 
    cast(
        '<Person ' + 
        (
            select
                PersonColumn.query('
                for $i in /Person/*
                return concat(local-name($i), "=""", data($i), """")
                ').value('.', 'nvarchar(max)')
            for xml path('')
        ) + '/>'
    as xml)
from MyTable

Это тоже можно сделать

select
    cast(
        '<Person ' + 
        PersonColumn.query('
            for $i in /Person/*
            return concat(local-name($i), "=""", data($i), """")
        ').value('.', 'nvarchar(max)') +
        '/>'
      as xml)
from MyTable

но это не сработает, если ваши данные содержат некоторые символы, такие как < > и т. д.

person Roman Pekar    schedule 21.11.2012
comment
+1: ваш лучший результат на самом деле чертовски хорош. Один из моих любимых на свете. - person RBarryYoung; 21.11.2012
comment
Это кажется довольно близким. Я дам ему попробовать. Спасибо - person Wiz; 22.11.2012
comment
Это отлично сработало. В сценарии, который я использую, не будет ни <, ни >. Спасибо. - person Wiz; 22.11.2012
comment
@Roman: У вас есть ссылка, по которой я могу сослаться на различные вещи, которые я могу использовать в PersonColumn.query? Я только что понял, что это не только < и >, где он ломается, но даже для &, поэтому нужно знать, есть ли что-нибудь, что будет читать необработанный xml, чем преобразовывать &amp; в &, что происходит в настоящее время с data($i) - person Wiz; 22.11.2012
comment
эта вещь внутри запроса называется XQuery, вы можете прочитать об этом здесь msdn.microsoft. ru / en-us / library / ms189075.aspx - person Roman Pekar; 22.11.2012

arrow_upward
0
arrow_downward

Похоже, вы действительно хотите PIVOT на именах элементов, которые являются прямыми дочерними элементами узла Person. Если вы должны сделать все это в TSQL, вы должны создать запрос динамически. См. Как мне Посмотрите на атрибуты столбца XML в T-SQL для вдохновения. В этом случае выражение поворота также было значением атрибута, а в вашем случае - именем элемента.

Я чувствую себя обязанным указать на очевидное - такое решение, вероятно, будет плохо работать, результаты будет трудно использовать (поскольку имена столбцов и их количество неизвестны) и может быть восприимчивым к атакам с использованием sql-инъекций в зависимости от контекста.

person Joshua Honig    schedule 21.11.2012
comment
Обычный PIVOT не может этого сделать, это можно сделать только с помощью динамического PIVOT. Учитывая, что вам все равно придется вырезать его из XML в виртуальную таблицу SQL, выполнить PIVOT, а затем заново скомпилировать его обратно в XML, здесь, похоже, слишком много размахивания руками без реальных примеров. - person RBarryYoung; 21.11.2012