SqlServer存储过程生成一个有树结构的xml文档

SqlServer存储过程生成一个有树结构的xml文档，

改文档是一个四层的树结构，我使用first、second、third、fourth四个标签来表示，理由游标进行循环生成，具体看代码吧：

ALTER PROCEDURE pro_test3
AS
BEGIN
declare @myDoc xml;
set @myDoc='<Root></Root>';
DECLARE @rid1 int;
DECLARE @rname1 VARCHAR(20);
DECLARE @rparent1 VARCHAR(4);
DECLARE youb1 CURSOR FOR SELECT * FROM ccl_treetest WHERE parentId = 0;
OPEN youb1
FETCH NEXT FROM youb1 INTO @rid1,@rname1,@rparent1;
WHILE(@@fetch_status = 0) 
BEGIN
/*循环输出xml*/
SET @myDoc.modify('insert <first
 id="{sql:variable("@rid1")}" 
 name="{sql:variable("@rname1")}" 
 parentId="{sql:variable("@rparent1")}" 
 /> into (/Root)[1]')
/*第二层*/
DECLARE @rid2 VARCHAR(4);
DECLARE @rname2 VARCHAR(20);
DECLARE @rparent2 VARCHAR(4);
DECLARE youb2 CURSOR FOR SELECT * FROM ccl_treetest WHERE parentId = @rid1;
OPEN youb2
FETCH NEXT FROM youb2 INTO @rid2,@rname2,@rparent2;
WHILE(@@fetch_status = 0) 
BEGIN
SET @myDoc.modify('insert <second
 id="{sql:variable("@rid2")}" 
 name="{sql:variable("@rname2")}" 
 parentId="{sql:variable("@rparent2")}" 
 /> into (/Root/first[@id=sql:variable("@rid1")])[1]')
/*第三层*/
DECLARE @rid3 VARCHAR(4);
DECLARE @rname3 VARCHAR(20);
DECLARE @rparent3 VARCHAR(4);
DECLARE youb3 CURSOR FOR SELECT * FROM ccl_treetest WHERE parentId = @rid2;
OPEN youb3
FETCH NEXT FROM youb3 INTO @rid3,@rname3,@rparent3;
WHILE(@@fetch_status = 0) 
BEGIN
SET @myDoc.modify('insert <third
 id="{sql:variable("@rid3")}" 
 name="{sql:variable("@rname3")}" 
 parentId="{sql:variable("@rparent3")}" 
 /> into (/Root/first/second[@id=sql:variable("@rid2")])[1]')
/*第四层*/
DECLARE @rid4 VARCHAR(4);
DECLARE @rname4 VARCHAR(20);
DECLARE @rparent4 VARCHAR(4);
DECLARE youb4 CURSOR FOR SELECT * FROM ccl_treetest WHERE parentId = @rid3;
OPEN youb4
FETCH NEXT FROM youb4 INTO @rid4,@rname4,@rparent4;
WHILE(@@fetch_status = 0) 
BEGIN
SET @myDoc.modify('insert <fourth
 id="{sql:variable("@rid4")}" 
 name="{sql:variable("@rname4")}" 
 parentId="{sql:variable("@rparent4")}" 
 /> into (/Root/first/second/third[@id=sql:variable("@rid3")])[1]')
/*end4*/
FETCH NEXT FROM youb4 INTO @rid4,@rname4,@rparent4;
END
CLOSE youb4;
DEALLOCATE youb4;
/*end3*/
FETCH NEXT FROM youb3 INTO @rid3,@rname3,@rparent3;
END
CLOSE youb3;
DEALLOCATE youb3;
/*end2*/
FETCH NEXT FROM youb2 INTO @rid2,@rname2,@rparent2;
END
CLOSE youb2;
DEALLOCATE youb2;
/*end1*/
FETCH NEXT FROM youb1 INTO @rid1,@rname1,@rparent1;
END
CLOSE youb1;
DEALLOCATE youb1;
select @myDoc
END

输出效果：

<Root>
  <first id="1" name="parent1" parentId="0">
    <second id="4" name="son1" parentId="1">
      <third id="10" name="lson111" parentId="4">
        <fourth id="14" name="lson1111111" parentId="10"/>
      </third>
    </second>
    <second id="7" name="son22" parentId="1"/>
  </first>
  <first id="2" name="parent2" parentId="0">
    <second id="5" name="sont2" parentId="2">
      <third id="11" name="lson222" parentId="5">
        <fourth id="13" name="llson 22222" parentId="11"/>
      </third>
      <third id="12" name="lson2222" parentId="5"/>
    </second>
    <second id="8" name="son11" parentId="2"/>
  </first>
  <first id="3" name="parent3" parentId="0">
    <second id="6" name="son3" parentId="3"/>
    <second id="9" name="son33" parentId="3"/>
  </first>
  <first id="15" name="parent0" parentId="0"/>
</Root>