SQL Error 'ORA-00904' When Retrieving Data From an XML File Via XMLQuery

Question

I've been trying for days now to retrieve data from an XML file with a SELECT statement in SQL Developer but I constantly get the 'ORA-00904' when trying to execute the statement. So far, these are the steps I've been following:

1. Create the table and directory where I want the XML data to be stored in: CREATE TABLE PLAYER OF XMLTYPE; / CREATE DIRECTORY PLDIR AS 'C:\Users\marta\OneDrive\Escritorio\UOC\Sem3\ABD\PR2'; /
2. Insert into my PLAYER table said data: INSERT INTO PLAYER VALUES (XMLTYPE(bfilename('PLDIR', 'InfoPlayersWPT.xml'),nls_charset_id('AL32UTF8'))) / So far so good. The issue appears when I try to execute the SELECT statement

What could it be? I've changed the $Name parameter a million times as well as the Name field but nothing changes. The thing is that in the XML file, these are the fields:

--Update-- I've modified a little bit the structure and this is the new error I get: enter image description here I've reached a point where I don't get if there could be a problem with my database connection or if the variable are incorrect.

Any form of help would be much appreciated.

Answer 1

Your table doesn't have a name or id column. Your query is trying to get those, while also transforming the XML to an info node making the id a node rather than an attribute, but you still don't extract the values from that new XML. You don't need to though.

If the document only has one player (which seems unlikely with the outer node 'Players') then you can get the data with an XMLQuery call for each value:

select XMLQuery('/Players/Player/@id' passing p.object_value returning content) as id,
  XMLQuery('/Players/Player/Name/text()' passing p.object_value returning content) as name
from player p

ID	NAME
1	Francinsco Navarro Compán

But it's a bit simpler, particularly if you add more data items, to use a single XMLTable instead:

select x.id, x.name
from player p
cross apply XMLTable(
  '/Players/Player'
  passing p.object_value
  columns id number path '@id',
    name varchar2(30) path 'Name'
) x

ID	NAME
1	Francinsco Navarro Compán

fiddle

... which would also handle multiple players in each XML document.