Using SQL 2005

I've not much experience with XML and SQL 2005. I have a field of XML
datatype. How do I look for a specific string within the XML field?

Thanks!

Here is fragment of th data in my xml field. I am looking to get the value
for mediatype.

http://xxx.org/schemas/03/2005/xxxx Version="5.2.9">



Text

.

.

.

.

"Steven" hotmail.com> wrote in message
news:epdHDnWAJHA.1184@TK2MSFTNGP04.phx.gbl...

Here is an example:

DECLARE @x XML
SELECT @x = '
http://xxx.org/schemas/03/2005/xxxx" Version="5.2.9">

Text

'

;WITH XMLNAMESPACES(
DEFAULT 'http://xxx.org/schemas/03/2005/xxxx'
)
SELECT @x.value('(Publication/Identification/MediaType)[1]','VARCHAR(20)')
AS MediaType
/*
MediaType
--------------------
Text
*/

To retrieve values for an XML column, you use the value method of the XML
data-type against the column, eg

DECLARE @xml XML

--SET @xml = 'http://xxx.org/schemas/03/2005/xxxx"
Version="5.2.9">
SET @xml = '
balls
test

'

DECLARE @table TABLE( id INT IDENTITY PRIMARY KEY, your_xml XML )

INSERT INTO @table VALUES ( @xml )

SELECT
t.your_xml.value( '(Publication/Identification/MediaType)[1]','VARCHAR(20)' )
FROM @table t

I had trouble getting this to work with your namespace declaration, which is
why I've commented it out.
HTH
wBob

you can use WITH XMLNAMESPACES to add namespace declarations. for example:

DECLARE @xml XML

SET @xml = '
http://xxx.org/schemas/03/2005/xxxx" Version="5.2.9">
balls
test

'

DECLARE @table TABLE( id INT IDENTITY PRIMARY KEY, your_xml XML )
INSERT INTO @table VALUES ( @xml )

;WITH XMLNAMESPACES(
default 'http://xxx.org/schemas/03/2005/xxxx'
)
SELECT
t.your_xml.value(
'(Publication/Identification/MediaType)[1]','VARCHAR(20)' )
FROM @table t