XML/SQL Query Issue

Question

Question

Write an SQL command to create a table BookHistory. The table should have a title column to store a string and be a primary key, and a bookDetails column of type XMLType. Write a second SQL command to select the title and, from bookDetails, the XML node ’/author/telephoneNumber’ where the XML node ’/author/surname’ is ‘Chan’.

Answer/Attempt

CREATE TABLE BookHistory(
title varchar(20) PRIMARY KEY,
bookDetails XMLType);

SELECT title, XMLQuery(/author/telephoneNumber/text()
PASSING bookDetails RETURNING CONTENT)
FROM BookHistory
WHERE extractValue(bookDetails,'/author/surName') = 'Chan';

Error ORA-00936: missing expression

Can someone explain what am doing wrong. its my first time doing xml/sql.

Answer 1

The first argument to your XMLQuery(), the XQuery_string, needs to be quoted:

SELECT title, XMLQuery('/author/telephoneNumber/text()' as telephoneNumber
PASSING bookDetails RETURNING CONTENT)
FROM BookHistory
WHERE extractValue(bookDetails,'/author/surName') = 'Chan';

That gets rid of your error; and I think it gives the result you want... but as an XMLType still.

You could also do this:

SELECT title,
 extractValue(bookDetails, '/author/telephoneNumber') as telephoneNumber
FROM BookHistory
WHERE extractValue(bookDetails,'/author/surName') = 'Chan';

SQL Fiddle of the second version; the first version doesn't error, but doesn't return for some reason in that environment, but both run fine for me under 11.2.0.3 and give the same result; with sample data:

insert into bookhistory values ('Some title',
 XMLType('
<author>
 <surName>Chan</surName>
 <telephoneNumber>1234</telephoneNumber>
</author>
'));

insert into bookhistory values ('Another book',
 XMLType('
<author>
 <surName>Segal</surName>
 <telephoneNumber>5678</telephoneNumber>
</author>
'));

Both give:

TITLE                TELEPHONENUMBER
-------------------- --------------------
Some title           1234

But the first version returns an XMLType, which displays as above in SQL*Plus but not in SQLDeveloper; you should really get the actual text value:

SELECT title, XMLQuery('/author/telephoneNumber/text()'
PASSING bookDetails RETURNING CONTENT).getStringVal() as telephoneNumber
FROM BookHistory
WHERE extractValue(bookDetails,'/author/surName') = 'Chan';

TITLE                TELEPHONENUMBER    
-------------------- --------------------
Some title           1234                 

SQL Fiddle of both working versions.