troubleshooting Question

Oracle Parsing XML into table

Avatar of g_currier
g_currierFlag for Germany asked on
Oracle DatabaseProgramming
7 Comments1 Solution617 ViewsLast Modified:
greetings,

this ugly piece of code below is the best way I could find to parse XML into a table.

What we have below is just a select from a typed in value (copy from file, then paste in).

The issue I am having is the aliasing: Oracle keeps throwing the invalid identifier error and it looks right to me...

I could use the help of a second or third set of eyes to explain to me where I am making my mistakes...

Thanks

SET DEFINE OFF

WITH temp_table
     AS (SELECT XMLTYPE (
                   '<?xml version="1.0"?>
<q:quakeml xmlns="http://quakeml.org/xmlns/bed/1.2" xmlns:catalog="http://anss.org/xmlns/catalog/0.1" xmlns:q="http://quakeml.org/xmlns/quakeml/1.2">
  <eventParameters publicID="quakeml:comcat.cr.usgs.gov/fdsnws/event/1/query?starttime=2013-10-01T06:00:00&amp;endtime=2013-10-02T08:00:00#requested=2013-11-24T04:09:48.000Z">
    <event catalog:datasource="ci" catalog:eventsource="ci" catalog:eventid="11372282" publicID="quakeml:comcat.cr.usgs.gov/fdsnws/event/1/query?eventid=ci11372282&amp;amp;format=quakeml">
      <origin catalog:datasource="ci" catalog:dataid="ci11372282" catalog:eventsource="ci" catalog:eventid="11372282" publicID="quakeml:earthquake.usgs.gov/product/ci/origin/ci11372282/1380736495867">
        <time>
          <value>2013-10-02T07:51:26.800Z</value>
        </time>
        <longitude>
          <value>-118.264</value>
        </longitude>
        <latitude>
          <value>34.0468</value>
        </latitude>
        <depth>
          <value>10700</value>
          <uncertainty>700</uncertainty>
        </depth>
        <originUncertainty>
          <horizontalUncertainty>400</horizontalUncertainty>
          <preferredDescription>horizontal uncertainty</preferredDescription>
        </originUncertainty>
        <quality>
          <usedPhaseCount>30</usedPhaseCount>
          <usedStationCount>22</usedStationCount>
          <standardError>0.13</standardError>
          <azimuthalGap>115.2</azimuthalGap>
          <minimumDistance>0.03593261</minimumDistance>
        </quality>
        <evaluationMode>manual</evaluationMode>
        <creationInfo>
          <creationTime>2013-10-02T17:54:55.867Z</creationTime>
          <version>4</version>
        </creationInfo>
      </origin>
      <magnitude catalog:datasource="ci" catalog:dataid="ci11372282" catalog:eventsource="ci" catalog:eventid="11372282" publicID="quakeml:earthquake.usgs.gov/product/ci/origin/ci11372282/1380736495867/magnitude">
        <mag>
          <value>1.5</value>
          <uncertainty>0.2</uncertainty>
        </mag>
        <type>Ml</type>
        <stationCount>34</stationCount>
        <originID>quakeml:earthquake.usgs.gov/product/ci/origin/ci11372282/1380736495867</originID>
        <evaluationMode>manual</evaluationMode>
        <creationInfo>
          <creationTime>2013-10-02T17:54:55.867Z</creationTime>
        </creationInfo>
      </magnitude>
      <preferredOriginID>quakeml:earthquake.usgs.gov/product/ci/origin/ci11372282/1380736495867</preferredOriginID>
      <preferredMagnitudeID>quakeml:earthquake.usgs.gov/product/ci/origin/ci11372282/1380736495867/magnitude</preferredMagnitudeID>
      <type>earthquake</type>
      <creationInfo>
        <agencyID>ci</agencyID>
        <creationTime>2013-10-02T17:56:06.008Z</creationTime>
        <version>4</version>
      </creationInfo>
    </event>
  </eventParameters>
</q:quakeml>')
                   the_data
           FROM DUAL)
                SELECT x1.*
                      ,t1.*
                      ,t2.*
                      ,t3.*
                      ,t4.*
                      ,t5.*
                      ,t6.*
                      ,t7.*
                      ,t8.*
                      ,t9.*
                      ,t10.*
                      ,t11.*
                      ,t12.*
                      ,t13.*
                      ,t14.*
                  FROM temp_table
                      ,XMLTABLE ('/quakeml/event_parameters/event'
                                 PASSING temp_table.the_data
                                 COLUMNS datasource VARCHAR2 (2) PATH '@datasource'
                                        ,eventsource VARCHAR2 (2) PATH '@eventsource'
                                        ,eventid NUMBER (14) PATH '@eventid'
                                        ,publicId VARCHAR2 (500) PATH '@publicID'
                                        ,origin XMLTYPE PATH '/origin'
                                        ,magnitude XMLTYPE PATH '/magnitude'
                                        ,creationInfo XMLTYPE PATH '/creationInfo'
                                        ,prefOriginID VARCHAR2 (500) PATH 'preferredOriginID'
                                        ,prefMagID VARCHAR2 (500) PATH 'preferredMagnitudeID'
                                        ,eventType VARCHAR2 (20) PATH 'type'
                                        ,creationInfo XMLTYPE PATH '/creationInfo') x1
                      ,XMLTABLE ('/time'
                                 PASSING x1.origin
                                 COLUMNS eq_time VARCHAR2 (100) PATH 'value') t2
                      ,XMLTABLE ('/longitude'
                                 PASSING x1.origin
                                 COLUMNS longitude NUMBER (8, 4) PATH 'value') t3
                      ,XMLTABLE ('/latitude'
                                 PASSING x1.origin
                                 COLUMNS latitude NUMBER (8, 4) PATH 'value') t4
                      ,XMLTABLE (
                          '/depth'
                          PASSING x1.origin
                          COLUMNS VALUE NUMBER (6) PATH 'value'
                                 ,uncertainty NUMBER (4) PATH 'uncertainty') t5
                      ,XMLTABLE (
                          '/originUncertainty'
                          PASSING x1.origin
                          COLUMNS horizontalUncertainty NUMBER (5)
                                     PATH 'horizontalUncertainty'
                                 ,preferredDescription VARCHAR2 (100)
                                     PATH 'preferredDescription') t6
                      ,XMLTABLE (
                          '/quality'
                          PASSING x1.origin
                          COLUMNS usedPhaseCount NUMBER (4) PATH 'usedPhaseCount'
                                 ,usedStationCount NUMBER (4) PATH 'usedStationCount'
                                 ,standardError NUMBER (4, 2) PATH 'standardError'
                                 ,azimuthalGap NUMBER (4, 3) PATH 'azimuthalGap'
                                 ,minimumDistance NUMBER (12, 8) PATH 'minimumDistance') t7
                      ,XMLTABLE ('/evaluationMode'
                                 PASSING x1.origin
                                 COLUMNS evaluationMode VARCHAR2 (20) PATH 'evaluationMode') t8
                      ,XMLTABLE (
                          '/mag'
                          PASSING x1.magnitude
                          COLUMNS magnitude NUMBER (4, 2) PATH 'value'
                                 ,uncertainty NUMBER (5, 3)) t9
                      ,XMLTABLE ('/type'
                                 PASSING x1.magnitude
                                 COLUMNS TYPE VARCHAR2 (4) PATH 'type') t10
                      ,XMLTABLE ('/stationCount'
                                 PASSING x1.magnitude
                                 COLUMNS stationCount NUMBER (3, 0) PATH 'stationCount') t11
                      ,XMLTABLE ('/evaluationMdoe'
                                 PASSING x1.magnitude
                                 COLUMNS ecaluationMode VARCHAR2 (20) PATH 'evaluationMode') t12
                      ,XMLTABLE (
                          '/creationInfo'
                          PASSING x1.magnitude
                          COLUMNS creationTime VARCHAR2 (100)
                                     PATH 'creationInfo/creationTime') t13
                      ,XMLTABLE (
                          '/creationInfo'
                          PASSING x1.creationInfo
                          COLUMNS agencyID VARCHAR2 (3) PATH 'creationInfo/agentID'
                                 ,creationTime VARCHAR2 (100)
                                     PATH 'creationInfo/creationTime'
                                 ,version NUMBER (4) PATH 'creationInfo/version') t14;
ASKER CERTIFIED SOLUTION
flow01
IT-specialist

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros