How to convert the sql query result into xml ? PLease..Please..

[ocs2me]

I have a table which mix with text and xml datatype.

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

|         T1                            |

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

Apple

Banana

Orange

<Name> Jason </Name>

Tomato

<Drink> Cola </Drink>

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

I have to query and filter which one is text and which one is XML and put it into different column. What should  i do ?

Should i convert all into xml data type first ? If yes, what is the example code will looks like ?or any other solution ?

I've tried create table , DBMS_XMLGEN......but its not working at all...

[rareddy]

Jason,

Took me little time, but here is what I got, it was working as expected.

SELECT
   CASE WHEN LOCATE('<', x.name) = 0 THEN name
   ELSE (select doc.name from XMLTABLE('/*' PASSING XMLPARSE(DOCUMENT x.name) COLUMNS name string PATH 'text()') as doc) 
   END as foo
FROM (SELECT * FROM TEXTTABLE(UNESCAPE('name\nApple\nBanana\nOrange\n<Name> Jason </Name>\nTomato\n<Drink> Cola </Drink>') COLUMNS name string HEADER) as y) as x

Note the "FROM" clause, I am just trying to duplicate contents of your table, you would replace that with your table name. What I did is check the charcter at position 1, if it is "<" then I assumed that as XML, then parsed it and grabbed the first text out of it, else used the name as is.

Hope this helps.

Ramesh..