I am trying to extract information from an XMLTYPE column with this query:
select
year,month,day,rev,ms_id
FROM archive_xml x,
XMLTable('/article_set/article/history/ms_id' passing x.ax_xml_data
columns
year varchar2(2000) path 'submitted_date/year',
month varchar2(2000) path 'submitted_date/month',
day varchar2(2000) path 'submitted_date/day',
rev varchar2(2) path 'rev_id',
ms_id varchar2(40) path '.'
) t
The path /article_set/article/history/ms_id is in more than one place in the file. When I used /article_set/article/history/ it resulted in the ORA-19279 error.
It is giving results like this:
YEAR | MONTH | DAY | REV | MS_ID |
2006 | 11 | 12 | 0 | 020061112220059(GMT-05:00) Eastern Time |
2006 | 11 | 13 | 0 | 020061113105924(GMT-05:00) Eastern Time |
2006 | 11 | 13 | 0 | 020061113113055(GMT-05:00) Eastern Time |
The first 4 are correct but I have no idea how it's getting the MS_ID. There is a <time_zone> tag at the same level as year, month etc. That may be what the (GMT-05:00) Eastern Time came from but I have no idea why. I don't know where the series of digits is coming from.
Thanks.