2 Replies Latest reply: Dec 4, 2012 12:45 AM by AlexAnd RSS

    xml data convert into only element data

    Sachinmrt
      Hello everybody,

      I create a form which use the web service. My output is come in xml form like..

      <NewDataSet>
      <Table>
      <PRIVATE_MARKA_BATCH_NO>0622</PRIVATE_MARKA_BATCH_NO>
      </Table>
      <Table>
      <PRIVATE_MARKA_BATCH_NO>DOOR CABINET</PRIVATE_MARKA_BATCH_NO>
      </Table>
      </NewDataSet>
      I want only element data like 0622, DOOR CABINET. Is it possible to remove the xml heading?? Please help..

      Forms [32 Bit] Version 10.1.2.0.2 (Production)
      Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
        • 1. Re: xml data convert into only element data
          AlexAnd
          SQL> select * from v$version where rownum=1;
           
          BANNER
          ----------------------------------------------------------------
          Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
           
          SQL> 
          SQL> select rtrim(xmlagg(xmlelement(e, private_marka_batch_no || ','))
            2               .extract('//text()'),
            3               ',')
            4    from (select xmltype('<NewDataSet>
            5  <Table>
            6  <PRIVATE_MARKA_BATCH_NO>0622</PRIVATE_MARKA_BATCH_NO>
            7  </Table>
            8  <Table>
            9  <PRIVATE_MARKA_BATCH_NO>DOOR CABINET</PRIVATE_MARKA_BATCH_NO>
           10  </Table>
           11  </NewDataSet>') xml
           12             from dual) t,
           13         xmltable('NewDataSet/Table' passing t.xml columns
           14                  private_marka_batch_no varchar2(100) path '.') x
           15  
          SQL> /
           
          RTRIM(XMLAGG(XMLELEMENT(E,PRIV
          --------------------------------------------------------------------------------
          0622,DOOR CABINET
           
          SQL> 
          • 2. Re: xml data convert into only element data
            AlexAnd
            also
            SQL> select * from v$version where rownum=1;
             
            BANNER
            ----------------------------------------------------------------
            Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
             
            SQL>
            SQL> select trim(column_value)
              2    from (select xmltype('<NewDataSet>
              3  <Table>
              4  <PRIVATE_MARKA_BATCH_NO>0622</PRIVATE_MARKA_BATCH_NO>
              5  </Table>
              6  <Table>
              7  <PRIVATE_MARKA_BATCH_NO>DOOR CABINET</PRIVATE_MARKA_BATCH_NO>
              8  </Table>
              9  </NewDataSet>') xml
             10             from dual) t,
             11         xmltable('string-join((for $i in /NewDataSet/Table/PRIVATE_MARKA_BATCH_NO return $i), ",")'
             12                  passing t.xml) x
             13  /
             
            TRIM(COLUMN_VALUE)
            --------------------------------------------------------------------------------
            0622,DOOR CABINET
             
            SQL>