CODEWITHSUNDEEP
sqloracle-databasexquery
Evgenij Reznik
Evgenij Reznik

Reputation: 18594

Using XQuery in Oracle

I'm trying to retrieve information from an Oracle database using XQuery. The table customer looks like

---id---last_name---
   1    Smith
   2    Jackson
   3    Peters

and I want all last names.
This is my code:

select last_name, XMLQuery(
'for $i in /customer
return $i/last_name'
PASSING BY VALUE customer
RETURNING CONTENT) last_name
FROM customer;

It says, there is an %s: invalid identifier in this line: PASSING BY VALUE customer

Upvotes: 2

Views: 3571

Answers (1)

Eric van der Vlist
Eric van der Vlist

Reputation: 857

If I understand correctly, you want to use XQuery to query purely relational data.

In that case, you can use ora:view and something such as:

SELECT XMLQuery('
    for $customer in ora:view("customer")/ROW
       return $customer/LAST_NAME
' RETURNING CONTENT) FROM DUAL;

should do the trick.

Upvotes: 5

Related Questions