1 Reply Latest reply on Oct 31, 2015 12:48 AM by Srini Chavali-Oracle

    Parse XML and insert into Oracle Table

    Ragul Halan

      Hi All,

       

      I have an XML document, which I need to parse and take the data from respective tags and insert it into another table.

       

      here is the XML I am having.

       

      <convertTo xsi:schemaLocation="https://xecdapi.xe.com/schema/v1/convertTo.xsd">

      <terms>http://www.xe.com/privacy.php</terms>

      <privacy>http://www.xe.com/legal/dfs.php</privacy>

      <to>USD</to>

      <amount>1.0</amount>

      <timestamp>2015-10-25T23:00:00Z</timestamp>

      <from>

        <rate>

        <currency>EUR</currency>

        <mid>0.9075541422</mid>

        </rate>

        <rate>

        <currency>INR</currency>

        <mid>65.0313451105</mid>

        </rate>

        <rate>

        <currency>CAD</currency>

        <mid>1.3167560135</mid>

        </rate>

        <rate>

        <currency>GBP</currency>

        <mid>0.6528693249</mid>

        </rate>

      </from>

      </convertTo>


      Here is the code I am using to parse the values

       

      DECLARE

        x XMLType := XMLType(

        '<convertTo xsi:schemaLocation="https://xecdapi.xe.com/schema/v1/convertTo.xsd">

      <terms>http://www.xe.com/privacy.php</terms>

      <privacy>http://www.xe.com/legal/dfs.php</privacy>

      <to>USD</to>

      <amount>1.0</amount>

      <timestamp>2015-10-25T23:00:00Z</timestamp>

      <from>

      <rate> 

      <currency>EUR</currency> 

      <mid>0.9075541422</mid>

      </rate>

      <rate> 

      <currency>INR</currency> 

      <mid>65.0313451105</mid>

      </rate>

      <rate> 

      <currency>CAD</currency> 

      <mid>1.3167560135</mid>

      </rate>

      <rate> 

      <currency>GBP</currency> 

      <mid>0.6528693249</mid>

      </rate>

      </from>

      </convertTo>'

        );

      BEGIN

        FOR r IN

        (

          SELECT

            ExtractValue(Value(p),'/rate/currency/text()') AS name

            --,ExtractValue(Value(p),'/row/Address/State/text()') as state

            --,ExtractValue(Value(p),'/row/Address/City/text()') as city

          FROM

            TABLE(XMLSequence(Extract(x,'convertTo/from/rate'))) p

        )

        LOOP

          -- do whatever you want with r.name, r.state, r.city

          dbms_output.put_line ('Name'||r.name);

        END LOOP;

      END;

       

      I am getting the below error message,

       

      Error report:

      ORA-31011: XML parsing failed

      ORA-19202: Error occurred in XML processing

      LPX-00234: namespace prefix "xsi" is not declared

      Error at line 1

      ORA-06512: at "SYS.XMLTYPE", line 310

      ORA-06512: at line 2

      31011. 00000 -  "XML parsing failed"

      *Cause:    XML parser returned an error while trying to parse the document.

      *Action:   Check if the document to be parsed is valid.

       

      Any help on how to resolve this would be really helpful.

       

      Appreciate your time and help.

       

      Thanks,

      Ragul