November 4, 2002 at 9:49 am
The table contains rows of data that relate to each other in a child/parent
fashion. ChildNodeID's belong to ParentNodeID's. The goal is to
dynamically build an XML file from this query that contains a correctly
sorted XML document in hierarchical fashion.
I have the hierarchy working great, but I'm stumped on the sorting aspect of
this stored procedure. I need to sort on the SortOrderNum column in each
parent/child relationship. Any suggestions? I'm stumped! Right now it's
sorting on ChildNodeID.
Thanks in advance for any suggestions.
Dan
-----
CREATE TABLE [dbo].[tblNodeView] (
[ChildNodeID] [int] NULL ,
[ParentNodeID] [int] NULL ,
[PriorParent] [int] NULL ,
[SortOrderNum] [int] NULL ,
[NodeLabel] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContentTemplateText] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ContentTypeID] [int] NULL
) ON [PRIMARY]
INSERT INTO tblNodeView VALUES(2,1,0,35,'Automotive','#NodeLabel#',1)
INSERT INTO tblNodeView VALUES(3,2,1,2,'Toyota','#NodeLabel#',1)
INSERT INTO tblNodeView VALUES(4,2,1,3,'Honda','#NodeLabel#',1)
INSERT INTO tblNodeView VALUES(5,2,1,4,'Chevy','#NodeLabel#',1)
INSERT INTO tblNodeView VALUES(6,1,0,20,'Furniture','#NodeLabel#',1)
INSERT INTO tblNodeView VALUES(7,6,1,6,'Couches','#NodeLabel#',1)
INSERT INTO tblNodeView VALUES(8,6,1,7,'Beds','#NodeLabel#',1)
INSERT INTO tblNodeView VALUES(9,3,2,8,'Trucks','#NodeLabel#',1)
INSERT INTO tblNodeView VALUES(10,3,2,9,'Cars','#NodeLabel#',1)
INSERT INTO tblNodeView VALUES(1,0,-1,11,'Classifieds','#NodeLabel#',1)
CREATE PROC dbo.ShowHierarchyXML
(
@Root int,
@MaxLevels int
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @ChildNodeID int, @curNodeLabel varchar(30), @curChildNodeID int,
@curParentNodeID int, @curPriorParent int, @curSortOrderNum int,
@curContentTypeID int, @curContentTemplateText varchar(500)
SELECT @curNodeLabel=NodeLabel, @curChildNodeID = ChildNodeID,
@curParentNodeID = ParentNodeID, @curPriorParent = PriorParent,
@curSortOrderNum = SortOrderNum, @curContentTypeID = ContentTypeID,
@curContentTemplateText = ContentTemplateText FROM dbo.tblNodeView WHERE
ChildNodeID = @Root
SELECT REPLICATE(' ', @@NESTLEVEL * 4) + '<Node ChildNodeID="' +
CAST(@curChildNodeID as varchar(10)) + '" ParentNodeID="' +
CAST(@curParentNodeID as varchar(10)) + '" PriorParent="' +
CAST(@curPriorParent as varchar(10)) + '" SortOrderNum="' +
CAST(@curSortOrderNum as varchar(10)) + '" ContentTypeID="' +
CAST(@curContentTypeID as varchar(10)) + '" NodeLabel="' + @curNodeLabel +
'" ContentTemplateText="' + @curContentTemplateText + '">'
SET @ChildNodeID = (SELECT MIN(ChildNodeID) FROM dbo.tblNodeView WHERE
ParentNodeID = @Root)
WHILE @ChildNodeID IS NOT NULL and @@NESTLEVEL<=@MaxLevels
BEGIN
EXEC dbo.ShowHierarchyXML @ChildNodeID,@MaxLevels
SET @ChildNodeID = (SELECT MIN(ChildNodeID) FROM dbo.tblNodeView WHERE
ParentNodeID = @Root AND ChildNodeID > @ChildNodeID)
END
SELECT '</Node>'
END
GO
November 5, 2002 at 9:51 am
Since you are using: "SET @ChildNodeID = (SELECT MIN(ChildNodeID) FROM dbo.tblNodeView WHERE ParentNodeID = @Root AND ChildNodeID > @ChildNodeID)", you will get them in ChildNodeID order.
What about using the SortOrderNum to control the looping...Something like this: "SET @ChildNodeID = (SELECT ChildNodeID FROM dbo.tblNodeView WHERE ParentNodeID = @Root AND SortOrderNum = (SELECT MIN(SortOrderNum) FROM dbo.tblNodeView WHERE ParentNodeID = @Root AND SortOrderNum > @SortOrderNum))"
You would have to set @SortOrderNum to the last value processed each time.
-Dan
-Dan
November 5, 2002 at 10:39 am
As a side-note, if you are using SQL 2000, you might want to look at the FOR XML clause, which you can append to queries, and which will automatically perform the job of generating XML output. It supports several options that allow you to control how the output is generated, including one option that will allow you to be very specific as to how the output should be generated; there is a little bit of a learning curve involved with that last option, but not much, and the results can be worth it. Using FOR XML could allow you to be more flexible in your query, so that you could be more explicit about how you want your data ordered, using ORDER BY. It separates the presentation of data from the task of retrieving the data. Just something to consider - maybe you already have.
Matthew Burr
November 5, 2002 at 10:46 am
Yeah I am currently using the FOR XML method and using recursion in the XSL to properly format it...but I need to exhaust this possibility for the sake of determining which method actually executes quicker in situations where the hierarchy is deep and also when it is shallow. I generally don't like formatting XML in the stored proc, but this is a unique situation (aren't they all?! hehe) and it's worth a try to see if it performs any better than the recursion being in the XSL. The one downfall of how I currently use the XSL and FOR XML is that I need to return the entire NodeView table and recurse through it in XSL. If that table becomes huge (which is very possible), it's a lot of unnecessary data returned...by doing the recursion in the stored proc and putting together the XML on-the-fly, I am only pulling out the necessary data.
November 6, 2002 at 10:23 am
Thanks dj, your suggestion helped me out. I did solve it:
CREATE PROC dbo.ShowHierarchyXML2
(
@Root int,
@MaxLevels int
--@SortOrderNumIn int = 0
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @ChildNodeID int,@SortOrderNum int,@curNodeLabel varchar(30), @curChildNodeID int, @curParentNodeID int, @curPriorParent int, @curSortOrderNum int, @curContentTypeID int, @curContentTemplateText varchar(500)
SELECT @curNodeLabel=NodeLabel, @curChildNodeID = ChildNodeID, @curParentNodeID = ParentNodeID, @curPriorParent = PriorParent, @curSortOrderNum = SortOrderNum, @curContentTypeID = ContentTypeID, @curContentTemplateText = ContentTemplateText FROM dbo.NodeView WHERE ChildNodeID = @Root
SELECT REPLICATE(' ', @@NESTLEVEL * 4) + '<Node ChildNodeID="' + CAST(@curChildNodeID as varchar(10)) + '" ParentNodeID="' + CAST(@curParentNodeID as varchar(10)) + '" PriorParent="' + CAST(@curPriorParent as varchar(10)) + '" SortOrderNum="' + CAST(@curSortOrderNum as varchar(10)) + '" ContentTypeID="' + CAST(@curContentTypeID as varchar(10)) + '" NodeLabel="' + @curNodeLabel + '">'
SELECT '<Template><![CDATA[' + @curContentTemplateText + ']]></Template>'
SELECT @SortOrderNum = min(SortOrderNum) FROM dbo.NodeView WHERE ParentNodeID = @Root
SELECT @ChildNodeID = ChildNodeID FROM dbo.NodeView WHERE ParentNodeID = @Root and SortOrderNum = @SortOrderNum
WHILE @SortOrderNum IS NOT NULL and @@NESTLEVEL<=@MaxLevels
BEGIN
EXEC dbo.ShowHierarchyXML2 @ChildNodeID,@MaxLevels--, @SortOrderNum
--SET @ChildNodeID = (SELECT MIN(ChildNodeID) FROM dbo.NodeView WHERE ParentNodeID = @Root AND ChildNodeID > @ChildNodeID)
SET @SortOrderNum = (SELECT MIN(SortOrderNum) FROM dbo.tblNodeView WHERE ParentNodeID = @Root AND SortOrderNum = (SELECT MIN(SortOrderNum) FROM dbo.tblNodeView WHERE ParentNodeID = @Root AND SortOrderNum > @SortOrderNum))
SELECT @ChildNodeID = ChildNodeID FROM dbo.NodeView WHERE ParentNodeID = @Root and SortOrderNum = @SortOrderNum
END
SELECT '</Node>'
END
GO
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply