XMLQuery in Oracle to query a XML

Question

I am new to XLQuery and was trying out a basic command-

CREATE TABLE person_data (
person_id   NUMBER(3),
person_data XMLTYPE
);

INSERT INTO person_data
(person_id, person_data)
VALUES
(1, XMLTYPE('
 <PDRecord>
   <PDName>Daniel Morgan</PDName>
   <PDDOB>12/1/1951</PDDOB>
   <PDEmail>[email protected]</PDEmail>
 </PDRecord>')
);

INSERT INTO person_data
(person_id, person_data)
VALUES
(2, XMLTYPE('
 <PDRecord>
   <PDName>Jack Cline</PDName>
   <PDDOB>5/17/1949</PDDOB>
   <PDEmail>[email protected]</PDEmail>
 </PDRecord>')
);

INSERT INTO person_data
(person_id, person_data)
VALUES
(3, XMLTYPE('
 <PDRecord>
   <PDName>Caleb Small</PDName>
   <PDDOB>1/1/1960</PDDOB>
   <PDEmail>[email protected]</PDEmail>
 </PDRecord>')
);

What I tried...

select person_id,
       XMLQuery('for $i  in /PDRecord where $i/PDName = "Jack Cline" order by $i/PDName return $i/PDName'
       passing by value t.person_data
       returning CONTENT ) XMLData
from person_data t;

But it's failing with error

ORA-30625: method dispatch on NULL SELF argument is disallowed

Not able to understand why? Also if you know some good tutorial for XMlQuery please point me to that.
Note: I have already gone through documentation.

Sorry, I cannot reproduce this with the data and query you've given. I get the expected results. (I'm running this on Oracle XE 18c.) — Luke Woodward
– Luke Woodward, Commented Oct 19, 2019 at 12:22

Luke Woodward · Accepted Answer · 2019-10-20 19:23:23Z

1

I suspect that what you are hitting is a bug in LiveSQL.

If we take your query and wrap your call to XMLQuery in an NVL which substitutes a dummy document in the null case, we get better output. This query returns successfully, substituting in the dummy document in the case where the condition doesn't match:

select person_id,
       NVL(XMLQuery('for $i in /PDRecord where $i/PDName = "Jack Cline" order by $i/PDName return $i/PDName'
       passing by value t.person_data
       RETURNING CONTENT), XMLTYPE('<empty/>')) XMLData
from person_data t;

There seem to be other bugs in LiveSQL as well: if you take the XMLData column alias away from the query above, the output gets corrupted.

answered Oct 20, 2019 at 19:23

Luke Woodward

65.3k16 gold badges94 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

XMLQuery in Oracle to query a XML

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related