Forum Stats

  • 3,854,528 Users
  • 2,264,379 Discussions
  • 7,905,718 Comments

Discussions

xpath query doesn't return right data

user4109719
user4109719 Member Posts: 34 Red Ribbon
edited Jun 29, 2015 5:44AM in XQuery

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.

Tagged:

Answers

This discussion has been closed.