XML Workshop XIV - Generating an XML Tree
-
Comments posted to this topic are about the item XML Workshop XIV - Generating an XML Tree
.
-
Thanks for this article. Your approach is very interesting especially with the use of CTE constructs. Finding good xml shaping articles is difficult at best and your approach shows genuine ingenuity.
I took a crack at shaping XML on the database side during a project a long time ago (SQL 2000) and found it to be difficult to get my head wrapped around the syntax. With CTE, your approach makes it a little easier.
After abandoning the XML shaping approach at the database layer in my project, I found it much easier to do it at the business layer using XML objects and appending node fragments received from the database. This approach, although easier, was probably not the most efficient.
Thanks again.
-Mike
-
One of the major deficiencies of using a CTE for hierarchical queries is the inability to order the nodes. Contrast with Oracle's CONNECT BY ... ORDER SIBLINGS BY ... functionality.
I noted this in earlier postings:
http://www.sqlservercentral.com/Forums/FindPost443059.aspx
http://www.sqlservercentral.com/Forums/FindPost366402.aspx
Having worked extensively with hierarchical structures in relational databases for over 20 years, I seriously looked at using the new CTE capability in SQL Server 2005. However, I had to reject it for the reasons noted in the above referenced postings.
Now you can build a "sort" field as is done in the [overly simplified] example, but it gets rather difficult if you need to order the nodes by a more commonly used string field. E.g., in the example, the nodes are ordered by their internal surrogate key (ID). From a presentation layer standpoint, the nodes would appear in "random" order. Another sort operation (now we've sorted the data twice) would be required to present the data in a more normal manner -- sorted by the part "name". Similar to a file system directory (folder) tree presentation. Note that a folder tree is not ordered by the internal node identifier, but by the "name".
I would like to see this simple example reworked to be more "real world".
"rant": I'm finding that most examples (in all technologies) are always too simple. I'd rather have a bit more complicated (i.e., don't always use "int" -- use varchar) example.
[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font] -
I guess I agree a bit with the comment about simple examples, although, I do also think the idea of forum examples is to explain the basics to give the reader a foundation which they would then run with based upon their very specific need. It's not about solving your particular problem for you but giving you and everyone else the tools to solve a multitude of problems.
That said, what I find fustrating is when the example solves a problem that is so narrow that the real world use is useable in only a very specific situation. (as in the example for this article) Why would you not supply the recurrsive example so a wider use can be provided? If I have 1 or 20 fixed levels in my XML it can be handled with recurrsion just as an undetermined number of levels could, where, the undetermined levels cannot use your example. And what are the odds that more people are using fixed levels, it is my experience, more times than not, the levels end up being variable.
My 2 cent, and not to loook a gift horse in the mouth. I guess what I'm saying here is: "Anyone have a good recurrisive example here you can spoon feed me?":)
-
The method shown in this article is great if you like having everything in your XML set up as an attribute. If you prefer actual XML elements with values between the element name brackets (e.g. ) try using the "FOR XML PATH" clause in SQL 2005. You can build an entire XML hierarchical structure using nested queries. This is especially useful if you are attempting to produce an XML result that conforms to an XSD schema. There is a good introductory discussion of FOR XML PATH here: http://msdn2.microsoft.com/en-us/library/ms345137.aspx#forxml2k5_topic6.
-
Totally agree with Doug. FOR XML PATH *is* the way to solve these problems in SQL Server 2005. Now, that said I will vote *against* doing such thing on the database server If we want to "scale" xml processing should in my opinion be done at client side. Let the relational engine do what it does best:
--> Sorting, searching and aggregations!
* Noel -
I'm sorry, by my bad English.
I reliase such functional in next way:
First and principal function:
USE [DK021-2007]
GO
/****** Object: UserDefinedFunction [dbo].[xmlIterator] Script Date: 02/29/2008 10:18:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:?. ??????
-- Create date: 26.02.2008
-- Description:???????? ?????? ? XML
-- =============================================
ALTER FUNCTION [dbo].[xmlIterator] (@id int, @xml xml)
RETURNS xml
AS
BEGIN
declare @childint
declare @xxml
if @xml is null
set @xml = (select * from itemsCPV where id = @idfor xml auto)
set @child = 0
while exists (select * from itemsCPV where idP = @id and id > @child)
begin
select top 1 @child = id from itemsCPV where idP = @id and id > @child order by id
set @x = (select * from itemsCPV where id = @child for xml auto)
if exists (select * from itemsCPV where idP = @child)
set @x = dbo.xmlIterator(@child, @x)
set @xml = dbo.insertNodeIntoElement(@xml, @x)
end
return @xml
END
and helper function:
USE [DK021-2007]
GO
/****** Object: UserDefinedFunction [dbo].[insertNodeIntoElement] Script Date: 02/29/2008 10:20:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:?. ??????
-- Create date: 26.02.2008
-- Description:??????? ???????? ? XML
-- =============================================
ALTER FUNCTION [dbo].[insertNodeIntoElement] (@root xml, @element xml)
RETURNS xml
AS
BEGIN
if @root is null
return null
if
@root.value('count(/*)','int') = 0 or @root is null
or @element.value('count(/*)','int') = 0
or @root.value('count(/*)','int') > 1
or @element.value('count(/*)','int') > 1
return @root
set @root = cast(cast(@root as nvarchar(max)) + cast(@element as nvarchar(max)) as xml)
set @root.modify('insert /*[2] into /*[1]')
set @root.modify('delete /*[2]')
return @root
END
Here: itemsCPV - is my hierarchical table , id and idP - identifiers (PARTS, ID and Parent by Jacob Sebastian).
For take subtree of XML document for instance:
select dbo.xmlIterator(341, null)
and full tree:
select dbo.xmlIterator(1, null)
This way is:
1. slow;
2. limited in 32 levels (by MS SQL)
In detail you can see in Russian: http://sql.ru/forum/actualthread.aspx?tid=531257
For questions my e-mail: brovko@ukr.net
Aleksey
-
Personally I avoid FOR XML EXPLICIT in SQL Server 2005 and use FOR XML PATH instead
CREATE FUNCTION dbo.GetSubTree(@id int)RETURNS XML
BEGIN RETURN
(SELECT id AS "@id",
name AS "@name",
dbo.GetSubTree(id)
FROM Parts
WHERE parent=@id OR (parent IS NULL AND @id IS NULL)
ORDER BY id
FOR XML PATH('Part'),TYPE)
END
GO
SELECT dbo.GetSubTree(NULL)
FOR XML PATH(''),TYPE
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thanks everyone for the interesting feed back. I agree that FOR XML PATH is much easier and almost as power ful as EXPLICIT. I also agree that the process of generating an XML tree would be easier using a recursive function.
The purpose of this article was to demonstrate a different method than using a recursive function. A recursive-function approach is already provided in the MS whitepaper I mentioned at the beginning of this article.
Many people find the usage of EXPLICIT is very complex. The difficult part is ordering the rows in the correct way so that the XML is generated in a certain structure. This was one of the points that I wanted to focus on this session.
Thanks again for the inputs.
.
-
Jacob Sebastian
Many people find the usage of EXPLICIT is very complex. The difficult part is ordering the rows in the correct way so that the XML is generated in a certain structure. This was one of the points that I wanted to focus on this session.How right you are Jacob. The best hint I found is that the magic to getting the document elements to come out right is to pay attention to the where clause. You need to put as much info into the cross-product table as you can to get the parent/child relationships to sort.
order by
[Process!2!id]
, [Schedule!3!scheduleId]
, [Notification!4!ownerId]
Make sure that the [Process!2!id] column has an Id (ProcessId in my case) for every row and that the Schedule is populated down as far as possible. I use a zero at the Process level and a 9999 for the Notification level when dealing with the Schedules. NOTE: I wrap a dummy [Processes!1] outer element so that a "real" xml document is returned.
Hope this helps.
--Paul Hunter
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply