Query out an xml element from a field containing xml data

'message' Field Data:
<xml>
...
<test>ABC</test>
...
</xml
How do I query out 'ABC' from this xml field data in a SQL Server?

Assume message field is in table 'Persons'What you could do, is write a user defined function. Something like

create function [dbo].[fn_GetText]
(
@.Text as XML
)
returns varchar(50)
begin
declare @.Result as varchar(50)
declare @.Count as int
declare @.Start as int

set @.Count = 1
set @.Start = 0

while @.Count <= len(@.Text)
begin
if substring(@.Text, @.Count, 6) = '<test>'
set @.Start = @.Count + 6

if substring(@.Text, @.Count, 7) = '<\test>' and @.Start <> 0
set @.Result = substring(@.Text, @.Start, @.Count - @.Start - 1)

set @.Count = @.Count + 1
end

return @.Result
end

go|||Hi Mike,

thanks for the reply.

As my comfort level is much higher with C#, I just wrote some string
parsing code and got my result :)

Next time I need to spin off a UDF, I will remember this.

Ranjith|||Ranjith (ranjithvenkatesh@.hotmail.com) writes:
> 'message' Field Data:
><xml>
> ...
> <test>ABC</test>
> ...
></xml>
> How do I query out 'ABC' from this xml field data in a SQL Server?
> Assume message field is in table 'Persons'

create table Persons (message xml NOT NULL)
go
insert Persons (message) VALUES('<xml><test>ABC</test></xml>')
go
select message.value(N'(/xml/test)[1]', 'varchar(10)')
from Persons
go
Drop table Persons

This is for SQL 2005. You did not mention which server of SQL Server
you are using. This matters a lot when XML is involved, as the XML
support in SQL 2005 is greatly extended over what is in SQL 2000,
including a new query language, XQuery which I'm using above.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx