XML for asp.net TreeView from SQL

Question

SSC Rookie

Points: 25

September 3, 2007 at 5:43 am

I'm trying to generate XML from SQL to use for my website navigation.

Here is the sql that i use for geting the name and the id of the website and sorting it.

My table strutcur looks like this

WITH tree(id, data, [LEVEL], pathstr) AS (SELECT CategoryID, CategoryName, 0, CAST(CategoryName AS VARCHAR(MAX))

FROM ProductCategorys

WHERE SubCategoryID IS NULL

UNION ALL

SELECT CategoryID,

CategoryName,

[LEVEL] + 1,

CAST(t .pathstr AS VARCHAR(MAX)) + ',' + CAST(CategoryName AS VARCHAR(MAX))

FROM ProductCategorys V INNER JOIN

tree t ON t.id = V.SubCategoryID)

SELECT id, SPACE([LEVEL] * 2) + data as data, pathstr

FROM tree

ORDER BY pathstr ASC, id ASC

This looks real nice but when i add FOR XML to the query things get messed up.

What i like is to know how to format my XML so i can use it for ASP.net TreeView Controler

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

amackay_1 Ten Centuries Points: 1240 More actions · Answer 1

Hi Esben, Did you get an answer on this? We are trying to do the same thing.