问题描述
我需要按字母顺序排列属性名称,我创建了以下代码,但它保留了 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>')