6 Replies Latest reply: Jun 19, 2012 7:18 AM by AlexAnd RSS

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

    944530
      I have a table Submission Record which contain a field with mix with text string and xml data type.

      ---------------------------------------------------------
      Table name: Submission Record
      --------------------------------------------------------
      Field name: RCA
      -------------------------------------------------------------
      Jason
      Tomato
      <Record>AA</Record>
      Fish
      Brother
      <Record>BB</Record>
      <Record>CC</Record>
      Tom is a girl
      ----------------------------------------------------------

      Its mixing with text and xml data.

      I wish to convert all into xml data as per request from management.

      my select statement is like below...but i think it may contain syntax error. Wish to get help from here.

      sqltestagain = "select '<DATA>' || RCA || '</DATA>' from Submission Record".

      Expect below will display

      <DATA>Jason</DATA>
      <DATA>Tomato</DATA>
      <DATA><Record>AA</Record></DATA>

      '
      '
      '
      '
      '

      or any other solution for it ??

      I've tried FOR XML....DBMS_XML......but none of it worked.....