XML Child - Parent Relationship
-
I have a table with 2 field
ID INT
ParentId INT
Records like1, NULL
2,1
3,2
so basically "1" do not have a parent
2 is child of 1
and 3 is child of 2
I want to show the output as
<
BaseUser Id="3" >
<
BaseUser Id="2" >
<
BaseUser Id="1" />
</
BaseUser>
</
BaseUser>
Kindest Regards,Amit Lohia
-
Hi Amit,
I'm a bit confused.
If 1 doesn't have a parent, why does it have a parent in the XML you've written? Did you mean the XML to be?...
<BaseUser Id="1">
<BaseUser Id="2">
<BaseUser Id="3"/>
</BaseUser>
</BaseUser>
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
yes you are correct. I was not able to get format the XML but the output remain the same
Kindest Regards,Amit Lohia
-
I was more meaning that you had the 1, 2 and 3 in the wrong order (or so it seemed), rather than the output, but no matter.
You basically need to get a data set like this and do a FOR XML EXPLICIT on it.
Tag Parent BaseUser!1!Id BaseUser!2!Id BaseUser!3!Id
----------- ----------- ------------- ------------- -------------
1 NULL 1 NULL NULL
2 1 1 2 NULL
3 2 1 2 3
Don't be confused by the 1s, 2s and 3s! The ones in red above are your data, the other ones are to help structure the XML!
Unfortunately, getting to that is not that 'nice' (particularly if we need to cope with more complicated data sets - and I'm guessing you do!).
I've just built this, and I think it does the trick for up to 9 levels deep. There are probably several articles on this out there on the web - though I've not looked, so there may be better ways than this.
Good luck!
--This SQL Script is safe to run
--Create the data
DECLARE @t TABLE (Id INT, ParentId INT)
INSERT INTO @t
SELECT 1, NULL
UNION SELECT 2, 1
UNION SELECT 3, 2
SET NOCOUNT ON
--Create a table to hold the xml data
DECLARE @tXml TABLE (Tag INT, Parent INT, Level1 INT, Level2 INT, Level3 INT, Level4 INT, Level5 INT, Level6 INT, Level7 INT, Level8 INT, Level9 INT)
--Insert the top level
INSERT INTO @tXml (Tag, Level1)
SELECT 1, Id FROM @t t1 WHERE t1.ParentId IS NULL
--Insert levels 2 - 9 (stop if any level is empty)
DECLARE @i INT
SET @i = 2
WHILE @i <= 9
BEGIN
INSERT INTO @tXml
SELECT
@i,
@i-1,
CASE WHEN @i = 1 THEN Id ELSE Level1 END,
CASE WHEN @i = 2 THEN Id ELSE Level2 END,
CASE WHEN @i = 3 THEN Id ELSE Level3 END,
CASE WHEN @i = 4 THEN Id ELSE Level4 END,
CASE WHEN @i = 5 THEN Id ELSE Level4 END,
CASE WHEN @i = 6 THEN Id ELSE Level4 END,
CASE WHEN @i = 7 THEN Id ELSE Level4 END,
CASE WHEN @i = 8 THEN Id ELSE Level4 END,
CASE WHEN @i = 9 THEN Id ELSE Level4 END
FROM
@tXml x
INNER JOIN @t t1 ON t1.ParentId = CASE @i WHEN 1 THEN NULL
WHEN 2 THEN Level1
WHEN 3 THEN Level2
WHEN 4 THEN Level3
WHEN 5 THEN Level4
WHEN 6 THEN Level5
WHEN 7 THEN Level6
WHEN 8 THEN Level7
WHEN 9 THEN Level8
END
IF @@ROWCOUNT = 0 BREAK
SET @i = @i + 1
END
SET NOCOUNT OFF
--Select the results as XML (note that the order by is important to get the right 'tree')
SELECT
Tag,
Parent,
Level1 AS [BaseUser!1!Id],
Level2 AS [BaseUser!2!Id],
Level3 AS [BaseUser!3!Id],
Level4 AS [BaseUser!4!Id],
Level5 AS [BaseUser!5!Id],
Level6 AS [BaseUser!6!Id],
Level7 AS [BaseUser!7!Id],
Level8 AS [BaseUser!8!Id],
Level9 AS [BaseUser!9!Id]
FROM @tXml
ORDER BY Level1, Level2, Level3, Level4, Level5, Level6, Level7, Level8, Level9
FOR XML EXPLICIT
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Thanks Ryan
Yes I did look into the Explicit option but did not code all the way as it was getting complicated and even I cannot restrict the level to 9. (I am sure we will not go till level 9 but still wanted to be flexible. I am looking into the following approach.
1. Creating Dynamic Query and using XML Auto. This is working and I am getting the output
2. Using XML Data Type and appending the child node (I am having some syntax problem, check my next thread)
Once again thanks for writing the code.
Kindest Regards,Amit Lohia
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply