我想实现如下格式的xml结果,请问如何实现?<root startDate="2007-05-10" endDate="2007-05-10">
<table name=" dynmmon_sales ">
<item id="1" name="a" />
<item id="2" name="b" />
<item id="3" name="c" />
</table>
</root>
<table name=" dynmmon_sales ">
<item id="1" name="a" />
<item id="2" name="b" />
<item id="3" name="c" />
</table>
</root>
DECLARE @t TABLE(id INT,name VARCHAR)
INSERT @t SELECT 1,'a'
UNION ALL SELECT 2,'b'
UNION ALL SELECT 3,'c' SELECT 1 AS TAG,
NULL AS Parent,
1 AS [root!1!cid],
NULL AS [item!2!id],
NULL AS [item!2!name]
UNION ALL
SELECT 2,1,1,id,name
FROM @t
ORDER BY [root!1!cid],[item!2!id]
FOR XML EXPLICIT
/*
<root cid="1">
<item id="1" name="a" />
<item id="2" name="b" />
<item id="3" name="c" />
</root>
*/
显示效果如
/*
<root cid="1" bid="2">
<item id="1" name="a" />
<item id="2" name="b" />
<item id="3" name="c" />
</root>
*/
INSERT @t SELECT 1,'a'
UNION ALL SELECT 2,'b'
UNION ALL SELECT 3,'c' SELECT 1 AS TAG,
NULL AS Parent,
1 AS [root!1!cid],
'aa' AS [root!1!xxx],
NULL AS [item!2!id],
NULL AS [item!2!name]
UNION ALL
SELECT 2,1,1,'',id,name
FROM @t
ORDER BY [root!1!cid],[item!2!id]
FOR XML EXPLICIT
/*
<root cid="1" xxx="aa">
<item id="1" name="a" />
<item id="2" name="b" />
<item id="3" name="c" />
</root>
*/