求助一个SQL语句

我用的是SQL Server 2008。有一个表结构如下（嵌套层次不确定）:
id name pid
1 A 0
2 A1 1
3 A2 1
4 A11 2
5 A21 3
6 A22 3
7 A111 4
8 A112 4
……

要生产如下XML格式的文件：
<root>
<row id='1' name='A'>
<row id='2' name='A1'>
<row id='4' name='A11'>
<row id='7' name='A111'/>
<row id='8' name='A112'/>
</row>
</row>
<row id='3' name='A2'>
<row id='5' name='A21'/>
<row id='6' name='A22'/>
</row>
</row>
</root>

请问这个SQL语句该如何写？

该回复于2011-11-29 10:52:04被管理员删除

csing (小刀) 等　级：

#2楼 得分：0回复于：2011-11-29 10:40:05 <root> ####<row id='1' name='A'> ########<row id='2' name='A1'> ############<row id='4' name='A11'> ################<row id='7' name='A111'/> ################<row id='8' name='A112'/> ############</row> ########</row> ####<row id='3' name='A2'> ############<row id='5' name='A21'/> ############<row id='6' name='A22'/> ########</row> ####</row> </root> 请忽略“#”

http://archive.cnblogs.com/a/1971128/

樹形遞歸生成?

按第1層生成，第2個結果集沒有 A

你用 FOR XML RAW就可生成這樣的xml格式

BOM按节点排序？

create function f_getxml(@pid int)
returns xml
as
begin
declare @xx xml;
select @xx = '<root/>';
declare @x xml;
with cte(id,name,pid)
as
(
select id,name,pid from t_test 
where pid = @pid
)
select @x = (select * from cte for xml path('row'),root('root'));
DECLARE @cnt INT,@totCnt INT,@strID varchar(100),@strName varchar(100);
SELECT @cnt = 1,@totCnt = @x.value('count(//row)','INT')
WHILE @cnt <= @totCnt BEGIN
select @strID = @x.value('(//row[sql:variable("@cnt")]/id)[1]','varchar(100)')
select @strName = @x.value('(//row[sql:variable("@cnt")]/name)[1]','varchar(100)')
SET @xx.modify('insert element row{} as last into (/root)[1]')
SET @xx.modify('insert attribute name{sql:variable("@strName")} as last into (//row[sql:variable("@cnt")])[1]')
SET @xx.modify('insert attribute id{sql:variable("@strID")} as last into (//row[sql:variable("@cnt")])[1]')
SELECT @cnt = @cnt + 1
END
return @xx
end

我写了这么一个语句，传递一个PID进去后，只能显示一级的。
我不知道这个递归应该写在什么位置？

你這個的格式，估計只能用拼的方式 

給你舉個列子
SQL code

use Tempdb
go
--> --> 
 
if not object_id(N'T') is null
    drop table T
Go
Create table T([id] int,[name] nvarchar(4),[pid] int)
Insert T
select 1,N'A',0 union all
select 2,N'A1',1 union all
select 3,N'A2',1 union all
select 4,N'A11',2 union all
select 5,N'A21',3 union all
select 6,N'A22',3 union all
select 7,N'A111',4 union all
select 8,N'A112',4
Go
DECLARE @x1 XML,@x2 NVARCHAR(500)

;WITH c
AS
(
SELECT *,ord=CAST(RIGHT(1000+ROW_NUMBER()over(ORDER BY ID),3) AS NVARCHAR(200)) from T WHERE  [pid]=1
UNION ALL 
SELECT a.*,CAST(c.ord+RIGHT(1000+ROW_NUMBER()over(ORDER BY a.ID),3)AS NVARCHAR(200))  FROM T AS a INNER JOIN c ON a.pid=c.ID
)
SELECT @x1='<Root>'+(SELECT id,Name
FROM 
(SELECT ID,NAME,ord='000' FROM t WHERE pid=0
UNION ALL 
SELECT ID,NAME,ord FROM c  WHERE ord LIKE '001%'
)t
ORDER BY ord
FOR XML RAW,ROOT('Root'))
+(SELECT ID,NAME,ord FROM c  WHERE ord LIKE '001%' FOR XML RAW,ROOT('row'))+'</Root>'

SELECT @x1
/*
<Root>
  <Root>
    <row id="1" Name="A" />
    <row id="2" Name="A1" />
    <row id="4" Name="A11" />
    <row id="7" Name="A111" />
    <row id="8" Name="A112" />
  </Root>
  <row>
    <row ID="2" NAME="A1" ord="001" />
    <row ID="4" NAME="A11" ord="001001" />
    <row ID="7" NAME="A111" ord="001001001" />
    <row ID="8" NAME="A112" ord="001001002" />
  </row>
</Root>
*/

改改，條件寫重了

SQL code

DECLARE @x1 XML,@x2 NVARCHAR(500)

;WITH c
AS
(
SELECT *,ord=CAST(RIGHT(1000+ROW_NUMBER()over(ORDER BY ID),3) AS NVARCHAR(200)) from T WHERE  [pid]=1
UNION ALL 
SELECT a.*,CAST(c.ord+RIGHT(1000+ROW_NUMBER()over(ORDER BY a.ID),3)AS NVARCHAR(200))  FROM T AS a INNER JOIN c ON a.pid=c.ID
)
SELECT @x1='<Root>'+(SELECT id,Name
FROM 
(SELECT ID,NAME,ord='000' FROM t WHERE pid=0
UNION ALL 
SELECT ID,NAME,ord FROM c  WHERE ord LIKE '001%'
)t
ORDER BY ord
FOR XML RAW,ROOT('Root'))
+(SELECT ID,NAME,ord FROM c  WHERE ord LIKE '002%' FOR XML RAW,ROOT('row'))+'</Root>'

SELECT @x1
/*
<Root>
  <Root>
    <row id="1" Name="A" />
    <row id="2" Name="A1" />
    <row id="4" Name="A11" />
    <row id="7" Name="A111" />
    <row id="8" Name="A112" />
  </Root>
  <row>
    <row ID="3" NAME="A2" ord="002" />
    <row ID="5" NAME="A21" ord="002001" />
    <row ID="6" NAME="A22" ord="002002" />
  </row>
</Root>
*/

先遞歸再用FOR XML RAW,ROOT('Root'),寫函數也是一樣的用法
SQL code

Go
DECLARE @x1 XML,@x2 NVARCHAR(500)

;WITH c
AS
(
SELECT *,ord=CAST(RIGHT(1000+ROW_NUMBER()over(ORDER BY ID),3) AS NVARCHAR(200)) from T WHERE  [pid]=1
UNION ALL 
SELECT a.*,CAST(c.ord+RIGHT(1000+ROW_NUMBER()over(ORDER BY a.ID),3)AS NVARCHAR(200))  FROM T AS a INNER JOIN c ON a.pid=c.ID
)
SELECT @x1='<Root>'+(SELECT id,Name
FROM 
(SELECT ID,NAME,ord='000' FROM t WHERE pid=0
UNION ALL 
SELECT ID,NAME,ord FROM c  WHERE ord LIKE '001%'
)t
ORDER BY ord
FOR XML RAW,ROOT('row'))
+(SELECT ID,NAME,ord FROM c  WHERE ord LIKE '002%' FOR XML RAW,ROOT('row'))+'</Root>'

SELECT @x1
/*
<Root>
  <row>
    <row id="1" Name="A" />
    <row id="2" Name="A1" />
    <row id="4" Name="A11" />
    <row id="7" Name="A111" />
    <row id="8" Name="A112" />
  </row>
  <row>
    <row ID="3" NAME="A2" ord="002" />
    <row ID="5" NAME="A21" ord="002001" />
    <row ID="6" NAME="A22" ord="002002" />
  </row>
</Root>
*/