Ricerca dell’ordine dei nodes nel documento XML in SQL Server

Come posso trovare l’ordine dei nodes in un documento XML?

Quello che ho è un documento come questo:

           

e sto cercando di ottenere questa cosa in un tavolo definito come

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

Preservare l’ordine dei valori dal documento XML (non possono essere ordinati dal loro codice). Voglio poter dire

 SELECT code FROM values WHERE parent_code = 121 ORDER BY ord 

e i risultati dovrebbero, deterministicamente, essere

     code 1211 1212 

    Ho provato

     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 

    Ma non accetta la funzione position() (‘ position() ‘ può essere usata solo all’interno di un predicato o selettore XPath).

    Immagino sia ansible in qualche modo, ma come?

    È ansible emulare la funzione position() contando il numero di nodes fratelli precedenti a ciascun nodo:

     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) 

    Ecco il set di risultati:

     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 

    Come funziona:

    • Il for $i in . la clausola definisce una variabile denominata $i che contiene il nodo corrente ( . ). Questo è fondamentalmente un trucco per aggirare la mancanza di XQuar di una funzione current() simile a XSLT.
    • L'espressione ../* seleziona tutti i fratelli (figli del genitore) del nodo corrente.
    • Il [. << $i] [. << $i] predicato [. << $i] filtra l'elenco dei fratelli a quelli che precedono ( << ) il nodo corrente ( $i ).
    • count() il numero dei fratelli precedenti e quindi aggiungiamo 1 per ottenere la posizione. In questo modo al primo nodo (che non ha fratelli precedenti) viene assegnata una posizione di 1.

    È ansible ottenere la posizione del xml restituito da una funzione x.nodes() modo:

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

    Per esempio:

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

    La risposta di erikkallen è assolutamente corretta.

    Tuttavia, se il documento / schema originale può essere modificato, un’alternativa è quella di memorizzare la posizione / l’indice in un attributo. Uso un mix di entrambi gli approcci, a seconda di chi sia il “creatore” dell’XML e del tipo di query che devono essere eseguite su di esso. Alla fine della giornata, mi riferisco soprattutto all’XML, ad eccezione forse di “memoria stupida” in SQL Server e di solito sono contento quando posso scaricarlo (XML) per tabelle normalizzate.

    Buon rapporto con le limitazioni non menzionate dei prodotti “di livello enterprise”: le meraviglie non finiscono mai.

    Il row_number() SQL Server accetta effettivamente una colonna xml-node per ordinare. Combinato con una CTE ricorsiva puoi farlo:

     declare @Xml xml = '          ' ;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 

    Per inciso, puoi farlo e farà ciò che ti aspetti:

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

    Perché, se funziona, non puoi semplicemente order by x.ml direttamente senza row_number() over è oltre me.

    Secondo questo documento e questa voce di connessione non è ansible, ma la voce Connect contiene due soluzioni alternative.

    Lo faccio così:

     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, oi AS ord FROM o CROSS APPLY @xml.nodes('/root//value[sql:column("oi")]') x(v) ORDER BY oi 

    Vedo risposta da @ Ben e … ricevere nuove soluzioni

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

    come

      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)