CTE to Build XML
-
Hi
I have been experimenting with ways of extracting relational data in forms of xml from SQL Server 2005. I have created some views containg and ID integer field and a XML field. The ID field is what i filter on when selecting data, and the XML field contains the actual data.
I came across a situation where the XML data was recursive in its nature. I have a product Hierarchy where products consists of a list of SubProducts. I solved this by creating a SQL Function which is called recursively in my view, retreiving all products that have the current product as parent. Something Like this:
SELECT
ProductID,
ProductName,
ParentID,
dbo.GetSubProducts(ProductID)
FROM
view_Product
This worked fine with small amount of data, but ended up being very slow when there was a medium amount of data.
I tried refactoring my view to use CTE, cause it seems the way to go.
The problem is i get a folder like structure, and not a XML like structure.
Like this:
Product1
Product2
Product1/Product2
Product1/Product2/Product3
Product1/Product2/Product4
Product3
Product4
What I wanted was like this:
Product1/Product2/Product3+Product4
Product2/Product3+Product4
Product3
Product4
I want one row per product having all subproducts beneath it. A complete tree when looking at a specific product no matter the level in the hierarchy.
I have not posted any sql as i have no real solution to the issue.
I hope you understand the problm anyhow.
Michael
-
Michael, I'm posting this message a lot today, but it would be really helpful if you could post up some sample data and expected results.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
Yes i know that examples ease the job of helping.
I left out the example, cause i wanted to see if anybody had been experimenting with the same type of query, and whether it was possible at all to to what i require.
im working on posting a complete example, with expected result
-
I created a full example including tables, views and data required to test the SQL.
-------------------------------------------------------------------------------------------------
---- DATA STRUCTURE PREPERATION -------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Create a Test Schema
CREATE SCHEMA TEST
GO
-- Create a table with parent/child relationship.
CREATE TABLE [TEST].[Product](
[ID] [int] NOT NULL,
[Name] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ParentID] [int] NOT NULL,
[ParentName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [PK_TEST_Product_1] PRIMARY KEY CLUSTERED
(
[ID] ASC,
[ParentID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
-- Populate table with sample data.
INSERT INTO [TEST].[Product] VALUES (26,'TV',0, NULL)
INSERT INTO [TEST].[Product] VALUES (43,'Buy Settopbox',47,'Settopbox Group')
INSERT INTO [TEST].[Product] VALUES (47,'Settopbox Group',26,'TV')
INSERT INTO [TEST].[Product] VALUES (48,'Rent Settopbox',47,'Settopbox Group')
INSERT INTO [TEST].[Product] VALUES (82,'DR1',98,'Plus TV')
INSERT INTO [TEST].[Product] VALUES (83,'DR2',98,'Plus TV')
INSERT INTO [TEST].[Product] VALUES (84,'Sverige TV1',98,'Plus TV')
INSERT INTO [TEST].[Product] VALUES (85,'Sverige TV2',98,'Plus TV')
INSERT INTO [TEST].[Product] VALUES (88,'TV2',98,'Plus TV')
INSERT INTO [TEST].[Product] VALUES (97,'Basic TV',100,'Standard TV Group')
INSERT INTO [TEST].[Product] VALUES (98,'Plus TV',100,'Standard TV Group')
INSERT INTO [TEST].[Product] VALUES (100,'Standard TV Group',26,'TV')
INSERT INTO [TEST].[Product] VALUES (124,'Special TV Group',26,'TV')
INSERT INTO [TEST].[Product] VALUES (152,'Oprettelse TV',26,'TV')
INSERT INTO [TEST].[Product] VALUES (698,'SmileSport pakke',124,'Special TV Group')
INSERT INTO [TEST].[Product] VALUES (699,'HD Package',124,'Special TV Group')
INSERT INTO [TEST].[Product] VALUES (701,'Discovery Package',124,'Special TV Group')
INSERT INTO [TEST].[Product] VALUES (702,'Kids Package',124,'Special TV Group')
INSERT INTO [TEST].[Product] VALUES (82,'DR1', 97, 'Basic TV')
INSERT INTO [TEST].[Product] VALUES (83,'DR2', 97, 'Basic TV')
-- Create view that selects from the table and defines an xml column.
CREATE VIEW [TEST].[view_Xml_Product]
AS
SELECT
P.ID,
P.[Name],
P.ParentID,
P.ParentName,
(
SELECT
P.ID,
P.[Name],
P.ParentID,
P.ParentName
FOR XML PATH('Product'), TYPE
)
AS [xml]
FROM [TEST].[Product] P WITH (NOLOCK)
GO
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
---- THE RECUSIVE CTE THAT CREATES RECURSIVE XML DOCUMENTS ------------------------------
-------------------------------------------------------------------------------------------------
WITH ProductTree (ID, [Name], ParentID, ParentName, [Path], [xml])
AS
(
SELECT
P.ID,
P.[Name],
P.ParentID,
P.ParentName,
CAST(P.ID AS Varchar(200)) AS [Path],
P.[xml] AS [xml]
FROM [TEST].[view_Xml_Product] AS P WITH (NOLOCK)
UNION ALL
SELECT
P2.ID,
P2.[Name],
P2.ParentID,
P2.ParentName,
CAST(RTRIM(P2.ID) + '->' + P3.[Path] AS varchar(200)) AS [Path],
(
SELECT
P2.ID,
P2.[Name],
P2.ParentID,
P2.ParentName,
P3.[xml] AS SubProductList
FOR XML PATH('Product'), TYPE
) AS SubProductList
FROM [TEST].[view_Xml_Product] AS P2 WITH (NOLOCK)
INNER JOIN ProductTree AS P3 ON P2.ID = P3.ParentID
)
SELECT * FROM ProductTree ORDER BY 1
OPTION (MAXRECURSION 50)
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
When executing the CTE you recieve a "folder-like" structure as shown in the Path field. Actually the XML column has the same structure as the path column.
Like this:
Product1
Product2
Product1->Product2
Product1->Product2->Product3
Product1->Product2->Product4
Product3
Product4
and the xml:
<Product>
<ID>26</ID>
<Name>TV</Name>
<ParentID>0</ParentID>
<SubProductList>
<Product>
<ID>124</ID>
<Name>Special TV Group</Name>
<ParentID>26</ParentID>
<ParentName>TV</ParentName>
<SubProductList>
<Product>
<ID>701</ID>
<Name>Discovery Package</Name>
<ParentID>124</ParentID>
<ParentName>Special TV Group</ParentName>
</Product>
</SubProductList>
</Product>
</SubProductList>
</Product>
What I wanted was like this:
Product1/Product2/Product3+Product4
Product2/Product3+Product4
Product3
Product4
and the xml (not complete, but show the idea):
<Product>
<ID>26</ID>
<Name>TV</Name>
<ParentID>0</ParentID>
<SubProductList>
<Product>
<ID>124</ID>
<Name>Special TV Group</Name>
<ParentID>26</ParentID>
<ParentName>TV</ParentName>
<SubProductList>
<Product>
<ID>698</ID>
<Name>SmileSport pakke</Name>
<ParentID>124</ParentID>
<ParentName>Special TV Group</ParentName>
</Product>
<Product>
<ID>699</ID>
<Name>HD Package</Name>
<ParentID>124</ParentID>
<ParentName>Special TV Group</ParentName>
</Product>
<Product>
<ID>702</ID>
<Name>Kids Package</Name>
<ParentID>124</ParentID>
<ParentName>Special TV Group</ParentName>
</Product>
<Product>
<ID>701</ID>
<Name>Discovery Package</Name>
<ParentID>124</ParentID>
<ParentName>Special TV Group</ParentName>
</Product>
</SubProductList>
</Product>
<Product>
<ID>100</ID>
<Name>Standard TV Group</Name>
<ParentID>26</ParentID>
<ParentName>TV</ParentName>
<SubProductList>
<Product>
<ID>97</ID>
<Name>Basic TV</Name>
<ParentID>100</ParentID>
<ParentName>Standard TV Group</ParentName>
</Product>
<Product>
<ID>98</ID>
<Name>Plus TV</Name>
<ParentID>100</ParentID>
<ParentName>Standard TV Group</ParentName>
</Product>
</SubProductList>
</Product>
</SubProductList>
</Product>
I want one row per product having all subproducts beneath it. A complete tree when looking at a specific product no matter the level in the hierarchy.
I hope you understand what I intend to do.
Michael Vivet
-
I can't see exactly what you're trying to achieve but see if this helps
CREATE FUNCTION dbo.GetSubTree(@ID int)RETURNS XML
BEGIN RETURN
(SELECT ID AS "ID",
[Name] AS "Name",
ParentID AS "ParentID",
dbo.GetSubTree(ID)
FROM [TEST].[view_Xml_Product]
WHERE ParentID=@ID
ORDER BY ID
FOR XML PATH('Product'),ROOT('SubProductList'),TYPE)
END
WITH ProductTree (ID, [Name], ParentID, ParentName, [Path], [xml], [xml2])AS
(
SELECT
P.ID,
P.[Name],
P.ParentID,
P.ParentName,
CAST(P.ID AS Varchar(200)) AS [Path],
P.[xml] AS [xml],
(SELECT P.ID AS "ID",
P.[Name] AS "Name",
P.ParentID AS "ParentID",
dbo.GetSubTree(P.ID)
FOR XML PATH('Product'),TYPE)
FROM [TEST].[view_Xml_Product] AS P WITH (NOLOCK)
UNION ALL
SELECT
P2.ID,
P2.[Name],
P2.ParentID,
P2.ParentName,
CAST(RTRIM(P2.ID) + '->' + P3.[Path] AS varchar(200)) AS [Path],
(
SELECT
P2.ID,
P2.[Name],
P2.ParentID,
P2.ParentName,
P3.[xml] AS SubProductList
FOR XML PATH('Product'), TYPE
) AS SubProductList,
(SELECT P2.ID AS "ID",
P2.[Name] AS "Name",
P2.ParentID AS "ParentID",
dbo.GetSubTree(P2.ID)
FOR XML PATH('Product'),TYPE)
FROM [TEST].[view_Xml_Product] AS P2 WITH (NOLOCK)
INNER JOIN ProductTree AS P3 ON P2.ID = P3.ParentID
)
SELECT * FROM ProductTree ORDER BY 1
OPTION (MAXRECURSION 50)
____________________________________________________
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 i will look at it.
Although it seems alot like my initial apprach using just a the view that i posted and a recursive function as you have written.
I thought that was kinda slow, so i was looking for a faster approach.
Ill use your code in my real-life database objects that contains more records.
Then i can compare the performance.
-
Your solution works, but its slower than just creating a recursive function which is then selected as part of the select statement.
Thank you for your effort.
If you find another faster way than the recursive function please feel free to post.
I am still investigating af better method.
-
Here's another approach generating the full XML once
CREATE FUNCTION dbo.GetSubTree(@ID INT,@Path VARCHAR(MAX))RETURNS XML
BEGIN RETURN
(SELECT ID AS "ID",
[Name] AS "Name",
ParentID AS "ParentID",
@Path + '->' + CAST(ID AS VARCHAR(MAX)) AS "Path",
dbo.GetSubTree(ID,@Path + '->' + CAST(ID AS VARCHAR(MAX)))
FROM [TEST].[view_Xml_Product]
WHERE ParentID=@ID
ORDER BY ID
FOR XML PATH('Product'),ROOT('SubProductList'),TYPE)
END
WITH AllNodes(a) AS (SELECT ID AS "ID",
[Name] AS "Name",
ParentID AS "ParentID",
ID AS "Path",
dbo.GetSubTree(ID,cast(ID as varchar(max)))
FROM [TEST].[view_Xml_Product]
WHERE ParentID=0
FOR XML PATH('Product'),TYPE)
SELECT r.value('ID[1]','INT') AS ID,
r.value('Name[1]','VARCHAR(20)') AS Name,
r.value('ParentID[1]','INT') AS ParentID,
r.value('../../Name[1]','VARCHAR(20)') AS ParentName,
r.value('Path[1]','VARCHAR(100)') AS Path,
r.query('.') AS xml
FROM AllNodes
CROSS APPLY a.nodes('//Product') as x(r)
____________________________________________________
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply