Solved

Oracle Parsing XML into table

Posted on 2013-11-23
7
519 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

0
Comment
Question by:g_currier
  • 4
  • 3
7 Comments
 
LVL 20

Expert Comment

by:flow01
Comment Utility
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
 

Author Comment

by:g_currier
Comment Utility
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
 
LVL 20

Expert Comment

by:flow01
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 20

Expert Comment

by:flow01
Comment Utility
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
 

Author Comment

by:g_currier
Comment Utility
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
 
LVL 20

Accepted Solution

by:
flow01 earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:g_currier
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This is about my first experience with programming Arduino.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now