Avatar of g_currier
g_currierFlag for Germany asked on

Oracle Parsing XML into table

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;

Open in new window

Oracle DatabaseProgramming

Avatar of undefined
Last Comment
g_currier

8/22/2022 - Mon
flow01

remove
                     ,t1.*
because it is never defined as (xml)table
and
remove one of the
                                        ,creationInfo XMLTYPE PATH '/creationInfo'
lines because that the same column definition

and  change event_parameters to  eventParameters to match the corresponding tag

After that i get an
ORA-00600: internal error code  but check if thats the same for you
ASKER
g_currier

well, it completes now, but I get no rows returned.

this is how I changed it (i removed what you asked and made some spelling corrections - was coding after 4:30 in the morning - never a good idea without coffee or redbull... )

SET DEFINE OFF
SET SERVEROUTPUT ON
SET LINESIZE 255
SET PAGESIZE 1000
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.datasource
                       --                                 ,x1.eventsource
                       --                                 ,x1.eventid
                       --                                 ,x1.publicId
                       --                                 ,x1.origin
                       --                                 ,x1.magnitude
                       --                                 ,x1.preferredOriginID
                       --                                 ,x1.preferredMagID
                       --                                 ,x1.TYPE
                       --                                 ,x1.creationInfo
                       x1.*
                      ,t2.*
                      ,t3.*
                      ,t4.*
                      ,t5.*
                      ,t6.*
                      ,t7.*
                      ,t8.*
                      ,t9.*
                      ,t10.*
                      ,t11.*
                      ,t12.*
                      ,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'
                                 ,preferredOriginID VARCHAR2 (500) PATH 'preferredOriginID'
                                 ,preferredMagID VARCHAR2 (500) PATH 'preferredMagnitudeID'
                                 ,TYPE 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 evaluationMode VARCHAR2 (20) PATH 'evaluationMode') t12
                      ,XMLTABLE (
                          '/creationInfo'
                          PASSING x1.creationInfo
                          COLUMNS agencyID VARCHAR2 (3) PATH 'creationInfo/agencyID'
                                 ,creationTime VARCHAR2 (100)
                                     PATH 'creationInfo/creationTime'
                                 ,version NUMBER (4) PATH 'creationInfo/version') t14;

Open in new window


It should return 1 row...what gives?  I have to admit that this is not my best topic, but it never hurts to keep on trying...
flow01

I assume there is a problem with the namespace q:
But I hit the internal error so i stopt searching. Now I know you can run it. I will try a stripped xml to get some result.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
flow01

Change the first XMLTABLE  to
XMLTABLE (XMLNamespaces(DEFAULT 'http://quakeml.org/xmlns/bed/1.2', 'http://quakeml.org/xmlns/quakeml/1.2' as "q", 'http://quakeml.org/xmlns/quakeml/1.2' as "c"),
 'q:quakeml/eventParameters/event'

It adds  namespaces  and modifies the path from 'quake .. to 'q:quake ..
I get results if thats the only XMLTABLE but keep the error if adding more.

Try to see what you get
ASKER
g_currier

OK, so this seems meaningful..i am getting results, still can't get all the tag attributes though...one per tag seems to work but the preceeding rest are not displaying:

the modified code:
SET DEFINE OFF
SET SERVEROUTPUT ON
SET LINESIZE 255
SET PAGESIZE 1000

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-11-01T06:00:00&amp;endtime=2013-11-23T06:00:00#requested=2013-11-23T20:09:21.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>
        <creationInfo>
            <creationTime>2013-11-23T20:09:44.000Z</creationTime>
        </creationInfo>
    </eventParameters>
</q:quakeml>')
                   the_data
           FROM DUAL)
                   SELECT                                        --event level
                         x1.datasource
                         ,x1.eventsource AS event_source
                         ,x1.eventid AS event_id
                         ,x1.publicId AS public_id
                         ,x1.origin
                         ,x1.magnitude
                         ,x1.preferredOriginID AS preferred_origin_id
                         ,x1.preferredMagID AS perferred_mag_id
                         ,x1.event_type
                         ,x1.creationInfo AS creation_info
                         --origin level I
                         ,x2.datasource AS origin_data_source
                         ,x2.dataid AS origin_data_id
                         ,x2.eventsource AS origin_event_source
                         ,x2.eventid AS origin_event_id
                         ,x2.publicId AS origin_public_id
                         -- origin level II
                         ,x3.eq_time
                         ,x3.longitude
                         ,x3.latitude
                         ,x3.DEPTH
                         ,x3.uncertainty
                         ,x3.horizontalUncertainty
                         ,x3.preferredDescription
                         ,x3.usedPhaseCount
                         ,x3.usedStationCount
                         ,x3.standardError
                         ,x3.azimuthalGap
                         ,x3.minimumDistance
                         ,x3.evaluationMode
                         ,x3.orig_creationTime
                         ,x3.orig_version
                         -- magnitude level
                         ,x4.mag_value
                         ,x4.mag_uncertainty
                         ,x4.magnitude_type
                         ,x4.magnitude_stationCount
                         ,x4.magnitude_originID
                         ,x4.magnitude_evaluationMode
                         ,x4.magnitude_creationTime
                         --  creation info level (optional)
                     --                         ,x5.agencyID
                     --                         ,x5.creationTime
                     --                         ,x5.version
                     FROM temp_table
                         ,XMLTABLE (
                             xmlnamespaces (DEFAULT 'http://quakeml.org/xmlns/bed/1.2'
                                           ,'http://quakeml.org/xmlns/quakeml/1.2' AS "q"
                                           ,'http://anss.org/xmlns/catalog/0.1' AS "catalog")
                            ,'q:quakeml/eventParameters/event'
                             PASSING temp_table.the_data
                             COLUMNS datasource VARCHAR2 (200) PATH '@datasource'
                                    ,eventsource VARCHAR2 (200) PATH '@eventsource'
                                    ,eventid VARCHAR2 (15) PATH '@eventid'
                                    ,publicId VARCHAR2 (500) PATH '@publicID'
                                    ,origin XMLTYPE PATH 'origin'
                                    ,magnitude XMLTYPE PATH 'magnitude'
                                    ,preferredOriginID VARCHAR2 (500) PATH 'preferredOriginID'
                                    ,preferredMagID VARCHAR2 (500) PATH 'preferredMagnitudeID'
                                    ,event_type VARCHAR2 (20) PATH 'type'
                                    ,creationInfo XMLTYPE PATH 'creationInfo') x1
                         ,XMLTABLE (
                             xmlnamespaces (DEFAULT 'http://quakeml.org/xmlns/bed/1.2'
                                           ,'http://quakeml.org/xmlns/quakeml/1.2' AS "q"
                                           ,'http://anss.org/xmlns/catalog/0.1' AS "catalog")
                            ,'q:quakeml/eventParameters/event/origin'
                             PASSING temp_table.the_data
                             COLUMNS datasource VARCHAR2 (200) PATH '@datasource'
                                    ,dataid VARCHAR2 (200) PATH '@dataid'
                                    ,eventsource VARCHAR2 (200) PATH '@eventsource'
                                    ,eventid VARCHAR2 (20) PATH '@eventid'
                                    ,publicId VARCHAR2 (500) PATH '@publicID') x2
                         ,XMLTABLE (
                             xmlnamespaces (DEFAULT 'http://quakeml.org/xmlns/bed/1.2'
                                           ,'http://quakeml.org/xmlns/quakeml/1.2' AS "q"
                                           ,'http://anss.org/xmlns/catalog/0.1' AS "catalog")
                            ,'/origin'
                             PASSING x1.origin
                             COLUMNS eq_time VARCHAR2 (100) PATH 'time/value'
                                    ,longitude VARCHAR2 (20) PATH 'longitude/value'
                                    ,latitude VARCHAR2 (20) PATH 'latitude/value'
                                    ,DEPTH VARCHAR2 (20) PATH 'depth/value'
                                    ,uncertainty VARCHAR2 (20) PATH 'depth/uncertainty'
                                    ,horizontalUncertainty VARCHAR2 (20)
                                        PATH 'originUncertainty/horizontalUncertainty'
                                    ,preferredDescription VARCHAR2 (200)
                                        PATH 'originUncertainty/preferredDescription'
                                    ,usedPhaseCount VARCHAR2 (20) PATH 'quality/usedPhaseCount'
                                    ,usedStationCount VARCHAR2 (20)
                                        PATH 'quality/usedStationCount'
                                    ,standardError VARCHAR2 (20) PATH 'quality/standardError'
                                    ,azimuthalGap VARCHAR2 (20) PATH 'quality/azimuthalGap'
                                    ,minimumDistance VARCHAR2 (20)
                                        PATH 'quality/minimumDistance'
                                    ,evaluationMode VARCHAR2 (200) PATH 'evaluationMode'
                                    ,orig_creationTime VARCHAR2 (200)
                                        PATH 'creationInfo/creationTime'
                                    ,orig_version VARCHAR2 (20) PATH 'creationInfo/version') x3
                         ,XMLTABLE (
                             xmlnamespaces (DEFAULT 'http://quakeml.org/xmlns/bed/1.2'
                                           ,'http://quakeml.org/xmlns/quakeml/1.2' AS "q"
                                           ,'http://anss.org/xmlns/catalog/0.1' AS "catalog")
                            ,'/magnitude'
                             PASSING x1.magnitude
                             COLUMNS mag_value VARCHAR2 (20) PATH 'mag/value'
                                    ,mag_uncertainty VARCHAR2 (20) PATH 'mag/uncertainty'
                                    ,magnitude_type VARCHAR (200) PATH 'type'
                                    ,magnitude_stationCount VARCHAR2 (20) PATH 'stationCount'
                                    ,magnitude_originID VARCHAR2 (200) PATH 'originID'
                                    ,magnitude_evaluationMode VARCHAR2 (200)
                                        PATH 'evaluationMode'
                                    ,magnitude_creationTime VARCHAR2 (200)
                                        PATH 'creationInfo/creationTime') x4
                    --                         ,XMLTABLE (
                    --                             xmlnamespaces (DEFAULT 'http://quakeml.org/xmlns/bed/1.2'
                    --                                           ,'http://quakeml.org/xmlns/quakeml/1.2' AS "q"
                    --                                           ,'http://anss.org/xmlns/catalog/0.1' AS "catalog")
                    --                            ,'/creationInfo'
                    --                             PASSING x1.creationInfo
                    --                             COLUMNS agencyID VARCHAR2 (200) PATH 'agencyID'
                    --                                    ,creationTime VARCHAR2 (200) PATH 'creationTime'
                    --                                    ,version varchar2(20) PATH 'version') x5
                    WHERE 1 = 1;

Open in new window


the results (partial) are in the attachment.  You can see that the blanks are the fields used to store attribute data...I'm still going to work on it though.  can you help long enough to see this through?

Thanksresult of query
ASKER CERTIFIED SOLUTION
flow01

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
g_currier

That did it.  I had tried  PATH 'event/catalog:datasource' before and that didn't work so I was doing some hunting around.

Your suggestion with PATH '@catalog:datasource' did the trick!

Now that I see it, it makes a weird kind of sense...

Thanks
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.