Query problem

ghoprat · #1 (**permalink**) 11-28-07, 05:29

Hi

One of my output columns contain xml data .My requirement is I want to write a query which will give an output of a particular portion of the xml data .
Say within the xmldata I have <me>wiqwn</me>.I want only this part as the output.
Can u suggest how should be the query for that be ?

r937 · #2 (**permalink**) 11-28-07, 08:33

Code:

select '<me>wiqwn</me>' as i_found_it
  from daTable
 where thirdcolumn like '%<me>wiqwn</me>%'

ghoprat · #3 (**permalink**) 11-28-07, 08:45

hi

thanks.
But what i actually wanted is not select '<me>wiqwn</me>' because i dont know the "wiqwn" and this will be variable every time .
I have the fixed opening and closing tag i want the contents within it as output .

Thanks in advance.

r937 · #4 (**permalink**) 11-28-07, 09:05

LOCATE('foo',daColumn) gives you the starting position of 'foo' in daColumn

you'll need two of these (one each for the opening and closing tags) together with some arithmetic in a SUBSTRING call