Generating nested XML in SQL Server

Question

I am trying to use the FOR XML syntax to take a one table database and generate nested XML using an Azure SQL database.

My table looks like this

TASK Table

NAME    | ID | PARENT
School  |  0 | NULL
Math    |  1 |    0
CSC     |  2 |    0
Eng     |  3 |    0
HW      |  4 |    1
Quiz    |  5 |    1
HW      |  6 |    2
Quiz    |  7 |    2
HW      |  8 |    3
Quiz    |  9 |    3
Hw1     | 10 |    4
Hw2     | 11 |    4

This is what I want the XML to come out like

<Task name=School>  
   <Task name=Math>  
      <Task name=HW>  
         <Task name=Hw1>  
         <Task name=Hw2>  
      <Task name=Quiz>  
   <Task name=CSC>  
      <Task name=HW>  
      <Task name=Quiz>  
   <Task name=Eng>  
      <Task name=HW>  
      <Task name=Quiz>  
<\Task>

-

SOLUTION FROM @Mikael Eriksson

Go to www.SQLFiddle.com, select SQL Server 2012 in the top left corner.

Enter the following into the Schema panel on the left then 'Build Schema'

create table dbo.Task
(
  Name varchar(10),
  ID int,
  Parent int
);

insert into Task values
('School'  ,  0 , null),
('Math'    ,  1 ,    0),
('CSC'     ,  2 ,    0),
('Eng'     ,  3 ,    0),
('HW'      ,  4 ,    1),
('Quiz'    ,  5 ,    1),
('HW'      ,  6 ,    2),
('Quiz'    ,  7 ,    2),
('HW'      ,  8 ,    3),
('Quiz'    ,  9 ,    3),
('Hw1'     , 10 ,    4),
('Hw2'     , 11 ,    4);

go

create function dbo.GetTaskXML(@ParentID int) returns xml
as
begin
  return (
         select Name as [@name],
                (select dbo.GetTaskXML(ID))
         from dbo.Task
         where Parent = @ParentID or 
               (Parent is null and @ParentID is null)
         for xml path('Task'), type
         )
end

Remember to click on 'Build Schema', Once the schema is built, you can enter your query in the window on the right.

select dbo.GetTaskXML(null)

Now run query and the bottom window will return the XML results, thanks again Mikael

I'm not sure what you're doing, but the answer to the question in the title is WHERE column NOT REGEX s1 AND column NOT REGEX s2. — Barmar
– Barmar, Commented Apr 11, 2014 at 0:01
mikehillyer.com/articles/managing-hierarchical-data-in-mysql — Barmar
– Barmar, Commented Apr 11, 2014 at 0:04

Mikael Eriksson · Accepted Answer · 2014-04-11 05:40:38Z

2

If your hierarchy is no deeper than 32 levels you can use a recursive scalar valued function that builds the XML.

create function dbo.GetTaskXML(@ParentID int) returns xml
as
begin
  return (
         select Name as [@name],
                (select dbo.GetTaskXML(ID))
         from dbo.Task
         where Parent = @ParentID or 
               (Parent is null and @ParentID is null)
         for xml path('Task'), type
         )
end

SQL Fiddle

answered Apr 11, 2014 at 5:40

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Devin Gleason Lambert Over a year ago

Mikael, is it possible instead to return the xml using the names instead of Task for example <Task name=School> would be <School>?

Mikael Eriksson Over a year ago

@DevinGleasonLambert I don't think so. I let you know if I figure something out. Probably you need to build the XML using dynamic queries and then do that recursively will be pretty complicated and can not be done in a function because functions does not support dynamic SQL.

Collectives™ on Stack Overflow

Generating nested XML in SQL Server

-

SOLUTION FROM @Mikael Eriksson

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

-

SOLUTION FROM @Mikael Eriksson

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related