10 Replies Latest reply: Aug 28, 2012 5:51 AM by odie_63 RSS

    Extract data from xml column to other table

    muttleychess
      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
          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
            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)
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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