6 Replies Latest reply: Apr 19, 2012 6:09 AM by 931798 RSS

    Extracting of data from xml takes almost an hour for 2.5M data.

    user1107506
      Hi,

      Any help would be highly appreciated. Extracting of data from xml takes almost an hour for 2.5M data. Is there any solution to this.

      WITH t AS
      ( SELECT XMLTYPE(response ) col FROM dual
      )
      SELECT
      b.entity_id,
      c.instance_id,
      d.attribute_id,
      d.data_type,
      d.attribute_value,
      d.outcome_style
      FROM t,
      XMLTABLE(XMLNamespaces('http://schemas.xmlsoap.org/soap/envelope/' AS "SOAP-ENV"
      , 'http://oracle.com/determinations/server/10.3/rulebase/assess/types' AS "typ")
      , 'SOAP-ENV:Envelope/SOAP-ENV:Body/typ:assess-response/typ:global-instance/typ:entity'
      PASSING t.col
      COLUMNS entity_id VARCHAR2(256) path '@id'
      , res_tmp2 XMLTYPE path 'typ:instance') b
      , xmltable(XMLNamespaces('http://schemas.xmlsoap.org/soap/envelope/' AS "SOAP-ENV"
      , 'http://oracle.com/determinations/server/10.3/rulebase/assess/types' AS "typ")
      , 'typ:instance'
      PASSING b.res_tmp2
      COLUMNS instance_id VARCHAR2(256) path '@id'
      , res_tmp3 XMLTYPE path 'typ:attribute') c
      , xmltable(XMLNamespaces('http://schemas.xmlsoap.org/soap/envelope/' AS "SOAP-ENV"
      , 'http://oracle.com/determinations/server/10.3/rulebase/assess/types' AS "typ")
      , 'typ:attribute'
      PASSING c.res_tmp3
      COLUMNS attribute_id VARCHAR2(256) path '@id'
      , data_type VARCHAR2(256) path '@type'
      , attribute_value VARCHAR2(256) path '.'
      , outcome_style VARCHAR2(256) path '@inferred' ) d;

      Thanks
      Ankith