Posted by Erland Sommarskog on 01/27/06 00:47

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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: select cast help needed
• Prev in forum: Re: Formula Parsing
• Thread view: Re: Query out an xml element from a field containing xml data

[Reply to this message]