March 9, 2010 at 5:04 pm
nyone can show me how to generate from this data
------------------------DATA--------------------------
Key ParentKey
5 NULL
25 5
33 25
26 5
27 5
34 27
28 5
29 5
to this XML result?
---------------------RESULTS--------------------------
<record key="5" parentkey = "">
<record key="25" parentkey = "5">
<record key="33" parentkey = "25"></record>
</record>
</record>
<record key="25" parentkey = "5">
<record key="26" parentkey = "5">
<record key="27" parentkey = "5">
<record key="34" parentkey = "27"></record>
</record>
</record>
<record key="28" parentkey = "5">
<record key="29" parentkey = "5">
</record>
March 10, 2010 at 3:23 am
I did solve it using FOR XML EXPLICIT, but I'm not sure it's the best option here. The problem with EXPLICIT is that you need to know how many levels of parents there can be. In your example the deepest level was three, thus my solution is based on that:
-- Create data
create table #x
(
[Key]int,
[ParentKey] int
)
insert into #x
select 5, NULL union all
select 25, 5 union all
select 33, 25 union all
select 26, 5 union all
select 27, 5 union all
select 34, 27 union all
select 28, 5 union all
select 29, 5
-- Need to traverse the structure to get:
-- * node-depth (named level), to be able to use EXPLICIT
-- * a nodePath to be able to sort the rows in correct order
;with Records ([Key], [ParentKey], [level], nodePath)
as
(
select [Key], ParentKey, 1, cast([Key] as varchar(max))
from #x
where ParentKey is null
union all
select x.[Key], x.ParentKey, R.level + 1, R.nodePath + '/' + cast(x.[Key] as varchar(max))
from #x x
inner join Records R on x.ParentKey = R.[Key]
)
select [level] as Tag, [level]-1 as Parent,
[Key] as [Record!1!Key], ISNULL(CAST(ParentKey as varchar(10)), '') as [Record!1!ParentKey],
[Key] as [Record!2!Key], ParentKey as [Record!2!ParentKey],
[Key] as [Record!3!Key], ParentKey as [Record!3!ParentKey]
-- Need to add more rows here to handle more levels of parents and children
from Records
order by nodePath
for xml explicit
/Markus
March 10, 2010 at 4:57 pm
good solution, but assuming the Depth/Level is infinite, creating an uncertain..
[HierarchyKey] as [Hierarchy!2!HierarchyKey],
ParentKey as [Hierarchy!2!ParentKey],
[Level] as [Hierarchy!2!Level],
[HierarchyKey] as [Hierarchy!3!HierarchyKey],
ParentKey as [Hierarchy!3!ParentKey],
[Level] as [Hierarchy!3!Level],
[HierarchyKey] as [Hierarchy!4!HierarchyKey],
ParentKey as [Hierarchy!4!ParentKey],
[Level] as [Hierarchy!4!Level],
[HierarchyKey] as [Hierarchy!5!HierarchyKey],
ParentKey as [Hierarchy!5!ParentKey],
[Level] as [Hierarchy!5!Level],
[HierarchyKey] as [Hierarchy!6!HierarchyKey],
ParentKey as [Hierarchy!6!ParentKey],
[Level] as [Hierarchy!6!Level]
etc, etc... lines of codes are definitely out. how can you handle that? I assume dynamic sql? or you jave a better idea?
March 12, 2010 at 12:58 am
I tried another approach - building the XML "manually" and then cast it to the XML-type:
-- Create some test data
create table #x
(
[Key] int,
[ParentKey] int
)
insert into #x
select 5, NULL union all
select 25, 5 union all
select 33, 25 union all
select 36, 33 union all
select 35, 25 union all
select 26, 5 union all
select 27, 5 union all
select 34, 27 union all
select 28, 5 union all
select 29, 5
-- Create a depth-counter: level
;with Records ([Key], [ParentKey], [level])
as
(
select [Key], ParentKey, 1
from #x
where ParentKey is null
union all
select x.[Key], x.ParentKey, R.level + 1
from #x x
inner join Records R on x.ParentKey = R.[Key]
)
select *, cast(null as varchar(max)) as myXML
into #temp
FROM Records
-- loop from leaf level down to the root and create the XML node, including all child nodes
declare @i int
select @i = max(level) from #temp
while (@i > 0)
begin
-- I use '{' and '}' instead of '<' and '>', because the latter are (for some reason)
-- converted to '<' and '>'. This way I can easy search and replace '{' to '<'..
update x
set myXML = '{Record key="' + cast([Key] as varchar(10)) + '" parentkey="' + isnull(cast(ParentKey as varchar(10)), '') + '"}' +
isnull((select isnull(myXML, '')
from #temp y
where [level] = @i + 1 and y.ParentKey = x.[Key]
for xml path('')
), '') + '{/Record}'
from #temp x
where [level] = @i
set @i = @i - 1
end
-- replace characters and convert into XML
select cast(replace(replace(myXML, '}', '>'), '{', '<') as XML)
from #temp
where ParentKey is null
drop table #temp
drop table #x
Perhaps someone else has a more elegant solution?
/Markus
March 12, 2010 at 2:05 am
I also found this Blog article, where the same problem is solved both by FOR XML together with XSLT and by a recursive CTE together with SQL CLR:
/Markus
March 12, 2010 at 4:12 am
I had the same problem.
It can be solved easily without XSL using a recursive funtion. Check the answer on this thread:
http://www.sqlservercentral.com/Forums/Topic879380-21-1.aspx
March 12, 2010 at 4:22 am
Nice work Michael,
That's a simple and beautiful solution!
/Markus
March 16, 2010 at 6:29 pm
well your suggestions are great, i had the recursive function calls as my first solution during the research but i used this solution from Hunterwood
;with HierarchyTree ([HierarchyKey], [ParentKey], [Level], nodePath)
as
(
select
[HierarchyKey],
[ParentKey],
1,
cast([HierarchyKey] as varchar(max)) as nodePath
from [dbo].[Hierarchy]
where [ParentKey] is null
union all
select
H.[HierarchyKey],
H.[ParentKey],
H1.[Level] + 1,
H1.nodePath + '/' + cast(H.[HierarchyKey] as varchar(max))
from
[dbo].[Hierarchy] H
inner join HierarchyTree H1 on H.ParentKey = H1.[HierarchyKey]
)
select
[level] as Tag,
[level]-1 as Parent,
[HierarchyKey] as [Hierarchy!1!HierarchyKey],
ISNULL(ParentKey, 0) as [Hierarchy!1!ParentKey],
[Level] as [Hierarchy!1!Level],
[HierarchyKey] as [Hierarchy!2!HierarchyKey],
ParentKey as [Hierarchy!2!ParentKey],
[Level] as [Hierarchy!2!Level],
[HierarchyKey] as [Hierarchy!3!HierarchyKey],
ParentKey as [Hierarchy!3!ParentKey],
[Level] as [Hierarchy!3!Level],
[HierarchyKey] as [Hierarchy!4!HierarchyKey],
ParentKey as [Hierarchy!4!ParentKey],
[Level] as [Hierarchy!4!Level],
[HierarchyKey] as [Hierarchy!5!HierarchyKey],
ParentKey as [Hierarchy!5!ParentKey],
[Level] as [Hierarchy!5!Level],
[HierarchyKey] as [Hierarchy!6!HierarchyKey],
ParentKey as [Hierarchy!6!ParentKey],
[Level] as [Hierarchy!6!Level]
from HierarchyTree
order by nodePath
for xml explicit
I weighed in the maximum depth a hierarchy could go through based on the business rules that we have and fixed the depth at maximum of 6. though it is supposed to be virtually unlimited but in the business world that's just too much...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply