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 2005 Forums
 Transact-SQL (2005)
 Converting XML column to string and searching it

Author  Topic 

toddhd
Yak Posting Veteran

55 Posts

Posted - 2009-01-13 : 11:05:27
I have one column in my table defined as an XML column. I need to search that column for a certain string, and report back true/false if it exists. I think I'm heading in the basic right direction, but I'm still getting an error when trying to convert the XML to a string. Any advice?

WITH Recipients AS (
SELECT ROW_NUMBER() OVER(ORDER BY dr.created_dt DESC) AS RowNum,
TotalRecords = Count(*) OVER(),
dr.doc_recipient_id,
dr.base_communication_id,
dr.doc_run_id,
dr.[object_Id],
dr.object_type,
dr.document_id,
dr.document_location,
dr.display_name,
dr.created_by,
dr.created_dt,
dr.updated_by,
dr.updated_dt,
dx.xml_snapshot,
-- HERE IS WHERE THE ERROR IS - CAN"t CAST XML TO STRING
CASE CHARINDEX(CAST(dx.xml_snapshot AS varchar(max)),'<EMailIndicator>1')
WHEN 0 THEN 'False'
WHEN 1 THEN 'True'
END isEmailed
FROM

-Todd Davis

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-13 : 11:23:43
SELECT [xString]
FROM TableName

where [xString].exist('<t1>a b c</t1>')=1
Go to Top of Page

toddhd
Yak Posting Veteran

55 Posts

Posted - 2009-01-13 : 11:48:35
I tried changing it to this, based on your suggestion, but in every case, it always returns true. What am I doing wrong?

dx.xml_snapshot,
--CASE CHARINDEX(CAST(dx.xml_snapshot AS varchar(5000)),'<EMailIndicator>1')
CASE (dx.xml_snapshot.exist('<EMailIndicator>1</EMailIndicator>'))
WHEN 0 THEN 'False'
ELSE 'True'
END isEmailed

-Todd Davis
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-13 : 11:54:30
try this too

SELECT youcolumns..., CASE WHEN xml_snapshot.exist('<EMailIndicator>1</EMailIndicator>')=0 THEN 'False'
ELSE 'True'
END isEmailed
FROM table t
Go to Top of Page

toddhd
Yak Posting Veteran

55 Posts

Posted - 2009-01-13 : 14:26:14
This still only returns true. In fact, I made some records where the stored XML contains only "<Test />", and it is returning true for those too. Weird.

Some examples of Exist() I've seen on the web use path notation (i.e. some/path/to[value="myValue"]) but that doesn't seem to work either. I'm so confused.

I guess there is no way to convert the XML to a varchar or something like that? When I do, it gives me a warning that the data may be truncated (whether it is or not) and fails.

-Todd Davis
Go to Top of Page

toddhd
Yak Posting Veteran

55 Posts

Posted - 2009-01-13 : 14:48:41
Just as a followup, I got it working. Thanks again for your help, I wasn't aware of exist(), and now I have some XPath knowledge too. :)
The solution was:

CASE WHEN xml_snapshot.exist('//EMailIndicator[contains(string(),"1")]') = 0 THEN 'False'
ELSE 'True'
END isEmailed


-Todd Davis
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-13 : 16:22:49
See blog post here
http://weblogs.sqlteam.com/peterl/archive/2008/09/01/Updated-XML-search-test-case-with-variables.aspx



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page
   

- Advertisement -