XML 数据生成
Java类DocXML3WService 有数据库连接串这样写 "select dbo.SelectAlbumElementsToXml() as XMLData"
SelectAlbumElementsToXml是一个标量值函数,生成MS SQL XML类型数据。
CREATE FUNCTION [dbo].[SelectAlbumElementsToXml] ()
RETURNS XML
AS
BEGIN
DECLARE @Result XML
SET @Result = (select Tag,Parent,
[node!1!label],[node!2!label],[node!3!label]
from (select 1 as Tag,
null as Parent,
ElementName as [node!1!label],
null as [node!2!label],
null as [node!3!label],
uplevel
from dbo.AlbumElements
where Leveltag=1
union all
select 2 as Tag, 1 as Parent,
null,ElementName,null,uplevel
from dbo.AlbumElements
where Leveltag=2
union all
select 3 as Tag, 2 as Parent,
null,null,ElementName,uplevel
from dbo.AlbumElements
where Leveltag=3
) A
order by uplevel
for xml EXPLICIT,type,root('root')
)
RETURN @Result
END
说明:
1. “For xml” 选项,表示Select出来的数据要XML格式;
2. “EXPLICIT”选项,需要生成树形结构的XML,这个选项比较难明白,这里不介绍,可以看看帮助;
3. 数据库数据:
4. 生成数据如:
<root>
<node label="音乐">
<node label="流行">
<node label="林忆莲" />
<node label="李克勤" />
<node label="刘德华" />
<node label="张学友" />
</node>
<node label="古典">
<node label="李斯特" />
<node label="莫扎特" />
<node label="贝多芬" />
<node label="巴赫" />
</node>
<node label="民乐">
<node label="宋祖英" />
</node>
</node>
</root>