This discussion is archived
7 Replies Latest reply: Dec 3, 2012 9:35 PM by Sachinmrt 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
    Sachinmrt Explorer
    Currently Being Moderated
    Is it possible to get only element variable one by one, if yes then plz tell me..thanks in advance..
  • 2. Re: xml data convert into only element data
    BluShadow Guru Moderator
    Currently Being Moderated
    Like this...
    SQL> ed
    Wrote file afiedt.buf
    
      1  WITH t AS (SELECT xmltype('<NewDataSet>
      2    <Table>
      3      <PRIVATE_MARKA_BATCH_NO>0622</PRIVATE_MARKA_BATCH_NO>
      4    </Table>
      5    <Table>
      6      <PRIVATE_MARKA_BATCH_NO>DOOR CABINET</PRIVATE_MARKA_BATCH_NO>
      7    </Table>
      8  </NewDataSet>') xml FROM dual)
      9  --
     10  -- end of test data
     11  --
     12  select x.*
     13  from   t
     14        ,xmltable('NewDataSet/Table'
     15                  passing t.xml
     16                  columns pmbn varchar2(30) path './PRIVATE_MARKA_BATCH_NO'
     17*                ) x
    SQL> /
    
    PMBN
    ------------------------------
    0622
    DOOR CABINET
  • 3. Re: xml data convert into only element data
    Sachinmrt Explorer
    Currently Being Moderated
    yes it's work...sir you create the table through qurey..
    Now plz suggest me if my xml data store in clob column than how can i make the same query..where i need to change??

    suppose my table name is TEST and clob cloumn name is xml_data


    thanks sir.. my problem is solved..

    Edited by: Sachingmrt on Dec 3, 2012 5:30 PM
  • 4. Re: xml data convert into only element data
    BluShadow Guru Moderator
    Currently Being Moderated
    Sachingmrt wrote:
    yes it's work...sir you create the table through qurey..
    Now plz suggest me if my xml data store in clob column than how can i make the same query..where i need to change??

    suppose my table name is TEST and clob cloumn name is xml_data


    thanks sir.. my problem is solved..
    XML is best stored in XMLTYPE datatypes, but as you have it in a CLOB it can be easily converted to XMLTYPE using the constructor method for that...
    select x.*
    from   test
          ,xmltable('NewDataSet/Table'
                    passing xmltype(xml_data)
                    columns pmbn varchar2(30) path './PRIVATE_MARKA_BATCH_NO'
                   ) x
    / 
  • 5. Re: xml data convert into only element data
    Sachinmrt Explorer
    Currently Being Moderated
    Thanks a lot sir... Now my problem is sloved... sir please give the any link which help me working on xml data.
  • 6. Re: xml data convert into only element data
    ranit B Expert
    Currently Being Moderated
    any link which help me working on xml data.
    Please refer these OTN links...
    >
    If you have questions related to using XML with Oracle database 9.1 and earlier please use the XML forums at http://forums.oracle.com/forums/category.jspa?categoryID=51.
    >
    1] https://forums.oracle.com/forums/category.jspa?categoryID=51

    >
    This forum provides you with an opportunity to discuss issues related to XML DB. Oracle XML DB is feature of Oracle Database 9i Release 2 and later. The minimum supported release for XML DB functionality is patch release 9.2.0.3.0.
    >
    2] XML DB

    Hope this Helps,
    Ranit B.

    Edited by: ranit B on Dec 3, 2012 6:03 PM
  • 7. Re: xml data convert into only element data
    Sachinmrt Explorer
    Currently Being Moderated
    Thanks...

Legend

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