生成XML TREE

 

 

 

/*

 

生成XML 树。

 

问题贴地址：http://topic.youkuaiyun.com/u/20100805/16/42b6c9fe-ffd8-48e3-ab84-00b754ada213.html?74723

 

*/

 

 

 

--生成测试表

 

 

 

--CREATE TABLE test(  id int, parentid int, title  varchar(10))

--INSERT test select  1 ,-1, 'A'

--INSERT test select  2 ,1, 'B'

--INSERT test select  3 ,1, 'C'

--INSERT test select  4 ,1, 'D'

--INSERT test select  5 ,-1, 'E'

--INSERT test select  6 ,5, 'F'

--INSERT test select  7 ,-1, 'G'

--INSERT test select  8 ,-1, 'H'

 

 

 

/*

 

需要生成的格式

 

<folders>

  <folder id="1" title="A">

    <folder id="2" title="B" />

    <folder id="3" title="C" />

    <folder id="4" title="D" />

  </folder>

  <folder id="5" title="E">

    <folder id="6" title="F" />

  </folder>

  <folder id="7" title="G" />

  <folder id="8" title="H" />

</folders>

*/

 

 

--方法一:FOR EXPLICIT 格式

;WITH CTE AS(

SELECT * FROM test

UNION ALL

SELECT -1,NULL,NULL

)

, CTE1

AS

(    SELECT

        1 AS tag,

        id,

        parentid,

        title,

        CAST( [id] AS VARBINARY(MAX)) AS Sort

    FROM CTE

    WHERE parentid IS NULL

 

    UNION ALL

 

    SELECT

        tag + 1,

        a.[id],

        a.parentid,

        a.title,

        CAST( SORT + CAST(a.[id] AS BINARY(4)) AS VARBINARY(MAX))

    FROM CTE a

    INNER JOIN CTE1 b ON a.parentid = b.id

),

CTE2 AS (

    SELECT

       *,

        (SELECT Tag FROM CTE1   WHERE ID = a.parentid) AS ParentTag

    FROM CTE1 a 

)

 

SELECT

Tag,

ParentTag as Parent,

CASE WHEN tag = 1 THEN title END AS 'folders!1', --or NULL AS 'folders!1' 生成根节点

CASE WHEN tag = 2 THEN [id] ELSE NULL END AS 'folder!2!id',

    CASE WHEN tag = 2 THEN title ELSE NULL END AS 'folder!2!title',

CASE WHEN tag = 3 THEN [id] ELSE NULL END AS 'folder!3!id',

    CASE WHEN tag = 3 THEN title ELSE NULL END AS 'folder!3!title',

CASE WHEN tag = 4 THEN [id] ELSE NULL END AS 'folder!4!id',

    CASE WHEN tag = 4 THEN title ELSE NULL END AS 'folder!4!title',

CASE WHEN tag = 5 THEN [id] ELSE NULL END AS 'folder!5!id',

    CASE WHEN tag = 5 THEN title ELSE NULL END AS 'folder!5!title' 

 

--可以根据大概的层次添加

--CASE WHEN tag = 6 THEN [id] ELSE NULL END AS 'folder!6!id',

--    CASE WHEN tag = 6 THEN title ELSE NULL END AS 'folder!6!title'

FROM CTE2

ORDER BY sort

FOR XML EXPLICIT

 

 

 

 

 

--方法二:递归函数

--IF OBJECT_ID('ufn_tab_getxml') is not null

-- DROP FUNCTION dbo.ufn_tab_getxml;

--GO

 

--ALTER FUNCTION dbo.ufn_tab_getxml(@pid INT)

--RETURNS XML AS

--BEGIN

-- RETURN (SELECT id [@id],title [@title],dbo.ufn_tab_getxml(id)

-- FROM TEST WHERE parentid=@pid FOR XML PATH('folder'),TYPE);

--END

--go

 

SELECT  id [@id],title [@title], dbo.ufn_tab_getxml(id)

FROM TEST WHERE parentid=-1 FOR XML PATH('folder'),TYPE,ROOT('folders');

 

 

/*

 

结果：

 

<folders>

  <folder id="1" title="A">

    <folder id="2" title="B" />

    <folder id="3" title="C" />

    <folder id="4" title="D" />

  </folder>

  <folder id="5" title="E">

    <folder id="6" title="F" />

  </folder>

  <folder id="7" title="G" />

  <folder id="8" title="H" />

</folders>

*/