Wednesday, October 19, 2011
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.
T-SQL FOR XML EXPLICIT
An excellent example of producing well formatted and structured XML direct from SQL where a non-tree structure is in use. I have used this to get a complex series of document information out in a specific format.
Subscribe to:
Posts (Atom)