TSQL XML Query help - 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)

TSQL XML Query help

Author

Topic

S_Nandra
Starting Member

3 Posts

Posted - 2008-12-30 : 10:43:47

Hi I have the following xml data in a sql table field:

<DVDReport ReportId="294" FixtureId="276797" CustomerId="13">
<PlayerComments>
<HomePlayers>
<Player id="11650" playername="Tim Howard" comments="sadfsadf" type="play" />
<Player id="81122" playername="Jack Rodwell" comments="sssss" type="play" />
<Player id="930" playername="Joleon Lescott" comments="aa" type="play" />
</HomePlayers>
<AwayPlayers>
<Player id="5390" playername="Aaron Mokoena" comments="w er" type="play" />
<Player id="7707" playername="Andre Ooijer" comments=" ewrew" type="play" />
<Player id="412" playername="David Dunn" comments="" type="play" />
</AwayPlayers>
</PlayerComments>
<MatchDetails HomeTeamId="8" AwayTeamId="23" FixtureId="276797" FixtureDate="16 August 2008" CreateDate="2008/12/19" />
</DVDReport>

I want to write a stored procedure that takes an @id parameter in the player node
so that I can retrieve a players comments as:

select xml_data
from XML_DVD_Table
where @id=81122

which should return
<Player id="81122" playername="Jack Rodwell" comments="sssss" type="play" />

I am having trouble working this out, does anyone know the answer to this???

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 12:12:51

Assuming datatype of field is xml, you can use as below


DECLARE @id int
SET @Id=81122--your passed value
SELECT xmlfield.query(for $i in ../Player where $i[id=sql:variable("@Id")]
return $i) AS result
FROM YourTable

S_Nandra
Starting Member

3 Posts

Posted - 2008-12-31 : 08:41:58

Thanks for the reply, I am getting the followinf error:

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'for'.
Msg 126, Level 15, State 1, Line 6
Invalid pseudocolumn "$i".

code...

DECLARE @id int
SET @Id=81122--your passed value

SELECT xml_data.query(for $i in ../Player where $i[id=sql:variable("@Id")]
return $i) AS result
FROM XML_DVD_Table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 08:46:34

does this work atleast?


DECLARE @id int
SET @Id=81122--your passed value
SELECT xmlfield.query(for $i in /DVDReport/PlayerComments/HomePlayers/Player where $i[@id=sql:variable("@Id")]
return $i) AS result
FROM YourTable

also is datatype of your table column xml?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-31 : 10:37:31

See http://weblogs.sqlteam.com/peterl/archive/2008/09/01/Updated-XML-search-test-case-with-variables.aspx

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources