Hi

In the follwing query, I use a very bad way to compute the last field
(Position)

using 1 + (ROW_NUMBER() OVER (ORDER BY att) - 1) /3 as Position

to find the instance number of the nodes/attrbutes.

The final result is what I expect , but Only if I have 3 attributes by
nodes - (which coresspond to 3 field in my MINIRES Table)

Does anybody knows if there is a better way (and more elegant -) to get
this numbering of nodes ?


declare @xml XML
/*
select @xml = (

select
rtrim(code_reseau_minitel) AS code_reseau_minitel ,
service,
rtrim(nom_facture) AS nom_facture
from
LP.dbo.Minires AS Minires where code_reseau_minitel = 'CAPA'
for XML AUTO, root('toto')
)
*/
select @xml = -- replacement for above query
'<toto>
<Minires code_reseau_minitel="CAPA" service="CPRDEVISES"
nom_facture="CAPA" />
<Minires code_reseau_minitel="CAPA" service="REPRISES "
nom_facture="CAPA" />
<Minires code_reseau_minitel="CAPA" service="STCKAGENCE"
nom_facture="CAPA" />
</toto>'

SELECT
T.att.value('local-name(.)', 'varchar(50)') AS Fieldname,
T.att.value('.', 'varchar(100)') AS Field_Value ,
1 + (ROW_NUMBER() OVER (ORDER BY att) - 1) /3 as Position
FROM
@xml.nodes('//@*') T(att);


/* result of above:

code_reseau_minitel CAPA 1
service CPRDEVISES 1
nom_facture CAPA 1
code_reseau_minitel CAPA 2
service REPRISES 2
nom_facture CAPA 2
code_reseau_minitel CAPA 3
service STCKAGENCE 3
nom_facture CAPA 3

*/