Searching an XML field - 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 2008 Forums

Transact-SQL (2008)

Searching an XML field

Author

Topic

next235
Starting Member

2 Posts

Posted - 2013-04-17 : 08:44:42

Hi

I want to search an XML column and return rows where the XML field contains the tag <Item Tr="12"> - what would be the easiest/best way to do this?

Many thanks in advance

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-17 : 08:50:02

Probably the exist() method. See here for documentation and examples: http://msdn.microsoft.com/en-us/library/ms189869.aspx

next235
Starting Member

2 Posts

Posted - 2013-04-17 : 09:32:46

excuse me for being daft, but is there any chance you could give me a simple example? Basically I have a table which includes the colums DEFINITION which contains an XML definition and NAME, and I wish to return NAMES where the DEFINITION XML column contains <Item Tr="12">.

Thanks.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-17 : 09:49:24

A simple example:

create table #tmp(name varchar(32), xmlCol xml);
insert into #tmp values
('a','<root><Item Tr="11"/></root>'),
('b','<root><Item Tr="12"/></root>');


select
	*
from
	#tmp
where
xmlCol.exist('//Item[@Tr="12"]') = 1;

drop table #tmp;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources