We help IT Professionals succeed at work.

Oracle Parsing XML into table

g_currier
g_currier asked
on
594 Views
Last Modified: 2013-11-24
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

Comment
Watch Question

flow01IT-specialist
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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...
flow01IT-specialist
CERTIFIED EXPERT

Commented:
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.
flow01IT-specialist
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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
IT-specialist
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.