数据以xml格式返回

[wsxcy66668888]

从数据库中查询一张表的数据
select 部门,姓名 from tb
如何才能生成下面的xml格式

<folder state="unchecked" label="全部">
　　 <folder state="unchecked" isBranch="true" label="技术科" >
       <folder state="unchecked" isBranch="false" label="张三" />
       <folder state="unchecked" isBranch="false" label="李四" />
       <folder state="unchecked" isBranch="false" label="王五" />
       <folder state="unchecked" isBranch="false" label="赵六" />
        ......
　　 </folder>
　　 <folder  state="unchecked" isBranch="true" label="生产科" >
       <folder state="unchecked" isBranch="false" label="牛七" />
       <folder state="unchecked" isBranch="false" label="马八" />
        ......
　　 </folder>
    ......
</folder>

[bancxc]

学习 这个》。

[--小F--]

有请P梁

[ACMAIN_CHM]

select * from t_record FOR XML AUTO

[fcuandy]

要加什么属性值，自己加虚列就可以了。

DECLARE @t TABLE(dp VARCHAR(100),nm VARCHAR(100))
INSERT @t SELECT 'aa','a1'
UNION ALL SELECT 'aa','a2'
UNION ALL SELECT 'cc','d3'
UNION ALL SELECT 'cc','vx'



SELECT *,
	(
		SELECT dp label,isdp='true',
			(
				SELECT nm label,isdp='false' 
					FROM @t 
					WHERE dp=a.dp 
					FOR XML RAW('flodr'),TYPE
			)
		FROM (SELECT DISTINCT dp FROM @t) a
		FOR XML RAW('flodr') ,TYPE
	) 
	FROM (SELECT '全部' label,state='unchecked') a
	FOR XML RAW('flodr')


/*
<flodr label="全部" state="unchecked">
  <flodr label="aa" isdp="true">
    <flodr label="a1" isdp="false" />
    <flodr label="a2" isdp="false" />
  </flodr>
  <flodr label="cc" isdp="true">
    <flodr label="d3" isdp="false" />
    <flodr label="vx" isdp="false" />
  </flodr>
</flodr>
*/

[maxiaopeng1127]

ding

[shabble]

等到答案

[无锡阔微信息科技]

if object_id('tb') is not null
drop table tb
go
create table tb(id int,name varchar(20))
insert into tb(id,name)
select 1,'zy' union all
select 2,'zy' union all
select 3,'zy' union all
select 4,'zy' union all
select 5,'zy' union all
select 6,'zy' union all
select 7,'zy' union all
select 8,'zy' union all
select 9,'zy'

select * from tb FOR XML AUTO
<tb id="1" name="zy"/><tb id="2" name="zy"/><tb id="3" name="zy"/><tb id="4" name="zy"/><tb id="5" name="zy"/><tb id="6" name="zy"/><tb id="7" name="zy"/><tb id="8" name="zy"/><tb id="9" name="zy"/>

[无锡阔微信息科技]

[Quote=引用 4 楼 fcuandy 的回复:]
要加什么属性值，自己加虚列就可以了。

SQL codeDECLARE@tTABLE(dpVARCHAR(100),nmVARCHAR(100))INSERT@tSELECT'aa','a1'UNIONALLSELECT'aa','a2'UNIONALLSELECT'cc','d3'UNIONALLSELECT'cc','vx'SELECT*,
(SELECT dp label,isdp='true',
(SELECT nm label,isdp='false'FROM@tWHERE dp=a.dpFOR XMLRAW('flodr'),TYPE
)FROM (SELECTDISTINCT dpFROM@t) aFOR XMLRAW('flodr') ,TYPE
)FROM (SELECT'全部' label,state='unchecked') aFOR XMLRAW('flodr')/*
<flodr label="全部" state="unchecked">
<flodr label="aa" isdp="true">
<flodr label="a1" isdp="false" />
<flodr label="a2" isdp="false" />
</flodr>
<flodr label="cc" isdp="true">
<flodr label="d3" isdp="false" />
<flodr label="vx" isdp="false" />
</flodr>
</flodr>*/
[/Quote]

我怎么运行出错啊

[Leshami]

还不太熟，学习了。

[fcuandy]

[Quote=引用 8 楼 zywcy100 的回复:]
引用 4 楼 fcuandy 的回复:
要加什么属性值，自己加虚列就可以了。

SQL codeDECLARE@tTABLE(dpVARCHAR(100),nmVARCHAR(100))INSERT@tSELECT'aa','a1'UNIONALLSELECT'aa','a2'UNIONALLSELECT'cc','d3'UNIONALLSELECT'cc','vx'SELECT*,
    (SELECT dp label,isdp='true',
            (SELECT nm label,isdp='false'FROM@tWHERE dp=a.dpFOR XMLRAW('flodr'),TYPE
            )FROM (SELECTDISTINCT dpFROM@t) aFOR XMLRAW('flodr') ,TYPE
    )FROM (SELECT'全部' label,state='unchecked') aFOR XMLRAW('flodr')/*
<flodr label="全部" state="unchecked">
  <flodr label="aa" isdp="true">
    <flodr label="a1" isdp="false" />
    <flodr label="a2" isdp="false" />
  </flodr>
  <flodr label="cc" isdp="true">
    <flodr label="d3" isdp="false" />
    <flodr label="vx" isdp="false" />
  </flodr>
</flodr>*/


我怎么运行出错啊
[/Quote]

sqlserver2k是不支持的，sqlserver2k要用ELEMENTS模式，写法比较复杂。

[victorcai2006]

抬头仰望……先来学习一下