Lets Have a fun with Technology.

BTemplates.com

Follow by Email

Wednesday, April 8, 2015

Generate Hierarchy XML using SQL Server.


Hello Friend's,

Today I have worked on generating Hierarchy XML output using SQL Server. Later I have bind this XML with TreeView.

Below is my table.


for your reference below is table creation query.

CREATE TABLE [dbo].[Ketan_Example](
[Id] [int] NOT NULL,
[Item] [nvarchar](50) NULL,
[ParentID] [int] NULL,
 CONSTRAINT [PK_Ketan_Example] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]



Now insert dummy data in table.





Create function

CREATE FUNCTION [dbo].[ketan_HierarchyNode] (@ParentID INT) RETURNS XML
BEGIN RETURN
    (SELECT Id as Value, 
            ParentID, 
            Item,
            dbo.ketan_HierarchyNode(Id)
        FROM Ketan_Example as Node 
        WHERE ParentID = @ParentID
        FOR XML Auto)
END;

GO



Create Store Procedure.

CREATE  PROCEDURE [dbo].[GetXMLforLabeling]
AS

SELECT 
Id,
        ParentID,
        Item,
        dbo.ketan_HierarchyNode(Id)
FROM    Ketan_Example
WHERE   ParentID IS NULL 
FOR     XML AUTO




All Done !!!


Now execute the Store Procedure.





Let me know if you have any question.


0 comments:

Post a Comment