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.

Alex Poole · Accepted Answer · 2014-01-26 08:59:09Z

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.

edited Jan 26, 2014 at 8:59

answered Jan 25, 2014 at 20:00

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

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

5 Comments

user2993831 Over a year ago

now gives me an error saying : ORA-04044: procedure, function, package, or type is not allowed here

Alex Poole Over a year ago

@user2993831 - hmm, I don't get an error on SQL Fiddle. But if I try it there with data then it doesn't return, not sure why at all - doesn't error though. Don't have direct access to a DB to test properly, sorry. Can you use extract for the phone number instead, though? Like this?

Alex Poole Over a year ago

@user2993831 - I don't see how you're getting that error from this code. SQL Fiddle doesn't like the XMLType-returning version, but it just never comes back it doesn't error; and the same code is fine in SQL*Plus/SQL Developer. I've added two alternatives though, both of which seem to work everywhere. Maybe you need to show exactly what you're running now and the full error stack?

user2993831 Over a year ago

i dont know what am doing wrong but it doesnt work for me at all. Am using oracle sql instant client and i tried it in oracle application express but no luck

Alex Poole Over a year ago

@user2993831 - we can't tell what you're doing wrong either without seeing it. Can you create an SQL Fiddle with exactly what you're running that produces that error? Re you sure it's this query that's erroring, not something you're running afterwards?

Collectives™ on Stack Overflow

XML/SQL Query Issue

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related