March 1, 2012 at 12:56 pm
I am almost done writing my query however there is one requirement that I am having trouble meeting. I know I can do this in the page code but I would like to do everything in SQL.
I have a udf that returns XML of category structure in this way. This is ok and correct .
Of course category structure is dynamic, so I cannot hard code anything.
SELECT [dbo].[udf_CategoryListSPXML](10866)
<category t="1" name="Rifle Parts" />
<category t="2" name="Sights" />
<category t="3" name="Front Sights" />
but the requirement is
<category t1="Rifle Parts" />
<category t2="Sights" />
<category t3="Front Sights" />
How can I do this in a udf???
my udf code is below...
DECLARE @t TABLE (CategoryID int, CategoryName VARCHAR(500), CatLevel INT, BreadCrumb VARCHAR(500))
;
WITH Cats (CategoryId, CategoryName, DisplayOrder, ParentCategoryId, CatLevel)
AS (
--starting (top-level) point for your hierarchy
SELECT CategoryId, CategoryName, DisplayOrder, ParentCategoryId ,0
FROM dbo.tblToolCategory WITH (NOLOCK)
WHERE CategoryID = @CategoryId and ISNULL(ActiveFlag,0)=1
UNION ALL
--dependent records
SELECT c.CategoryId, c.CategoryName, c.DisplayOrder, c.ParentCategoryId, ch.Catlevel + 1
FROM dbo.tblToolCategory c WITH (NOLOCK) INNER JOIN Cats ch
ON ch.ParentCategoryId = c.CategoryID and ISNULL(ActiveFlag,0)=1
)
INSERT @t
SELECT CategoryId, CategoryName, CatLevel, ''
FROM Cats
ORDER BY CategoryId, ParentCategoryId
-- Fix the CatLevel since we went from bottom to back up, we need to reverse the CatLevel.
Declare @Count int
SET @Count = (Select count(CategoryID) from @t)
Update @t
Set CatLevel = ((CatLevel - (@Count - 1)) * -1) + 1
RETURN
(
SELECT CatLevel as "@t", t.CategoryName as "@name"
FROM @t t
FOR XML PATH('category'), TYPE)
March 2, 2012 at 2:19 am
Instead of:
<category t1="Rifle Parts" />
<category t2="Sights" />
<category t3="Front Sights" />
This would probably make more sense:
<category t1="Rifle Parts" t2="Sights" t3="Front Sights" />
You can achieve this by PIVOT-ing data and then converting to xml.
March 2, 2012 at 6:32 am
Thank you but the problem is how can i pivot it in a function? Columns are dynamic....
March 2, 2012 at 6:45 am
Problem is that dynamic queries (exec, sp_execute) or any procedure call are not allowed in functions.
You could build your xml without "for xml" - directly as string, then convert the string into xml type.
But the whole logic is not clear to me. What are you trying to achieve?
What is the relation between that dynamic columns?
To ask for a badly formatted xml is not a good requirement.
Whole concept is questionable.
March 2, 2012 at 7:23 am
Thank You guys,
sounds like this requirement cannot be met in a udf unless
I build it as a string and then convert it to a XML. That is all I wanted to know.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply