For XML clause

[jhoge123@yahoo.com]

I'm trying to turn a product table into an XML file to create an
heirarchical menu, and I was wondering if there was any easy way to do
this. The table is (simplified) below:

Create table product(
category varchar,
subcategory varchar,
name varchar)

and I want the XML to represent the category structure as below:

<Categories>
<sucategories >
<products>
.....
</products>
</subcategories>
</Categories>

Thanks,

John

[Erland Sommarskog]

Re: For XML clause

(jhoge123@yahoo .com) writes:[color=blue]
> I'm trying to turn a product table into an XML file to create an
> heirarchical menu, and I was wondering if there was any easy way to do
> this. The table is (simplified) below:
>
> Create table product(
> category varchar,
> subcategory varchar,
> name varchar)
>
> and I want the XML to represent the category structure as below:
>
><Categories>
> <sucategories >
> <products>
> .....
> </products>
> </subcategories>
></Categories>[/color]

I think that you should be able to do this with FOR XML EXPLICIT.
But I'm not very good at XML queries, so I don't want give an outline of
something that is likely to be incorrect.

If you post a CREATE TABLE statement for the table, INSERT statements
for some sample data, and the desired XML from the data, I might give
it a try.

Or you could look at FOR XML yourself in Books Online. (That's anyway
where I have to look to write this type of queries. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[markc600@hotmail.com]

Re: For XML clause

Create table product(
category varchar(10),
subcategory varchar(10),
name varchar(10))

insert into product(categor y,subcategory,n ame)
values ('Vehicle','Car ','Sports')
insert into product(categor y,subcategory,n ame)
values ('Vehicle','Car ','Saloon')
insert into product(categor y,subcategory,n ame)
values ('Vehicle','Mot orbike','Sports ')
insert into product(categor y,subcategory,n ame)
values ('Vehicle','Mot orbike','Tourer ')

dbcc traceon(257) -- pretty print for Query Analyzer text results

select distinct 1 as Tag,
null as Parent,
category as [Category!1!Valu e],
null as [subcategories!2 !Value],
null as [products!3!Valu e]
from product
union all
select distinct 2 as Tag,
1 as Parent,
category as [Category!1!Valu e],
subcategory as [subcategories!2 !Value],
null as [products!3!Valu e]
from product
union all
select 3 as Tag,
2 as Parent,
category as [Category!1!Valu e],
subcategory as [subcategories!2 !Value],
name as [products!3!Valu e]
from product
order by [Category!1!Valu e],[subcategories!2 !Value],[products!3!Valu e]
for xml explicit

[Erland Sommarskog]

Re: For XML clause

(markc600@hotma il.com) writes:[color=blue]
> dbcc traceon(257) -- pretty print for Query Analyzer text results[/color]

Ah, that's one cute. Didn't know.

And thanks for posting the example!


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp