Нахождение порядка узлов в XML-документе в SQL Server

Question

Как я могу найти порядок узлов в документе XML?

У меня есть такой документ:

<value code="1">
    <value code="11">
        <value code="111"/>
    </value>
    <value code="12">
        <value code="121">
            <value code="1211"/>
            <value code="1212"/>
        </value>
    </value>
</value>

и я пытаюсь поместить эту вещь в таблицу, определенную как

CREATE TABLE values(
    code int,
    parent_code int,
    ord int
)

Сохранение порядка значений из XML-документа (их нельзя упорядочить по их коду). Я хочу быть в состоянии сказать

SELECT code 
FROM values 
WHERE parent_code = 121 
ORDER BY ord

и результаты должны, детерминистически

code
1211
1212

я пытался

SELECT 
    value.value('@code', 'varchar(20)') code, 
    value.value('../@code', 'varchar(20)') parent, 
    value.value('position()', 'int')
FROM @xml.nodes('/root//value') n(value)
ORDER BY code desc

Но он не принимает position() функция ('position()'может использоваться только внутри предиката или селектора XPath).

Я думаю, это возможно каким-то образом, но как?

Answer 1

Вы можете подражать position() функция путем подсчета числа узлов одного уровня, предшествующих каждому узлу:

SELECT
    code = value.value('@code', 'int'),
    parent_code = value.value('../@code', 'int'),
    ord = value.value('for $i in . return count(../*[. << $i]) + 1', 'int')
FROM @Xml.nodes('//value') AS T(value)

Вот набор результатов:

code   parent_code  ord
----   -----------  ---
1      NULL         1
11     1            1
111    11           1
12     1            2
121    12           1
1211   121          1
1212   121          2

Как это устроено:

• for $i in . предложение определяет переменную с именем $i который содержит текущий узел (.). По сути, это обходной путь для обхода отсутствия в XQuery XSLT-подобного current() функция.
• ../* выражение выбирает всех братьев и сестер (дочерних элементов родителя) текущего узла.
• [. << $i] Предикат фильтрует список братьев и сестер по отношению к тем, которые предшествуют (<<) текущий узел ($i).
• Мы count() количество предшествующих братьев и сестер, а затем добавить 1, чтобы получить позицию. Таким образом, первому узлу (у которого нет предшествующих братьев и сестер) назначается позиция 1.

Answer 2

Вы можете получить позицию XML, возвращенную x.nodes() функционировать так:

row_number() over (order by (select 0))

Например:

DECLARE @x XML
SET @x = '<a><b><c>abc1</c><c>def1</c></b><b><c>abc2</c><c>def2</c></b></a>'

SELECT
    b.query('.'),
    row_number() over (partition by 0 order by (select 0))
FROM
    @x.nodes('/a/b') x(b)

Answer 3

SQL Server row_number() фактически принимает столбец xml-узлов для упорядочения. В сочетании с рекурсивным CTE вы можете сделать это:

declare @Xml xml = 
'<value code="1">
    <value code="11">
        <value code="111"/>
    </value>
    <value code="12">
        <value code="121">
            <value code="1211"/>
            <value code="1212"/>
        </value>
    </value>
</value>'

;with recur as (
    select
        ordr        = row_number() over(order by x.ml),
        parent_code = cast('' as varchar(255)),
        code        = x.ml.value('@code', 'varchar(255)'),
        children    = x.ml.query('./value')
    from @Xml.nodes('value') x(ml)
    union all
    select
        ordr        = row_number() over(order by x.ml),
        parent_code = recur.code,
        code        = x.ml.value('@code', 'varchar(255)'),
        children    = x.ml.query('./value')
    from recur
    cross apply recur.children.nodes('value') x(ml)
)
select *
from recur
where parent_code = '121'
order by ordr

Кроме того, вы можете сделать это, и он будет делать то, что вы ожидаете:

select x.ml.query('.')
from @Xml.nodes('value/value')x(ml)
order by row_number() over (order by x.ml)

Почему, если это работает, вы не можете просто order by x.ml прямо без row_number() over вне меня.

Answer 4

Ответ от erikkallen абсолютно правильный.

Однако, если исходный документ / схема может быть изменен, альтернативой является сохранение позиции / индекса в атрибуте. Я использую сочетание обоих подходов, в зависимости от того, кто является "создателем" XML и типа запросов, которые необходимо выполнить для него. В конце дня я сожалею о большинстве случаев использования XML, за исключением, возможно, "глупого хранилища" в SQL Server, и обычно счастлив, что могу вывести его (XML) для нормализованных таблиц.

Приятно иметь дело с не упомянутыми ограничениями продуктов "уровня предприятия" - чудеса никогда не кончаются.

Answer 5

Согласно этому документу и этой записи подключения это невозможно, но запись подключения содержит два обходных пути.

Я делаю это так:

WITH n(i) AS (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9),
     o(i) AS (SELECT n3.i * 100 + n2.i * 10 + n1.i FROM n n1, n n2, n n3)
SELECT v.value('@code', 'varchar(20)') AS code,
       v.value('../@code', 'varchar(20)') AS parent,
       o.i AS ord
  FROM o
 CROSS APPLY @xml.nodes('/root//value[sql:column("o.i")]') x(v)
 ORDER BY o.i

Answer 6

Я вижу ответ @Ben и... получаю новое решение

 row_number() over (order by (select null))

как

  SELECT value.value('@code', 'varchar(20)') code, 
  value.value('../@code', 'varchar(20)') parent, 
  row_number() over (order by (select null))
  FROM @xml.nodes('/root//value') n(value)