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
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&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;format=quakeml">
<origin catalog:datasource="ci" catalog:dataid="ci11372282" catalog:eventsource="ci" catalog:eventid="11372282" publicID="quakeml:earthquake.usgs.gov/product/ci/origin/ci11372282/1380736495867">
<time>
<value>2013-10-02T07:51:26.800Z</value>
</time>
<longitude>
<value>-118.264</value>
</longitude>
<latitude>
<value>34.0468</value>
</latitude>
<depth>
<value>10700</value>
<uncertainty>700</uncertainty>
</depth>
<originUncertainty>
<horizontalUncertainty>400</horizontalUncertainty>
<preferredDescription>horizontal uncertainty</preferredDescription>
</originUncertainty>
<quality>
<usedPhaseCount>30</usedPhaseCount>
<usedStationCount>22</usedStationCount>
<standardError>0.13</standardError>
<azimuthalGap>115.2</azimuthalGap>
<minimumDistance>0.03593261</minimumDistance>
</quality>
<evaluationMode>manual</evaluationMode>
<creationInfo>
<creationTime>2013-10-02T17:54:55.867Z</creationTime>
<version>4</version>
</creationInfo>
</origin>
<magnitude catalog:datasource="ci" catalog:dataid="ci11372282" catalog:eventsource="ci" catalog:eventid="11372282" publicID="quakeml:earthquake.usgs.gov/product/ci/origin/ci11372282/1380736495867/magnitude">
<mag>
<value>1.5</value>
<uncertainty>0.2</uncertainty>
</mag>
<type>Ml</type>
<stationCount>34</stationCount>
<originID>quakeml:earthquake.usgs.gov/product/ci/origin/ci11372282/1380736495867</originID>
<evaluationMode>manual</evaluationMode>
<creationInfo>
<creationTime>2013-10-02T17:54:55.867Z</creationTime>
</creationInfo>
</magnitude>
<preferredOriginID>quakeml:earthquake.usgs.gov/product/ci/origin/ci11372282/1380736495867</preferredOriginID>
<preferredMagnitudeID>quakeml:earthquake.usgs.gov/product/ci/origin/ci11372282/1380736495867/magnitude</preferredMagnitudeID>
<type>earthquake</type>
<creationInfo>
<agencyID>ci</agencyID>
<creationTime>2013-10-02T17:56:06.008Z</creationTime>
<version>4</version>
</creationInfo>
</event>
</eventParameters>
</q:quakeml>')
the_data
FROM DUAL)
SELECT x1.*
,t1.*
,t2.*
,t3.*
,t4.*
,t5.*
,t6.*
,t7.*
,t8.*
,t9.*
,t10.*
,t11.*
,t12.*
,t13.*
,t14.*
FROM temp_table
,XMLTABLE ('/quakeml/event_parameters/event'
PASSING temp_table.the_data
COLUMNS datasource VARCHAR2 (2) PATH '@datasource'
,eventsource VARCHAR2 (2) PATH '@eventsource'
,eventid NUMBER (14) PATH '@eventid'
,publicId VARCHAR2 (500) PATH '@publicID'
,origin XMLTYPE PATH '/origin'
,magnitude XMLTYPE PATH '/magnitude'
,creationInfo XMLTYPE PATH '/creationInfo'
,prefOriginID VARCHAR2 (500) PATH 'preferredOriginID'
,prefMagID VARCHAR2 (500) PATH 'preferredMagnitudeID'
,eventType VARCHAR2 (20) PATH 'type'
,creationInfo XMLTYPE PATH '/creationInfo') x1
,XMLTABLE ('/time'
PASSING x1.origin
COLUMNS eq_time VARCHAR2 (100) PATH 'value') t2
,XMLTABLE ('/longitude'
PASSING x1.origin
COLUMNS longitude NUMBER (8, 4) PATH 'value') t3
,XMLTABLE ('/latitude'
PASSING x1.origin
COLUMNS latitude NUMBER (8, 4) PATH 'value') t4
,XMLTABLE (
'/depth'
PASSING x1.origin
COLUMNS VALUE NUMBER (6) PATH 'value'
,uncertainty NUMBER (4) PATH 'uncertainty') t5
,XMLTABLE (
'/originUncertainty'
PASSING x1.origin
COLUMNS horizontalUncertainty NUMBER (5)
PATH 'horizontalUncertainty'
,preferredDescription VARCHAR2 (100)
PATH 'preferredDescription') t6
,XMLTABLE (
'/quality'
PASSING x1.origin
COLUMNS usedPhaseCount NUMBER (4) PATH 'usedPhaseCount'
,usedStationCount NUMBER (4) PATH 'usedStationCount'
,standardError NUMBER (4, 2) PATH 'standardError'
,azimuthalGap NUMBER (4, 3) PATH 'azimuthalGap'
,minimumDistance NUMBER (12, 8) PATH 'minimumDistance') t7
,XMLTABLE ('/evaluationMode'
PASSING x1.origin
COLUMNS evaluationMode VARCHAR2 (20) PATH 'evaluationMode') t8
,XMLTABLE (
'/mag'
PASSING x1.magnitude
COLUMNS magnitude NUMBER (4, 2) PATH 'value'
,uncertainty NUMBER (5, 3)) t9
,XMLTABLE ('/type'
PASSING x1.magnitude
COLUMNS TYPE VARCHAR2 (4) PATH 'type') t10
,XMLTABLE ('/stationCount'
PASSING x1.magnitude
COLUMNS stationCount NUMBER (3, 0) PATH 'stationCount') t11
,XMLTABLE ('/evaluationMdoe'
PASSING x1.magnitude
COLUMNS ecaluationMode VARCHAR2 (20) PATH 'evaluationMode') t12
,XMLTABLE (
'/creationInfo'
PASSING x1.magnitude
COLUMNS creationTime VARCHAR2 (100)
PATH 'creationInfo/creationTime') t13
,XMLTABLE (
'/creationInfo'
PASSING x1.creationInfo
COLUMNS agencyID VARCHAR2 (3) PATH 'creationInfo/agentID'
,creationTime VARCHAR2 (100)
PATH 'creationInfo/creationTime'
,version NUMBER (4) PATH 'creationInfo/version') t14;ASKER
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... )
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...
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&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;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;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...
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.
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.
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
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
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
It adds namespaces and modifies the path from 'quake .. to 'q:quake ..
I get results if thats the only XMLTABLE but keep the error if adding more.
Try to see what you get
ASKER
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:
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?
Thanks
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&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;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;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?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That did it. I had tried PATH 'event/catalog:datasource'
Your suggestion with PATH '@catalog:datasource' did the trick!
Now that I see it, it makes a weird kind of sense...
Thanks
Your suggestion with PATH '@catalog:datasource' did the trick!
Now that I see it, it makes a weird kind of sense...
Thanks
,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