SQL data int XML multiple elements needed

[shpinoza1980]

Hi, I'm trying to create an xml document out of a sql 2005 table

I use this code in a query:

select p.name as "NAME"
from nop_product p
for xml path('PRODUCT'),ROOT('STORE')

my problem is that i need 3 level tree - all <PRODUCT> elements to be under <PRODUCTS> like this:
<STORE>
<PRODUCTS>
<PRODUCT>
<NAME> ..... </NAME>
</PRODUCT>
<PRODUCT>
<NAME> ....... </NAME>
</PRODUCT>
</PRODUCTS>
</STORE>

how do i do that? how do I add another permanent element after the ROOT tag and before the <PRODUCT> tag?
thanks

 

[tsuji]

You can do this.
[tt]
select (select
p.name as 'NAME'
from nop_product p
for xml path('PRODUCT'), TYPE, ROOT('PRODUCTS')
) for xml path(''), ROOT('STORE')
[/tt]