This discussion is archived
10 Replies Latest reply: Aug 28, 2012 3:51 AM by odie_63 RSS

Extract data from xml column to other table

muttleychess Newbie
Currently Being Moderated
Hi

I have a table with clob column, inside have a xml, How can I to extract data inside column

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE     9.2.0.8.0     Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
  • 1. Re: Extract data from xml column to other table
    damorgan Oracle ACE Director
    Currently Being Moderated
    SELECT <column_name> FROM <table_name>;
    Should work reasonably well.

    The fact that the string has the structure of XML is irrelevant to the fact that it is just one big string.
  • 2. Re: Extract data from xml column to other table
    muttleychess Newbie
    Currently Being Moderated
    Thank, but I want to extract data inside tags from xml
    SQL> SELECT xmldoc
      2    FROM xml_documents
      3  /

    XMLDOC
    --------------------------------------------------------------------------------
    <!-- claim77804.xml -->
    <Claim>
    <ClaimId>77804</ClaimId>
    <Policy>12345</Policy>
    <Settlements>
    <Payment Approver="JCOX">1000</Payment>
    <Payment Approver="PSMITH">1850</Payment>
    </Settlements>
    <DamageReport>
    The insured's <Vehicle Make="Volks">Beetle</Vehicle>
    broke through the guard rail and plummeted into a ravine.
    The cause was determined to be <Cause>faulty brakes</Cause>.
    Amazingly there were no casualties.
    </DamageReport>
    </Claim>

    SQL>
    I need only *12345* from Policy Tag,...etc
    Payment Approver="JCOX" ==> 1000
    Payment Approver="PSMITH"==>1850
  • 3. Re: Extract data from xml column to other table
    Jason_(A_Non) Expert
    Currently Being Moderated
    Since you are 9.2.0.8, you are looking for the FROM clause to contain
    TABLE(XMLSequence(extract(...)))
    and the SELECT columns will contain extract or extractValue, depending upon your need.

    There are plenty of old examples floating around the forum. I would suggest you search for "TABLE(XMLSequence(extract"

    That should get you started and then we can help you when you get stuck.
  • 4. Re: Extract data from xml column to other table
    muttleychess Newbie
    Currently Being Moderated
    thank you

    I have a table
    SQL> desc xml_documents;
    Name      Type          Nullable Default Comments
    --------- ------------- -------- ------- --------
    DOCNAME   VARCHAR2(200)                          
    XMLDOC    CLOB          Y                        
    TIMESTAMP DATE          Y                        
    inside XMLDOC column there is only a record below
    <!-- claim77804.xml -->
    <Claim>
    <ClaimId>77804</ClaimId>
    <Policy>12345</Policy>
    <Settlements>
    <Payment Approver="JCOX">1000</Payment>
    <Payment Approver="PSMITH">1850</Payment>
    </Settlements>
    <DamageReport>
    The insured's <Vehicle Make="Volks">Beetle</Vehicle>
    broke through the guard rail and plummeted into a ravine.
    The cause was determined to be <Cause>faulty brakes</Cause>.
    Amazingly there were no casualties.
    </DamageReport>
    </Claim>
    I tried to use example from : http://www.oracle-base.com/articles/9i/xmlsequence.php

    SELECT extract(value(d), '//POLICY/text()').getStringVal() AS empno
     FROM    xml_documents x,
           table(xmlsequence(extract(x.xmldoc, '/ROWSET/Claim'))) d;
    But no work, show me error
    ORA-00932: inconsistent datatypes: expected - got -
  • 5. Re: Extract data from xml column to other table
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    The example is based on XML datatype stuff, so XMLTYPE.
    You don't use XML content but CLOB content (to say it bluntly)
    SELECT extract(value(d), '//POLICY/text()').getStringVal() AS empno
     FROM    xml_documents x,
           table(xmlsequence(extract(xmltype(x.xmldoc), '/ROWSET/Claim'))) d;
    You better switch for these kind of CLOB based xml questions to the XML forum. The XMLDB forum is mainly dedicated for answering questions based on XMLTYPE.
  • 6. Re: Extract data from xml column to other table
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    As stated in Tim's (Hall) post, its based on XMLTYPE
    CREATE TABLE xml_tab OF XMLTYPE;
    
    DECLARE
      l_xmltype XMLTYPE;
    BEGIN
      SELECT sys_xmlagg(
               xmlelement(
                 "EMP",
                 xmlforest(e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm)
               )
             )
      INTO   l_xmltype
      FROM   emp e;
    
      INSERT INTO xml_tab VALUES (l_xmltype);
      COMMIT;
    END;
    /
    CREATE TABLE xml_tab OF XMLTYPE;
    ...
    ...
    DECLARE
      l_xmltype XMLTYPE;
    ...
    ...
  • 7. Re: Extract data from xml column to other table
    muttleychess Newbie
    Currently Being Moderated
    How can to Extract when column is a clob, but inside is a xml ? :-(
  • 8. Re: Extract data from xml column to other table
    damorgan Oracle ACE Director
    Currently Being Moderated
    One solution: Cast the CLOB to XML using the XMLCAST function.

    Another is to convert using the XMLTYPE's CREATEXML static function.
    http://www.morganslibrary.org/library.html
  • 9. Re: Extract data from xml column to other table
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    XMLTYPE(clob column) will do the job fine.

    from your own library http://www.morganslibrary.org/reference/xml_functions.html
    EXTRACT(XMLType_Instance>, <XPath_string>, <namespace_string>)
    Not that it makes that much difference. The performance will range from NOT GOOD up to HORRIBLE as long as he is using CLOB columns to store XML.

    CLOB's are optimized for CHARACTER LARGE OBJECTS.

    XMLTYPE are optimized for storage of ...???

    And I know for sure what the next question will be if he gets this XPath extract syntax correct ("I got the following extract statement. How do I get it to perform. It always worked, but now it is too slow...")

    As long as he is using CLOB columns to store XML, it would be good to try to find answers on a different forum.

    Edited by: Marco Gralike on Aug 27, 2012 10:32 PM
  • 10. Re: Extract data from xml column to other table
    odie_63 Guru
    Currently Being Moderated
    damorgan wrote:
    One solution: Cast the CLOB to XML using the XMLCAST function.
    No, XMLCast function just does the opposite, and is not available in OP's version anyway.

    http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb04cre.htm#ADXDB4249

Legend

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