1

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?

Sasha Fes
  • 27
  • 6
  • I dont think it is possible to preserve sorting based on attribute. Try looking at this https://msdn.microsoft.com/en-us/library/ms187107(v=sql.90).aspx – Kannan Kandasamy May 21 '17 at 00:00
  • Look at [this link](https://stackoverflow.com/q/7230739/5089204). The sort order of elements is preserved but not the order of attributes. There is something called "canonical XML", but the question is: Why do you need this? There is AFAIC no *pretty* or *elegant* way to achieve this... – Shnugo May 22 '17 at 19:13

1 Answers1

3

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>')
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
  • not exactly, result has to be the xml file but like this: " '" – Sasha Fes May 21 '17 at 00:00
  • @SashaFes Happy it helped. I'm sure there must be a smarter way, but it escapes me at this point. – John Cappelletti May 21 '17 at 00:30
  • @SashaFes I added an in-line approach if needed. – John Cappelletti May 21 '17 at 01:24
  • Hi John, it is possible to order the attributes alphabetically, but this will not be preserved, if you don't store the result as string. Whatever you do here, there is no guarantee to get the attributes in the same order the next time. In most cases this is done to get XML documents compareable on string level or to create a hash to check for manipulations. Besides the attribute's order there's also no guarantee about insignificant whitespaces. The linked document below the question is very readworthy... – Shnugo May 23 '17 at 07:54
  • @Shnugo Thanks for the info. Can you share the link again? It may have been truncated. – John Cappelletti May 23 '17 at 08:10
  • @JohnCappelletti, just look at the first comment below the question (Kannan Kandasamy). There's the link... – Shnugo May 23 '17 at 08:21
  • @Shnugo I just assumed this was a cosmetic request, after all, I saw no value actual in the attribute order. Then I saw a very similar question from last night (which you commented on). Now I'm guessing a homework. – John Cappelletti May 23 '17 at 13:38