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                            |

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

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

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

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