This discussion is archived
2 Replies Latest reply: Dec 3, 2012 10:45 PM by AlexAnd RSS

xml data convert into only element data

Sachinmrt Explorer
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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>

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points