將遞迴資料展成樹狀 XML 節點

應朋友的需求,寫了以下的範例

USE tempdb
GO
CREATE TABLE [dbo].[tbOrganization](
    [Orgid] [int] NOT NULL,
    [OrgName] [nvarchar](30) NULL,
    [ParentOrgid] [int] NULL
) ON [PRIMARY]
GO

INSERT tbOrganization VALUES(1,N’A’,NULL),(2,N’B’,1),(3,N’C’,2),(4,N’D’,1),(5,N’E’,4),(6,N’F’,5),(7,N’G’,2),(8,N’H’,1),(9,N’I’,6)
GO

CREATE PROC [dbo].[spXML] @OrgID INT
AS
WITH Hier(Orgid,OrgPath,OrgName,ParentOrgid,Lvl,XMLNode) AS
(
    SELECT Orgid,Convert(varchar(MAX),Orgid)+Convert(varchar(MAX),OrgName) AS OrgPath,
    OrgName,ParentOrgid,1,
    ‘<asp:TreeNode Text="’ + Convert(varchar(MAX),OrgName) + ‘" Value="’ +  Convert(varchar(MAX),OrgName) + ‘">’ AS XMLNode
    FROM tbOrganization
    WHERE OrgID=@OrgID
    UNION ALL
    SELECT s.OrgID,
    OrgPath + " +Convert(varchar(MAX),s.Orgid)+ Convert(varchar(MAX),s.OrgName) OrgPath,
    s.OrgName,s.ParentOrgID,Lvl+1,
    ‘<asp:TreeNode Text="’ + Convert(varchar(MAX),s.OrgName) + ‘" Value="’ +  Convert(varchar(MAX),s.OrgName) + ‘">’ AS XMLNode
    FROM tbOrganization s JOIN Hier h
    ON s.ParentOrgID=h.OrgID
)
SELECT Lvl,XMLNode INTO #tmp FROM Hier ORDER BY OrgPath

DECLARE @xml NVARCHAR(MAX)=N'<asp:TreeView ID="TreeView1" runat="server" xmlns:asp="test"><Nodes>’
DECLARE cur CURSOR FAST_FORWARD
FOR SELECT * FROM #tmp
DECLARE @Lvl INT=1,@PreLvl INT=1,@Count INT,@Node NVARCHAR(1000)
OPEN cur
FETCH NEXT FROM cur INTO @Lvl,@Node
WHILE @@FETCH_STATUS=0
BEGIN
       SET @xml+=@Node
       SET @PreLvl=@Lvl
       FETCH NEXT FROM cur INTO @Lvl,@Node
       IF @Lvl=@PreLvl
        SET @xml+=N'</asp:TreeNode>’
       IF @Lvl<@PreLvl
       BEGIN
        SET @Count=@PreLvl-@Lvl
        WHILE @Count>=0
        BEGIN
            SET @xml+=N'</asp:TreeNode>’
            SET @Count-=1
        END
       END   
END
CLOSE cur
DEALLOCATE cur
WHILE @PreLvl>1
BEGIN
    SET @xml+=N'</asp:TreeNode>’
    SET @PreLvl-=1
END
SET @xml+='</Nodes></asp:TreeView>’
SELECT @xml

–測試用,可以刪除
SELECT CONVERT(XML,@XML)
GO
–測試
EXEC spXML 1
EXEC spXML 3

執行結果如下

image

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: