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.
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 TableNamewhere [xString].exist('<t1>a b c</t1>')=1 |
|
|
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 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-13 : 11:54:30
|
try this tooSELECT youcolumns..., CASE WHEN xml_snapshot.exist('<EMailIndicator>1</EMailIndicator>')=0 THEN 'False'ELSE 'True'END isEmailedFROM table t |
|
|
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 |
|
|
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 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
|
|
|