I've following database structure (just junk values)
[ChannelID] [ChannelName] [ChannelCatID] [RSSFeedURL]
1 YouTube 1 ret
2 GoogleVideo 1 ert
3 MSNVideo 1 rtertert
4 ABC 2 retert
5 YahooVideo 1 erter
6 CBS 2 rtr
7 NBC 2 trtrt
8 ESPN 2 rt
9 FOX 2 rtrtttr
10 AOL 1 rt
I wanna generate following XML structure out of it
- <DOC>
- <Item Type="xyz">
- <Channel id="a">
<Name>aaaaa<Name/>
<URL>aaaaa<URL/>
</Channel>
- <Channel id="b">
<Name>bbbb<Name/>
<URL>bbbb<URL/>
</Channel>
---
---
</Item>
- <Item Type="lmn">
- <Channel id="x">
<Name>xxxx<Name/>
<URL>xxxx<URL/>
</Channel>
- <Channel id="y">
<Name>yyyy<Name/>
<URL>yyyy<URL/>
</Channel>
---
---
</Item>
------
------
------
</DOC>
I 've written this Select For XML query
SELECT ChannelCatID as '@type',
ChannelID as 'Channel/@id',
ChannelName as 'Channel/Name',
RSSFeedURL as 'Channel/URL'
FROM ChannelMaster
FOR XML PATH('Item'), ROOT('DOC')
It gives me this result
- <DOC>
- <Item Type="xyz">
- <Channel id="a">
<Name>aaaaa<Name/>
<URL>aaaaa<URL/>
</Channel>
</Item>
- <Item Type="xyz">
- <Channel id="b">
<Name>bbbb<Name/>
<URL>bbbb<URL/>
</Channel>
</Item>
- <Item Type="xyz">
---
</Item>
- <Item Type="xyz">
---
</Item>
------
------
------
</DOC>
I want it this way:
all channels of Type xyz should come under singe <Item Type="xyz"> tag
instead of seperate tags
what changes should i make ??
Unsolved 
