Help me making FOR XML QUERY plezz!!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Help me making FOR XML QUERY plezz!!

Author

Topic

Ninad
Starting Member

1 Post

Posted - 2007-09-20 : 01:49:07

OK Here goes my BIG question

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, plezz help meout wit dis ??

.(]\[)[](]\[).

tm
Posting Yak Master

160 Posts

Posted - 2007-10-01 : 16:09:51

Hope this helps as I didn't get exactly what you require but close.

The data used (data comes from AdventureWorks sample database):

purchaseorderid PurchaseOrderDetailID orderqty
--------------- --------------------- --------
2 2 3
2 3 3
3 4 550

select purchaseorderid as [Type] ,
(
select PurchaseOrderDetailID as [id],
OrderQty as [name]
from purchasing.PurchaseOrderDetail as [DetailItems]
where purchaseorderid = [Item].purchaseorderid
for xml AUTO, type, elements
)
from
(
select distinct Purchaseorderid
from purchasing.PurchaseOrderDetail
) [Item]
where [Item].purchaseorderid in (2,3)
FOR XML AUTO, ROOT('DOC')

>> The above gives ..

<DOC>
<Item Type="2">
<DetailItems>
<id>2</id>
<name>3</name>
</DetailItems>
<DetailItems>
<id>3</id>
<name>3</name>
</DetailItems>
</Item>
<Item Type="3">
<DetailItems>
<id>4</id>
<name>550</name>
</DetailItems>
</Item>
</DOC>

quote:
Originally posted by Ninad

OK Here goes my BIG question

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, plezz help meout wit dis ??

.(]\[)[](]\[).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources