使用 t-sql 在 xml 中订购属性名称

2021-09-10 00:00:00 xml tsql sql-server

我需要按字母顺序排列属性名称，我创建了以下代码，但它保留了 xml:

DECLARE @xml XML = N'<cpost s="a" cena="0.0000" cpost_id="16385" flprt="1" moq="0"valuta_id="2" nmatr_id="14117" norg_id="1791"/></tt>'选择t.query('.')FROM @xml.nodes('*/*') AS t(t)ORDER BY t.value('local-name(.)','nvarchar(max)')FOR XML PATH(''), TYPE, ROOT('tt')

我哪里做错了?

解决方案

不漂亮，但这是我的想法.

dbFiddle

示例

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>'声明@S varchar(max) = ''选择@S = @S + concat(Item,'="',Value,'" ')从 (选择前 1000 名Item = attr.value('local-name(.)','varchar(100)'),Value = attr.value('.','varchar(max)')从@XML.nodes('/tt/cpost') 为 A(r)交叉应用 A.r.nodes('./@*') AS B(attr)按 attr.value('local-name(.)','varchar(100)') 排序) 一种选择 convert(xml,'

退货

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

<块引用>

编辑 - 添加了内联方法

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>'选择 convert(xml,'<tt><cpost '+Stuff((Select '' +concat(Item,'="',Value,'" ')从 (选择前 1000 名Item = attr.value('local-name(.)','varchar(100)'),Value = attr.value('.','varchar(max)')从@XML.nodes('/tt/cpost') 为 A(r)交叉应用 A.r.nodes('./@*') AS B(attr)按 attr.value('local-name(.)','varchar(100)') 排序) 一种对于 XML 路径 ('')),1,1,'') +'/></tt>')

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?

解决方案

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>')

相关文章