Nov 11, 2011

Function XMLTable in Oracle 10g

The function XMLTABLE is used to translate an xml object into separated fields. But you
probably want to construct a table with xml content, which is different.
You can create a table with an extra column that contains xml content

1. CREATE TABLE mytable (my_id NUMBER PRIMARY KEY, my_xml XMLType);

2.
INSERT INTO mytable VALUES (1,xmltype(''));

3. SELECT my_id
      ,my_xml.extract('/myxml@id').getstringval()
from mytable
Finished.
---error


So you actually do want to use the XMLTABLE function, Your error indicates that you are not
getting the file at all. So you need to craft your url to load it correctly. A test case I
constructed with embedded xml is:
  SELECT seq
       , id
        , content
 FROM XMLTABLE('/xml/myrec'
        PASSING XMLType('

Output is:
 SEQ ID    CONTENT
---- ----- --------------------
   1 D12   hello1
   2 D13   hello2

SELECT person_id
FROM person_data a, XMLtable('for $root in $date
return $root/pdrecord/PDDOB/text()'
passing a.person_data as "date")t
/

 SELECT * FROM XMLTABLE(
     'ora:view("PERSON_DATA")/ROW/PERSON_ID,/ROW/PERSON_DATA')

SELECT XMLQuery(
     'ora:view("DEPARTMENTS")'
  RETURNING CONTENT) AS EMP_DEPARTMENTS
 FROM dual
/



No comments:

OraApps Search

Custom Search

Search This Blog