1 Reply Latest reply on Jun 19, 2012 1:31 PM by Ramesh Reddy

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

    Jason Mana Newbie

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



      |         T1                            |





      <Name> Jason </Name>


      <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...

        • 1. Re: How to convert the sql query result into xml ? PLease..Please..
          Ramesh Reddy Master



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


             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.