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"