What is an XMLTABLE

Question

1. What is an XMLTABLE.

2. Do let me know the syntax of creating an XMLTABLE

3. Sample query to fetch records from XMLTABLE.

4. Are there any database level pre-requisites required before creating an XMLTABLE.

Answer 1

XMLTABLE Might be what you'Re looking for.

Answer 2

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

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

Then you use the xml content inside your queries.

INSERT INTO mytable VALUES (1,xmltype('<myxml id="D45"/>'));

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

Finished.

OK responding to the second comment:

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:

 1  SELECT seq
 2        , id
 3        , content
 4  FROM XMLTABLE('/xml/myrec'
 5        PASSING XMLType('<xml>'
 6                      ||'<myrec id="D12"><content>hello1</content></myrec>'
 7                      ||'<myrec id="D13"><content>hello2</content></myrec>
 8                      ||</xml>')
 9        COLUMNS   seq FOR ORDINALITY
10                , id VARCHAR2(100) PATH '@id'
11                , content VARCHAR2(100) PATH 'content'
12*      ) AS my_table

Output is:

 SEQ ID    CONTENT
---- ----- --------------------
   1 D12   hello1
   2 D13   hello2