What is XMLTABLE

Question

• What is XMLTABLE.

• Let me know the syntax for creating XMLTABLE

• Sample query for retrieving records from XMLTABLE.

• Before creating XMLTABLE, database level prerequisites are required.

Answer 1

The XMLTABLE function is used to translate an xml object into separate fields. But you probably want to build a table with xml content that is different.

You can create a table with an extra column containing xml content

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

Then you use the xml content inside your requests.

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

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

Done.

OK by replying to the second comment:

, XMLTABLE, , . URL-, . xml:

 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

:

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

Answer 2

XMLTABLE , , .