CODEWITHSUNDEEP
sqloracle-databaseoracle11gr2
LSdev
LSdev

Reputation: 153

how to generate a table from an XML file stored in BLOB format?

I need to generate a table populated from an XML file (stored in BLOB format) using a SQL query (Oracle 11g R2). The table should contains one row per student.

When I try to use the following syntax :

select x.*
from XMLTABLE('/XML/highschool/class/student' passing (SELECT XML_CONTENT
   FROM T_FILES
   WHERE ID_FILE = 1)
   columns seq for ordinality,
   STUDENT_NAME Varchar2(500) PATH 'cd[@name=''STUDENT_NAME'']@data'
)
as x;

I get the error

19109. 00000 -  "RETURNING keyword expected"
*Cause:    The keyword RETURNING was missing.
*Action:   Specify the RETURNING keyword.

I tried to use dbms_xmlgen.getxmltype and some other solutions without success.

Upvotes: 0

Views: 1183

Answers (1)

DazzaL
DazzaL

Reputation: 21993

your XPATH is corrupt.

use:

PATH 'cd[@name="STUDENT_NAME"]/@data'

eg:

SQL> create table T_FILES
  2  (XML_CONTENT xmltype, ID_FILE number);

Table created.

SQL> insert into t_files values (
  2  xmltype('<XML>
  3    <highschool>
  4      <class>
  5        <user>
  6          <cd name="STUDENT_NAME" data="foo"/>
  7        </user>
  8        <user>
  9          <cd name="STUDENT_NAME" data="foo2"/>
 10        </user>
 11      </class>
 12    </highschool>
 13  </XML>'), 1);

1 row created.

SQL> commit;

Commit complete.

SQL> select /*+ cursor_sharing_exact */ a.*
 2  from XMLTABLE('/XML/highschool/class/user' PASSING  (SELECT XML_CONTENT
 3     FROM T_FILES
 4     WHERE ID_FILE = 1)
 5               columns
 6               seq for ordinality,
 7               STUDENT_NAME Varchar2(500) PATH 'cd[@name="STUDENT_NAME"]/@data'
 8       )  a;

      SEQ STUDENT_NAME
--------- --------------------
        1 foo
        2 foo2

vs

SQL>  select /*+ cursor_sharing_exact */ a.*
  2  from XMLTABLE('/XML/highschool/class/user' PASSING  (SELECT XML_CONTENT
  3     FROM T_FILES
  4     WHERE ID_FILE = 1)
  5               columns
  6               seq for ordinality,
  7               STUDENT_NAME Varchar2(500) PATH 'cd[@name=''STUDENT_NAME'']@data'
  8       )  a;
from XMLTABLE('/XML/highschool/class/user' PASSING  (SELECT XML_CONTENT

though the syntax you're using is ONLY valid if there is one row in your base table for that ID_FILE. If it is not, then you have to put the table outside of the xmltable definition like:

select /*+ cursor_sharing_exact */ a.*
from T_FILES t,
     XMLTABLE('/XML/highschool/class/user' PASSING  t.XML_CONTENT
             columns
             seq for ordinality,
             STUDENT_NAME Varchar2(500) PATH 'cd[@name="STUDENT_NAME"]/@data'
     )  a
WHERE t.ID_FILE = 1;

if you've put the XML in BLOB and not stored as XMLTYPE from a blob, then convert in the select with:

select /*+ cursor_sharing_exact */ a.*
from XMLTABLE('/XML/highschool/class/user' PASSING  
             (SELECT xmltype.createxml(XML_CONTENT, NLS_CHARSET_ID('UTF8'), null)
                FROM T_FILES
               WHERE ID_FILE = 1)
             columns
             STUDENT_NAME Varchar2(500) PATH 'cd[@name="STUDENT_NAME"]/@data'
     )  a;

ie. xmltype.createxml(XML_CONTENT, NLS_CHARSET_ID('UTF8'), null). change the character set as appropriate.

Upvotes: 1

Related Questions