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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy