1

I'm using sql server 2008 r2, I have my_table containing my_col of type text which contains xml, I want to select an attribute from the root element of that xml and convert it to a bit type (the attribute takes the value 'true'/'false') when I select a particular row from the table, rather than selecting out the whole string and passing it back to the server as its quite a long string and I could be selecting many rows at a time. I'm not too good with regex or sql. Here is the format of my text/xml column:

<rootElementName <!--lots of attributes--> Recommended="false"><!--...lots of stuff in here...--></rootElementName>

I just want to select out 0 for rows that have Recommended="false" and 1 for Recommended="true".

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Daniel Robinson
  • 13,806
  • 18
  • 64
  • 112
  • [*`ntext`, `text`, and `image` data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use `nvarchar(max)`, `varchar(max)`, and `varbinary(max)` instead.*](http://msdn.microsoft.com/en-us/library/ms187993.aspx) – marc_s Sep 22 '13 at 11:08
  • And if that column contains XML - then why isn't it of datatype `XML` ?!?!? – marc_s Sep 22 '13 at 11:09
  • because it was originally implemented by some one who had absolutely no idea what they were doing now I have to live with it like that, I will ask the team if the table schema can be updated to change that column to xml type, but Im guessing there will be some flakey reason it can't be – Daniel Robinson Sep 22 '13 at 11:20

1 Answers1

4

First you have to convert your text column to an xml type and then do the XQuery. 

SELECT convert(XML,my_col).value('(/rootElementName/@Recommended)[1]', 'bit') rec
FROM  my_table

I looked up/adapted stuff from this question and this question.

The conversion idea came from here

Here is a working SqlFiddle

Community
  • 1
  • 1
rene
  • 41,474
  • 78
  • 114
  • 152