Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 532
  • Last Modified:

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

0
g_currier
Asked:
g_currier
  • 4
  • 3
1 Solution
 
flow01Commented:
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
0
 
g_currierAuthor 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...
0
 
flow01Commented:
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
flow01Commented:
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
0
 
g_currierAuthor 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
0
 
flow01Commented:
Try changing the path of PATH '@datasource' to include the namespace catalog
I don't know the exact syntax but would try
PATH 'catalog:@datasource'
or
PATH '@catalog:datasource'
If it works change  other catalog:items the same way.
0
 
g_currierAuthor 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
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now