Select t.Id,t.Name,t.Url From dbo.SGame_List t Where t.Type = 1
for xml path('game'),root('list'),type查询数据中如果某一行的某个字段的值为null的话会不生成这个节点.如何自动生成这个节点
for xml path('game'),root('list'),type查询数据中如果某一行的某个字段的值为null的话会不生成这个节点.如何自动生成这个节点
SELECT '01','1','A',92.10 UNION ALL
SELECT '01','2',null,10.00 SELECT TID,CLASSID,KINDID,PRICE FROM @TB for xml path('game'),root('list'),TYPE, ELEMENTS XSINIL/*
<list xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<game>
<TID>01</TID>
<CLASSID>1</CLASSID>
<KINDID>A</KINDID>
<PRICE>92.10</PRICE>
</game>
<game>
<TID>01</TID>
<CLASSID>2</CLASSID>
<KINDID xsi:nil="true" />
<PRICE>10.00</PRICE>
</game>
</list>
*/
SELECT '01','1','A',92.10 UNION ALL
SELECT '01','2',null,10.00 DECLARE @x XML
SET @x=(SELECT TID,CLASSID,KINDID,PRICE FROM @TB for xml path('game'),root('list'),TYPE, ELEMENTS XSINIL)select a.b.value('(./TID)[1]','nvarchar(50)') as a,
a.b.value('(./TID)[1]','int') as b,
a.b.value('(./KINDID)[1]','nvarchar(50)') as c,
a.b.value('(./PRICE)[1]','money') as d
from @x.nodes('list/game') as a(b)/*
a b c d
01 1 A 92.10
01 1 10.00
*/