|
How To: Creating parent/child relationships in XML (27876 Requests)
Note: A much more effective method for solving this problem can be found here.
The current version of XML for SQL does not support recurisve queries that use the FOR XML clause. This FAQ is
designed to demonstrate how it is possible to create recursive queries using cursors.
For this example we are going to create a tree view type of menu.
First we need to create the menus table. Below is the declaration for this table.
create table menus (
menuID int NOT NULL,
parentID int NULL,
Title varchar(50) NOT NULL,
URL varchar(256) NULL,
sort varchar(60) NULL
)
go
alter table menus add constraint PK_menus primary key clustered
(
menuID
)
This table will be used to create our menu structure. The menuID is the primary key and the parentID is the column
we will use to create the depth in the tree. The sort column will be used to sort our menu.
Next we create a function that will be used to get the value for the sort column. This function will be called from
a trigger which will update the contents of the sort column.
create function getMenuSort
(
@menuID int,
@parentID int
)
returns varchar(50)
as
begin
declare @sort varchar(50),
@nextID int
select @sort = isnull(cast(@parentID as varchar(4)) + '.', '') + cast(@menuID as varchar(4)),
@nextID = @parentID
while (not @nextID is NULL)
begin
select @nextID = parentID,
@sort = isnull(cast(parentID as varchar(4)) + '.', '') + @sort
from menus
where menuID = @nextID
end
return @sort
end
After creating the function we can now create the trigger that calls the function.
alter trigger tr_menus on menus
for insert, update
as
declare @sort varchar(50),
@nextid int,
@menuid int
update menus
set sort = dbo.getMenuSort(inserted.menuID, inserted.parentID)
from inserted
where inserted.menuID = menus.menuID
Here is some sample data that you can insert into your table to get started.
insert into menus values (1000, null, 'Root Menu', null, null)
insert into menus values (1001, 1000, 'Group 1', null, null)
insert into menus values (1002, 1000, 'Group 2', null, null)
insert into menus values (1003, 1001, 'Group 1 - item 1', null, null)
insert into menus values (1004, 1001, 'Group 1 - item 2', null, null)
insert into menus values (1005, 1002, 'Group 2 - item 1', null, null)
insert into menus values (1006, 1002, 'Group 2 - item 2', null, null)
insert into menus values (1007, 1000, 'Group 3', null, null)
insert into menus values (1008, 1007, 'Group 3 - item 1', null, null)
insert into menus values (1009, 1007, 'Group 3 - item 2', null, null)
insert into menus values (1010, 1008, 'Group 3 - item 1 - subitem 1', 'http://www.dell.com', null)
insert into menus values (1011, 1008, 'Group 3 - item 1 - subitem 2', 'http://msdn.microsoft.com', null)
insert into menus values (1012, 1009, 'Group 3 - item 2 - subitem 1', 'http://www.msn.com', null)
insert into menus values (1013, 1009, 'Group 3 - item 2 - subitem 2', 'http://www.microsoft.com', null)
After you have inserted the sample data you will notice that the sort column is filled with values. If your trigger
and function is working correctly you should see values like '1000.1001.1005' in the sort column.
The next task is to create the stored procedure that creates the XML. Since this is a recursive query we cannot
simply use the FOR XML clause but have to create the XML by hand. Here is the stored procedure.
create proc menus_get
as
declare @mID int,
@pID int,
@id varchar(4),
@title varchar(50),
@url varchar(256),
@lastl int,
@level int,
@i int,
@sort varchar(50)
declare curMenus cursor for
select menuID,
cast(menuID as varchar(4)),
parentID,
sort,
title,
url
from menus
order by sort
open curMenus
fetch next from curMenus into @mID, @id, @pID, @sort, @title, @url
while (@@fetch_status = 0) begin
select '<menu id="' + @id +
'" title="' + @title +
isnull('" url="' + @url, '') +
'">'
fetch next from curMenus into @mID, @id, @pID, @sort, @title, @url
if (@@fetch_status = 0) begin
set @level = ((len(@sort) + 1) / 5)
set @i = @lastl - @level
set @lastl = @level
end
else begin
set @i = @level - 1
end
while (@i >= 0)
begin
select '</menu>'
set @i = @i - 1
end
end
deallocate curMenus
return
The stored procedure relies heavily on the sort column, both for sorting and for determining the menu level. Once we
have the stored procedure working we can test it out by creating a sample template.
<?xml version="1.0"?>
<root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:query>
execute menus_get
</sql:query>
</root>
A working sample of this template can be found here.
We now have a parent/child XML document. Next week I will try to add to this FAQ and create an HTML menu from our XML.
Feedback
# Tree view
10/9/2001 1:52 PM
Kieran Kelly
Bryant, you responded to a post I made in the microsoft.public.sqlserver.xml newsgroup in which i was asking about recursion. The reason I want this method is so I can build a tree view menu. I have made and xslt page that translates the xml into html. This xslt page uses recursion so that the tree has no limits. Is it possible for me to send it to you to see what you think.
email: kieran.kelly@blueyonder.co.uk
# Tree view
10/9/2001 5:54 PM
Bryant
I would be very interested in posting an example of using XSL to accomplish this. That would be a much better method.
# xml template
1/28/2002 6:04 AM
Mark
Hi
I am working on a nested xml document using Visual C#.NET
and i found this topic quite useful,
how can i get the result from the xml template also mentioned in the XSL
example
when i try to use it in the QA it gives an error 'cannot load xpweb70.dll '
thanx
# re: How To: Creating parent/child relationships in XML
12/30/2003 7:20 AM
Gordon
This link :
Note: A much more effective method for solving this problem can be found here.
[http://sqlxml.org/faq.asp?55]
Has an incorrect URL. Should be:
http://sqlxml.org/faq.aspx?55
# How to create Tree view in HTML
4/22/2004 11:49 PM
Gobinath
Hi,
Gobinath here, How to create a Tree View using HTML from the XML file.
Could any one help me out to solve this problem.
Waiting for ur soon reply.
regards,
-Gobi
# Multiple Tables (joins) challenging hiearchy in resulting xml
9/1/2004 5:38 PM
Crash
Hello,
I am selection all cloumns from 10 tables that are linked by 'ArtistID' ... meaning every table has a column named 'ArtistID'
Lets assums my table names are table1, table2, table3....
my sql is
select *
from table1, table2, table3...
where
table1.artistID = table2.artistid and
table1.artistID = table3.artistid and
table1.artistID = table4.artistid ....
and table1.artistid ='1'
the resulting document looks llike this
<root>
<table1>
<table1_child1/>
<table1_child2/>
.
.
<table2>
<table2_child1/>
<table2_child2/>
.
.
<table3>
<table3_child1/>
<table3_child2/>
.
.
</table3>
</table2>
</table1>
</root>
What I REALLY WANT IS !!!!
<root>
<table1>
<table1_child1/>
<table1_child2/>
.
.
</table1>
<table2>
<table2_child1/>
<table2_child2/>
.
.
</table2>
<table3>
<table3_child1/>
<table3_child2/>
.
.
</table1>
.
.
</root>
How do I accomplish this? please email me after you answer @ retwaru@fusionus.com
Thanks
# re: How To: Create table for loading xml file when xml file does not have fixed number of tags(fields)
11/14/2005 6:32 AM
Madhu
I have xml file in which I have various number of tags depending on transaction. for example the following xml file has one record with certain number of fields and other has different number of fields.
<TRAN-FILE VALUE = "TRAN-FILE">
<PRIMKEY VALUE = "GP20021120ME000100000100">
<L1T-9914>
<L2T-8646>1</L2T-8646>
<L2T-8902>0</L2T-8902>
<L2T-9158>4</L2T-9158>
<L2T9414>PR1DA:ET0_VI4_ESAA_031201_698.021120_0001_APP</L2T-9414>
<L2T-9670>COM</L2T-9670>
<L2T-9926>A31201</L2T-9926>
<L2T-10182></L2T-10182>
<L2T-10438>EBA</L2T-10438>
<L2T-10694>021120</L2T-10694></L1T-9914>
<L1T-12928>
<L2T-7616>1</L2T-7616>
<L2T-2012>AFBSUB_E_1</L2T-2012>
<L2T-15041>1</L2T-15041>
<L2T-29151>3</L2T-29151></L1T-12928>
</PRIMKEY>
<PRIMKEY VALUE = "GP20021120ME000100000200">
<L1T-9914>
<L2T-8646>1</L2T-8646>
<L2T-8902>0</L2T-8902>
<L2T-9158>4</L2T-9158>
<L2T-9414>PR1DA:ET0_VI4_ESAA_031201_698.021120_0001_APP</L2T-9414>
<L2T-9670>COM</L2T-9670>
<L2T-9926>A31201</L2T-9926>
<L2T-10182></L2T-10182>
<L2T-10438>EBA</L2T-10438>
<L2T-10694>021120</L2T-10694></L1T-9914>
<L1T-15543>
<L2T-18907>F</L2T-18907>
<L2T-19163 VALUE="01">
<L3T-25320>EBA</L3T-25320></L2T-19163>
<L2T-15579>0001</L2T-15579>
<L2T-21723>000001</L2T-21723>
<L2T-23259>A21120</L2T-2
# re: How To: Creating parent/child relationships in XML
10/12/2006 3:29 AM
Hiren
Using this stored procedure can i create n-depth relationship xml of parent child relation from one table
# Thomas
4/20/2007 4:24 AM
Thomas
Nice site!
# tqolzv faexjgibq
5/2/2007 8:08 AM
eofz@mail.com
fcvkm fhpgizr lwso fgxpszmbi epyrhd ybur yuvgfsnxw
# tdcwgmnux mlqnyzwe
7/7/2007 5:57 AM
hwdrom@mail.com
niqzhax hqmvoewd cwsvp iegfyzxso huqaid reqxv kazoscndl
niuqskyc oydwais qjns wsto mfepcs ztfegwyi xfmghil
niuqskyc oydwais qjns wsto mfepcs ztfegwyi xfmghil
qcympgkfn owmyvugzb ansco uymk fgaxybspr sclq kpenfrdt
|