XML Query Problem? - SQL Server Forums

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

Transact-SQL (2005)

XML Query Problem?

Author

Topic

chava_sree
Yak Posting Veteran

56 Posts

Posted - 2008-10-23 : 21:53:05

Hi all,

I am trying to retrieve XML data based on a Condition.. and unable to get that result.

here is my Query where i need to get only Communityadministrator values to be retrieved but somehow my query gives all records and ignores the where condition.. anyhelp please.
--------------------------------------------------------------
xml Structure
=============

<roles>
<role>
<type>communityblogger</type>
<id>20</id>
</role>
<role>
<type>communityadministrator</type>
<id>106</id>
</role>
</roles>

SELECT [USER_ID],USER_FIRSTNAME,USER_LASTNAME,USER_EMAIL,
n.l.query('
for $ro in /roles/role
where $ro/roles.role.type = "communityadministrator"
return $ro') AS comm_admin,
n.l.value('id[1]','VARCHAR(20)') AS comm_id
FROM #TEMPXML
CROSS APPLY TEST_XML.nodes('//roles/role') n(l)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-24 : 01:35:10

not sure which output you want. so giving both

declare @xml xml

set @xml='<roles>
<role>
<type>communityblogger</type>
<id>23</id>
</role>
<role>
<type>communityadministrator</type>
<id>423</id>
</role>
<role>
<type>communityblogger</type>
<id>12</id>
</role>
<role>
<type>communityadministrator</type>
<id>23</id>
</role>
<role>
<type>communityblogger</type>
<id>11</id>
</role>
<role>
<type>communityadministrator</type>
<id>121</id>
</role>
<role>
<type>communityblogger</type>
<id>202</id>
</role>
<role>
<type>communityadministrator</type>
<id>116</id>
</role>
<role>
<type>communityblogger</type>
<id>35</id>
</role>
<role>
<type>communityadministrator</type>
<id>200</id>
</role>
<role>
<type>communityblogger</type>
<id>45</id>
</role>
<role>
<type>communityadministrator</type>
<id>112</id>
</role>
</roles>'


select @xml.query('/roles/role[type=''communityadministrator'']')

output
--------------------------------------------------
<role><type>communityadministrator</type><id>423</id></role><role><type>communityadministrator</type><id>23</id></role><role><type>communityadministrator</type><id>121</id></role><role><type>communityadministrator</type><id>116</id></role><role><type>communityadministrator</type><id>200</id></role><role><type>communityadministrator</type><id>112</id></role>

(1 row(s) affected)

chava_sree
Yak Posting Veteran

56 Posts

Posted - 2008-10-24 : 03:12:00

Visakh

I might have confused you,

I am getting results but i need to filter those results by adding a where condition.. and am using the below query to get all results..

Query
------

SELECT [USER_ID],USER_FIRSTNAME,USER_LASTNAME,USER_EMAIL,
n.l.value('type[1]','VARCHAR(20)') AS comm_admin,
n.l.value('id[1]','VARCHAR(20)') AS comm_id
FROM #TEMPXML
CROSS APPLY TEST_XML.nodes('//roles/role') n(l)

here are the results. but i need to Filter only "CommunityAdministrator" records
----------------------------------------------------------------------------------

name email community_admin comm_id
===============================================================
Markle cmarkle@cisco.com communityblogger 50
Markle cmarkle@cisco.com communityblogger 20
Markle cmarkle@cisco.com communityadministrat 106
Markle cmarkle@cisco.com communityblogger 108
Markle cmarkle@cisco.com user 141

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-24 : 03:48:58

[code]SELECT c.d.value('id[1]', 'INT') AS ID
FROM @xml.nodes('/roles/role') AS c(d)
WHERE c.d.value('type[1]', 'VARCHAR(22)') = 'communityadministrator'[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-24 : 03:51:20

use this then

select v.u.value('./type[1]','varchar(2000)') as type,v.u.value('./id[1]','int') as type
from @xml.nodes('/roles/role[type=''communityadministrator'']')v(u)


output
--------------------------
type                      type
------------------------- -----------
communityadministrator    423
communityadministrator    23
communityadministrator    121
communityadministrator    116
communityadministrator    200
communityadministrator    112

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources