How to query and filter a field that mix with text datatype and xml datatype

• ocs2me

  SSC Rookie

  Points: 42

  More actions

  June 18, 2012 at 11:55 pm

  #257712

  I have a table [T1] which stored the text data type. Unfortunately, the management decided to replace the following input data by xml data without change the field's datatype. So the field is mix with text string and xml data. Is it possible to use sql to query both datatype?

  -----------------------

  | T1 |

  -----------------------

  King

  Queen

  <Food> Apple </Food>

  Teacher

  Student

  <Vegetable> Tomato </Vegetable>

  ---------------------------

  The table field is mix with text data and xml data.

  What should i do if i only wish to extract the xml data only or the text string only ?

• Adi Cohn-120898

  SSC-Dedicated

  Points: 33944

  More actions

  June 19, 2012 at 2:06 am

  #1502609

  Obviesly as you wrote it would be much better to use an XML column and not store the XML as string. Having said that, depending on your text in the column, you'll might be able to add in the where clause a criteria that checks if the how the column begins and ends. Have a look at the script bellow that shows how to it. Take into account that there could be cases that this check will fail and it is not 100% sure

  use tempdb

  go

  create table Demo (vc varchar(100))

  go

  insert into Demo (vc)

  select 'King'

  union select 'Queen'

  union select '<Food> Apple </Food>'

  union select 'Teacher'

  union select 'Student'

  union select '<Vegetable> Tomato </Vegetable>'

  go

  with XMLRecords as (

  select cast(vc as xml) as xmlc

  from Demo

  where vc like '<%>')

  select xmlc.query('.') from XMLRecords

  go

  drop table Demo

  Adi

  --------------------------------------------------------------
  To know how to ask questions and increase the chances of getting asnwers:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Dwain Camps

  SSC Guru

  Points: 86978

  More actions

  June 19, 2012 at 7:27 pm

  #1503183

  Maybe this is too simplistic but couldn't you do something like this?

  create table #Demo (vc varchar(100))

  insert into #Demo (vc)

  select 'King'

  union select 'Queen'

  union select '<Food> Apple </Food>'

  union select 'Teacher'

  union select 'Student'

  union select '<Vegetable> Tomato </Vegetable>'

  -- Query non-XML

  SELECT vc

  FROM #Demo

  WHERE SUBSTRING(LTRIM(vc), 1, 1) <> '<'

  -- Query XML

  SELECT vc

  FROM #Demo

  WHERE SUBSTRING(LTRIM(vc), 1, 1) = '<'

  DROP TABLE #Demo

  ---

  My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

  My thought question: Have you ever been told that your query runs too fast?

  My advice:
  INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
  The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

  Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
  Since random numbers are too important to be left to chance, let's generate some![/url]
  Learn to understand recursive CTEs by example.[/url]
  [url url=http://www.sqlservercentral.com/articles/St