July 20, 2010 at 2:06 pm
Hi all,
I'm having trouble trying trying to write an sql for xml query that will create a nested hierarchy for my data.
I have a table called xmmldata that has three fields I would like to use 'MAST_CAT' 'PARENT_CAT' and 'CATEGORY'
when I perform a distinct select query it returns the data below:
MAST_CAT-------PARENT_CAT-----------CATEGORY
Electronics-------Audio Equipment--------Amplifiers & Receivers
Electronics-------Audio Equipment--------Audio Equipment
Electronics-------Audio Equipment--------Audio Systems
Electronics-------Audio Equipment--------Cables, Parts & Power Supplies
Electronics-------Home Entertainment----Blu-Ray Players
Electronics-------Home Entertainment----DVD Players
Electronics-------Home Entertainment----DVD Recorders
Haircare---------Hair Appliances--------- Hairdryers
The final xml structure I'm trying to achieve should look like this:
<mast_cat name='electronics'>
<parent_cat name='audio equipment'>
<category name='amplifiers & receiver's>
<category name='Audio equipment'>.......
With a single node for each MAST_CAT item followed by multiple child nodes for the respective PARENT_CAT and CATEGORY fields
I've been working with the code below and the closest I've got is pasted below that.
select
1 as tag,
null as parent,
mast_cat as [x!1!mast_cat],
null as [y!2!parent_cat],
null as [z!3!category]
from xmmldata as x
union
select
2 as tag,
1 as parent,
null,
y.parent_cat,
null
from xmmldata y,xmmldata x where x.id=y.id
union
select
3 as tag,
2 as parent,
null,
null,
z.category
from xmmldata x,xmmldata y,xmmldata z where (x.id=y.id and y.id=z.id)
for xml explicit,root('awin')
<awin>
<x mast_cat="Haircare" />
<x mast_cat="Electronics">
<y parent_cat="Audio Equipment" />
<y parent_cat="Home Entertainment" />
<y parent_cat="Hair Appliances">
<z category="Audio Equipment" />
<z category="DJ Equipment" />
<z category="Amplifiers & Receivers" />
<z category="Remote Controls " />
<z category="Home Entertainment" />
<z category="Home Cinema" />
<z category="Projectors " />
<z category="HiFi Speakers" />
<z category="Blu-Ray Players" />
<z category="Radios" />
<z category="Tuners" />
<z category="CD Players" />
<z category="Cassette Decks" />
<z category="Hairdryers" />
<z category="DVD Recorders" />
<z category="Headphones" />
<z category="Audio Systems" />
<z category="Cables, Parts & Power Supplies" />
<z category="Set Top Boxes & Receivers " />
<z category="DVD Players" />
</y>
</x>
</awin>
As you can see I've managed to nest the elements and only return a distinct parent node (There are over 100,000) rows and counting. What I can't seem to manage is to sort the child nodes so they sit under the correct parent node.
I feel like I'm fairly close, and would appreciate some ideas
Thanks in Advance
Philip K
July 21, 2010 at 8:55 am
How's this? I get distinct for both the Mast_Cat field and the Parent_Cat field so there won't be duplicates in the results set.
Table Create
create table xmmldata(
id int identity(1,1) not null,
MAST_CAT varchar(20),
PARENT_CAT varchar(20),
CATEGORY varchar(30))
Sample Data
insert into xmmldata
select 'Electronics','Audio Equipment','Amplifiers & Receivers' union all
select 'Electronics','Audio Equipment','Audio Equipment' union all
select 'Electronics','Audio Equipment','Audio Systems' union all
select 'Electronics','Audio Equipment','Cables, Parts & Power Supplies' union all
select 'Electronics','Home Entertainment','Blu-Ray Players' union all
select 'Electronics','Home Entertainment','DVD Players' union all
select 'Electronics','Home Entertainment','DVD Recorders' union all
select 'Haircare','Hair Appliances',' Hairdryers'
Proposed Solution:
select Mast_Cat as [@Mast_Cat],
(select Parent_Cat as [@Parent_Cat],
(select Category as [@Category]
from xmmldata c
where b.Mast_Cat = c.Mast_Cat
and b.Parent_Cat = c.Parent_cat
for XML Path('Category'), Type
)
from (select distinct Mast_Cat, Parent_Cat from xmmldata) b
where a.Mast_Cat = b.Mast_Cat
FOR XML PATH('Parent_Cat'), TYPE
)
from (select distinct Mast_Cat from xmmldata) a
FOR XML PATH('Mast_Cat'),
ROOT('awin')
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 21, 2010 at 1:40 pm
Hi Mike01
Many thanks for your help - You cracked it! Works a treat. I wanted the category node as distinct too and sorted the whole lot alphabetically. The final query looked like this:
select Mast_Cat as [@Mast_Cat],
(select Parent_Cat as [@Parent_Cat],
(select distinct Category as [@Category]
from xmmldata c
where b.Mast_Cat = c.Mast_Cat
and b.Parent_Cat = c.Parent_cat order by category
for XML Path('Category'), Type
)
from (select distinct Mast_Cat, Parent_Cat from xmmldata) b
where a.Mast_Cat = b.Mast_Cat
FOR XML PATH('Parent_Cat'), TYPE
)
from (select distinct Mast_Cat from xmmldata) a order by mast_cat
FOR XML PATH('Mast_Cat'),
ROOT('awin')
and the xml output looked like this:
<awin>
<Mast_Cat Mast_Cat="Electronics">
<Parent_Cat Parent_Cat="Audio Equipment">
<Category Category="Amplifiers & Receivers" />
<Category Category="Audio Equipment" />
<Category Category="Audio Systems" />
<Category Category="Cables, Parts & Power Supplies" />
<Category Category="Cassette Decks" />
<Category Category="CD Players" />
<Category Category="DJ Equipment" />
<Category Category="HiFi Speakers" />
<Category Category="Radios" />
<Category Category="Tuners" />
</Parent_Cat>
<Parent_Cat Parent_Cat="Home Entertainment">
<Category Category="Blu-Ray Players" />
<Category Category="DVD Players" />
<Category Category="DVD Recorders" />
<Category Category="Headphones" />
<Category Category="Home Cinema" />
<Category Category="Home Entertainment" />
<Category Category="Projectors " />
<Category Category="Remote Controls " />
<Category Category="Set Top Boxes & Receivers " />
</Parent_Cat>
</Mast_Cat>
<Mast_Cat Mast_Cat="Haircare">
<Parent_Cat Parent_Cat="Hair Appliances">
<Category Category="Hairdryers" />
</Parent_Cat>
</Mast_Cat>
</awin>
Thanks again!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply