Thursday, October 13, 2011

T-SQL Tree Into XML

My previous post contained a link to a Microsoft article detailing how to get well structured XML directly from T-SQL. This post is about getting a tree of data from T-SQL into XML. FOR XML EXPLICIT cannot be used (without major headaches) but a simple recursive function and a SELECT can be used. Here's where I poached my solution from:

  • http://stackoverflow.com/questions/2409228/generate-structured-xml-document-from-hierarchical-table-data-t-sql/2425180#2425180
In short, my code is something like this (generic code for a wider audience):
CREATE function [dbo].[SelectCategoryChild](@categoryid as int)
returns xml
begin
    return (
        select
            categoryid as "@id",
            parentcategoryid as "@parentcategoryid",
            title as "@title",
            dbo.SelectCategoryChild(categoryid)
        from category
        where parentcategoryid = @categoryid
        for xml path('category'), type
    )
end
GO
SELECT
    categoryid AS "@categoryid",
    '' AS "@parentcategoryid",
    title as "@title",
    dbo.SelectCategoryChild(categoryid)
FROM category
WHERE parentcategoryid is null and siteid = 1
FOR XML PATH ('category'), root('categories')
The code above works for a single table created from the statement (references and indices omitted for clarity):
CREATE TABLE [dbo].[Category](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[ParentCategoryID] [int] NULL,
[Title] [varchar](150)
)
CategoryID is the primary key,
ParentCategoryID references the CategoryID and provides theone-to-many tree structure, a NULL value indicates the node is a root node (top-level) in the tree,
Title is simply some data (in this case the name) of the node.

1 comment: