Skip to content Skip to sidebar Skip to footer

Order Attribute Names In Xml Using T-sql

I need to order attribute names in alphabetic order and I've created following code, but it left xml as it is: DECLARE @xml XML = N'
DECLARE@xml XML = N'
<tt>
    <cpost s="a" cena="0.0000" cpost_id="16385" flprt="1" moq="0" valuta_id="2" nmatr_id="14117" norg_id="1791" />
</tt>'Declare@Svarchar(max) =''Select@S=@S+ concat(Item,'="',Value,'" ')
 From (
        Select Top 1000 
               Item   = attr.value('local-name(.)','varchar(100)')
              ,Value= attr.value('.','varchar(max)') 
         From@XML.nodes('/tt/cpost') as A(r)
         Cross Apply A.r.nodes('./@*') AS B(attr)
         OrderBy attr.value('local-name(.)','varchar(100)')
      ) A

Selectconvert(xml,'<tt><cpost '+@S+'/></tt>')

Returns

<tt><cpostcena="0.0000"cpost_id="16385"flprt="1"moq="0"nmatr_id="14117"norg_id="1791"s="a"valuta_id="2" /></tt>

EDIT - Added an In-Line Approach

DECLARE@xml XML = N'
<tt>
    <cpost s="a" cena="0.0000" cpost_id="16385" flprt="1" moq="0" valuta_id="2" nmatr_id="14117" norg_id="1791" />
</tt>'Selectconvert(xml,'<tt><cpost '+Stuff((Select' '+concat(Item,'="',Value,'" ')
 From (
        Select Top 1000 
               Item   = attr.value('local-name(.)','varchar(100)')
              ,Value= attr.value('.','varchar(max)') 
         From@XML.nodes('/tt/cpost') as A(r)
         Cross Apply A.r.nodes('./@*') AS B(attr)
         OrderBy attr.value('local-name(.)','varchar(100)')
      ) A
 For XML Path ('')),1,1,'') +'/></tt>')

Post a Comment for "Order Attribute Names In Xml Using T-sql"