Creating XML data from recursive CTE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Creating XML data from recursive CTE

Author

Topic

jakal
Starting Member

5 Posts

Posted - 2008-10-06 : 12:14:30

I am trying to create an XML doc from my category table, I tried the MSN example using recursive user defined functions, it worked, but I hit the 32 function call limit on that.
here is the example that I used but hit the limit; (this works for smaller categories, but bombs on the larger categories)
select CategoryID as "@categoryid", CategoryName as "@CategoryName",
CASE WHEN CategoryID=@CategoryID
THEN dbo.udf_CategoryListXML(CategoryID)
END
FROM tblCategory
WHERE CategoryID=@CategoryID
FOR XML PATH('Category'), TYPE

Below is my main query I would like to output as one XML field that would hold all my category structure, I will then use this field and bind it to treeview control.

My table structure is as follows;

Select CategoryID = 1,CategoryName='Item 1',[Description] = 'blah',DisplayOrder=0,ActiveFlag=1,ParentCategoryID = NULL
union all
Select 2,'Item 2','blah',0,1,NULL
union all
Select 3,'Item 3','blah',0,1,NULL
union all
Select 4,'Item 3','blah',0,1,1
union all
Select 5,'Item 3','blah',0,1,1
union all
Select 6,'Item 3','blah',0,1,2
union all
Select 7,'Item 3','blah',0,1,5
union all
Select 8,'Item 3','blah',0,1,7

here is the query that works wonderfully, but I would like to output CategoryID, and CategoryName to an organized XML data.

WITH CategoryHierarchy (CategoryId, CategoryName, [Description], DisplayOrder, ActiveFlag, ParentCategoryId, NodePath, CatLevel)
AS (
--starting (top-level) point for your hierarchy
SELECT CategoryId, CategoryName, [Description], DisplayOrder, ActiveFlag, ParentCategoryId,
'/' + CAST(DisplayOrder AS VARCHAR(MAX)) + '-' + CAST(CategoryID AS VARCHAR(MAX)),0
FROM tblCategory
WHERE ActiveFlag = 1 and (CategoryId = @ParentCategoryId OR @ParentCategoryId IS NULL)

UNION ALL

--dependent records
SELECT c.CategoryId, c.CategoryName, c.Description, c.DisplayOrder, c.ActiveFlag, c.ParentCategoryId,
ch.NodePath + '/' + CAST(c.DisplayOrder AS VARCHAR(12)) + '-' + CAST(c.CategoryID AS VARCHAR(12)),ch.Catlevel + 1
FROM tblCategory c INNER JOIN CategoryHierarchy ch
ON ch.CategoryId = c.ParentCategoryId
Where c.ActiveFlag = 1
)

--return the results from the CTE

Select CategoryId, CategoryName, [Description], DisplayOrder, ActiveFlag, ParentCategoryId, NodePath, CatLevel,
IndentedCategoryName = LEFT(REPLICATE(' ', Catlevel) + CategoryName, 100)
FROM CategoryHierarchy
ORDER BY Nodepath,DisplayOrder

I would like to output CategoryID, and CategoryName to an orginized XML data to bind to my treeview like this;

<Category categoryid="1" CategoryName="BOOKS & VIDEOS">
<Category categoryid="14" CategoryName="FIREARMS DISASSEMBLY">
<Category categoryid="128" CategoryName="SHOTGUN DISASSEMBLY VIDEOS" />
<Category categoryid="323" CategoryName="MULTI-GUN DISASSEMBLY BOOKS" />
<Category categoryid="513" CategoryName="SHOTGUN DISASSEMBLY BOOKS" />
<Category categoryid="569" CategoryName="RIFLE DISASSEMBLY VIDEOS" />
<Category categoryid="595" CategoryName="HANDGUN DISASSEMBLY VIDEOS" />
<Category categoryid="625" CategoryName="HANDGUN DISASSEMBLY BOOKS" />
<Category categoryid="647" CategoryName="RIFLE DISASSEMBLY BOOKS" />
</Category>
<Category categoryid="21" CategoryName="METAL FINISHING">
<Category categoryid="377" CategoryName="METAL FINISHING VIDEOS" />
<Category categoryid="464" CategoryName="METAL FINISHING BOOKS" />
</Category>
</Category>

Thank You

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 12:27:31

use FOR XML AUTO or FOR XML PATH in select statement using CTE. have a look at syntax in booksonline

jakal
Starting Member

5 Posts

Posted - 2008-10-06 : 15:25:03

I have looked but I could not find a solution to my problem, I do not think a solution exists for unknown level depth.

This is what I basically would like to create, if anyone knows how to do it, I would really really appriate it, but I think it is impossible...

Here is the source table. (this table is recursive and can go very deep)
ID Parent Name
1 NULL car
2 1 engine
3 1 body
4 3 door
5 3 fender
6 4 window
7 2 piston

Here is the required result XML file format.
<Part id="1" name="car">
<Part id="2" name="engine">
<Part id="7" name="piston" />
</Part>
<Part id="3" name="body">
<Part id="4" name="door">
<Part id="6" name="window" />
</Part>
<Part id="5" name="fender" />
</Part>
</Part>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources