Recherche d'un ordre de noeud dans un document XML dans SQL Server

Comment puis-je trouver l'ordre des nœuds dans un document XML?

Ce que j'ai est un document comme ceci:

<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>

et je vais essayer d'obtenir cette chose dans une table est définie comme

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

La préservation de l'ordre des valeurs à partir du document XML (ils ne peuvent pas être commandés par leur code). Je veux être capable de dire

SELECT code 
FROM values 
WHERE parent_code = 121 
ORDER BY ord

et les résultats devraient, de façon déterministe, être

code
1211
1212

J'ai essayé

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

Mais il n'accepte pas la position() fonction ('position()' ne peut être utilisé à l'intérieur d'un prédicat ou XPath sélecteur).

Je suppose que c'est possible d'une certaine façon, mais comment?

source d'informationauteur erikkallen

31

Vous pouvez émuler le position() fonction en comptant le nombre de nœuds frère précédant chaque nœud:
```
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)
```
Voici l'ensemble des résultats:
```
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
```
Comment cela fonctionne:
- La $i . clause définit une variable nommée $i qui contient le nœud courant (.). C'est un hack pour contourner XQuery l'absence d'une transformation XSLT-comme current() fonction.
- La ../* expression sélectionne tous les frères et sœurs (les enfants de parent du noeud courant.
- La [. << $i] prédicat filtres de la liste des frères et sœurs de ceux qui le précèdent (<<) le nœud courant ($i).
- Nous count() le nombre de précédents, frères et sœurs, puis ajouter 1 pour obtenir la position. De cette façon, le premier nœud (qui n'a pas de précédent dans la fratrie) est affecté à un poste de 1.

Vous pouvez obtenir la position de l'xml retourné par un x.nodes() fonction de la façon:

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

Par exemple:

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)

3

La réponse par erikkallen est absolument correcte.

Toutefois, si le document original/schéma peut être modifié, une alternative consiste à stocker la position/d'index dans un attribut. J'utilise un mélange de ces deux approches, selon qui le "créateur" de l'XML est et le type de requêtes qui doivent être effectuées. À la fin de la journée, j'rue de la plupart de l'utilisation de XML sauf peut-être "bête de stockage" dans SQL Server et suis heureux quand je peux faire un dump (XML) pour les tables normalisées.

Heureux de traiter avec le bon de rappeler les limites de classe entreprise de produits -- les merveilles jamais de fin.

SQL Server row_number() accepte réellement un xml-colonne nœuds à l'ordre par. Combiné avec un expression de table commune récursive vous pouvez faire ceci:

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

En aparté, vous pouvez le faire et il le fera qu'attendez-vous:

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

Pourquoi, si cela fonctionne, vous ne pouvez pas simplement order by x.ml directement, sans row_number() over est au delà de moi.

Selon cette document et ce connecter l'entrée il n'est pas possible, mais le Connecter entrée contient deux solutions de contournement.

Je fais comme ça:

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

Je voir la réponse par @Ben et... obtenir la nouvelle solution

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

comme

  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)

Vous devez vous connecter pour publier un commentaire.