MSSQL2005 对XML 如何排序?字段 Xml 存放
<xml a="1" b="2" ../>
<xml a="4" b="3" ../>
<xml a="我" b="他" ../>如:order a desc,或者 group by a.
排序的方法有那些?
<xml a="1" b="2" ../>
<xml a="4" b="3" ../>
<xml a="我" b="他" ../>如:order a desc,或者 group by a.
排序的方法有那些?
declare @myDoc xml
set @myDoc =
'<Root>
<ProductDescription ProductID="1" ProductName="Road Bike">
<Features>
<Warranty>1 year parts and labor</Warranty>
<Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>
<Features year = "2008">
<Warranty>2</Warranty>
<Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>
</ProductDescription>
</Root>'
--查询全部
SELECT @myDoc.query('/Root/ProductDescription/Features')
--带元素条件
SELECT @myDoc.query('/Root/ProductDescription/Features[Warranty = 2]')
--带属性条件
SELECT @myDoc.query('/Root/ProductDescription/Features[@year = 2008]')
insert #test select '<xml a="1" b="2" />'
insert #test select '<xml a="4" b="3" />'
insert #test select '<xml a="我" b="他" />'
select * from #test
order by x.value('(/*/@a)[1]','varchar(10)') descx
----------------------------------------------------------------------------------------------------
<xml a="我" b="他" />
<xml a="4" b="3" />
<xml a="1" b="2" />(3 行受影响)