Member Avatar for Ninad

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 ??

  • 2 Contributors
  • 1 Reply
  • 186 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by kb.net
Member Avatar for kb.net

Hi,
Try adding this to the end of the query:
"group By Item Type"

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.