Select XML element values and display multiple rows Oracle 11g

Question

I have the following table in Oracle 11g:

 CREATE TABLE jason_xml( id NUMBER(5) PRIMARY KEY, xml_content XMLTYPE )tablespace WD_T

Within the xml_content column I have an XML document:

 <results> <return> <actualtime>0.0</actualtime> <billingamount>0.0</billingamount> <buildlisttext> <buildnumber>0</buildnumber> <completiondate>2007-04-10T12:36:00+02:00</completiondate> <componentid>0</componentid> <containsrecipients>false</containsrecipients> <containsrecipientshasbeenset>true</containsrecipientshasbeenset> <costamount>0.0</costamount> <createdate>2006-11-20T17:10:02+01:00</createdate> <createdbysystemuserid>89198</createdbysystemuserid> <currentownersystemuserid>12122</currentownersystemuserid> <currentownerusergroupid>0</currentownerusergroupid> <customerid>95</customerid> <description>From: Ricky Bolton</description> </buildlisttext> </return> <return> <actualtime>0.0</actualtime> <billingamount>0.0</billingamount> <buildlisttext> <buildnumber>0</buildnumber> <completiondate>2007-04-10T12:36:00+02:00</completiondate> <componentid>0</componentid> <containsrecipients>false</containsrecipients> <containsrecipientshasbeenset>true</containsrecipientshasbeenset> <costamount>0.0</costamount> <createdate>2006-11-20T17:10:02+01:00</createdate> <createdbysystemuserid>89198</createdbysystemuserid> <currentownersystemuserid>12122</currentownersystemuserid> <currentownerusergroupid>0</currentownerusergroupid> <customerid>95</customerid> <description>From: Derek Trotter</description> </buildlisttext> </return> </results>

I'm trying to query this document from within my jason_xml table column and then have the results presented as:

 |billingamount|Description| |0.0 |From: Ricky Bolton| |0.0 |From: Derek Trotter|

I've been pointed in direction of the Oracle API but I'm not very good at reading API's and find this one to be extremely poorly written. I've tried a number of the operators defined on this page but have had no joy:

http://docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb04cre.htm#BABDGFFH

I've gotten as far as this but keep getting 'invalid identifier' in PL/SQL developer. I know I'm probably going completely wrong with this so does anyone have any pointers / a solution?

 SELECT extractValue(OBJECT_VALUE, 'results/return/buildlisttext/description') "DESCRIPTION" FROM jason_xml x WHERE xmlexists('results/return/buildlisttext/description' PASSING OBJECT_VALUE);

I'm using a MySQL user with PHP and could easily do this with that combination of technologies but unfortunately I have to use Oracle 11g at work.

Any help would be greatly appreciated.

jezzipin · Accepted Answer · 2013-08-07 08:58:50Z

Turns out that this is the correct response for this situation:

 SELECT xtab.billingamount, xtab.description FROM jason_xml jx, xmltable('/results/return' PASSING jx.xml_content COLUMNS billingamount varchar2(4000) path '//billingAmount', description clob path '//description' )xtab;

OldProgrammer · Accepted Answer · 2013-08-07 01:11:34Z

Give this a try. There may be a simpler method.

SELECT EXTRACTVALUE (t.COLUMN_VALUE, 'return/billingamount') Billing_Amount, EXTRACTVALUE (t.COLUMN_VALUE, 'return/buildlisttext/description') Description FROM jason_xml, XMLTABLE ('for $i in /results/return return $i' PASSING jason_xml.xml_content) t

Here is the SQL Fiddle example

This does not include the constraint you show, but you can add that easily to the XQuery expression.

Did you look at the SQLFiddle example? I ran it with the sample data you provided.
I did. The code I have posted below is the only code I've managed to get working correctly.

Collectives™ on Stack Overflow

Select XML element values and display multiple rows Oracle 11g

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related