SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Searching an XML field
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

next235
Starting Member

2 Posts

Posted - 04/17/2013 :  08:44:42  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3659 Posts

Posted - 04/17/2013 :  08:50:02  Show Profile  Reply with Quote
Probably the exist() method. See here for documentation and examples: http://msdn.microsoft.com/en-us/library/ms189869.aspx
Go to Top of Page

next235
Starting Member

2 Posts

Posted - 04/17/2013 :  09:32:46  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3659 Posts

Posted - 04/17/2013 :  09:49:24  Show Profile  Reply with Quote
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;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000