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

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