XML for asp.net TreeView from SQL

• Esben Nepper

  Grasshopper

  Points: 15

  More actions

  September 3, 2007 at 5:43 am

  #91230

  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

  CategoryID	SubCategoryID	CategoryName
  1	NULL	Hardware
  2	NULL	Computere
  3	NULL	Software
  4	NULL	Foto & Video
  5	NULL	Tv & HiFi
  6	NULL	PDA & GPS
  7	1	CD/DVD/Floppy
  8	1	Harddiske
  9	1	Grafikkort
  10	1	Højttalere & Headset
  11	1	I/O kort
  12	1	Kabinetter mv.
  13	1	Kablerz
  14	1	Køling
  15	1	Lydkort
  16	1	Mus & Tastatur mv.
  17	1	RAM
  18	1	Skærme
  19	9	Nvidia

  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

• amackay_1

  Ten Centuries

  Points: 1240

  More actions

  September 21, 2007 at 8:30 am

  #735261

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