Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

xpath query doesn't return right data

user4109719Jun 26 2015 — edited Jun 29 2015

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:

  

YEARMONTHDAYREVMS_ID
200611120020061112220059(GMT-05:00) Eastern Time
200611130020061113105924(GMT-05:00) Eastern Time
200611130020061113113055(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.

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 27 2015
Added on Jun 26 2015
1 comment
703 views