Order attribute names in xml using t-sql

2019-07-31 22:45发布

问题:

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'<tt>
       <cpost s="a" cena="0.0000" cpost_id="16385" flprt="1" moq="0"
    valuta_id="2" nmatr_id="14117" norg_id="1791" />

       </tt>'

    SELECT
        t.query('.')
    FROM @xml.nodes('*/*') AS t(t)
    ORDER BY t.value('local-name(.)','nvarchar(max)')
    FOR XML PATH(''), TYPE, ROOT('tt')

Where did I make mistake?

回答1:

Not pretty, but this is where my thinking takes me.

dbFiddle

Example

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 @S varchar(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)
         Order By attr.value('local-name(.)','varchar(100)')
      ) A

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

Returns

<tt>
  <cpost cena="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>'

Select convert(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)
         Order By attr.value('local-name(.)','varchar(100)')
      ) A
 For XML Path ('')),1,1,'') +'/></tt>')