XML file insert into oracle Table and Query

XML file insert into oracle Table and Query
--------------------------------------------------------
1. create a Object directory xmldir
create or replace directory xmldir as ':E/file.xml';

2. create xmltype table
create table xmlfile_tbl of xmlTYPE;

3. Insert xmlfile to the table
insert into xmlfile_tblvalues(XMLType(bfilename('XMLDIR', 'CD1.XML'),nls_charset_id('AL16UTF8')));

Note :- Directory name Must be Block letter.

4. Then I try to Query it:

SQL> select object_value from xmlfile_tbl;

Return complete file.xml

5.example - 1

SELECT EXTRACTVALUE(VALUE(t), 'x/y') value_of_y, EXTRACTVALUE(VALUE(t), 'x/z') value_of_z FROM TABLE(XMLSEQUENCE(XMLTYPE.CreateXML('valuevalue2'))) t;

6. example - 2

BEGIN FOR i IN ( SELECT EXTRACTVALUE(VALUE(t), '/B/ID') b_id, EXTRACTVALUE(VALUE(tc),'/D/ID') d_id FROM TABLE(XMLSEQUENCE(x.EXTRACT('A/B'))) t, TABLE(XMLSEQUENCE(EXTRACT(VALUE(t), 'B/C/D'))) tc ) LOOP dbms_output.put_line('B id : ' i.b_id ' : D id : ' i.d_id); END LOOP; END;

7. example - 3

DECLARE x XMLTYPE := XMLTYPE.CREATEXML(' 1 2 3 4 '); x1 XMLTYPE; BEGIN FOR i IN ( SELECT EXTRACTVALUE(VALUE(t), '/B/ID') b_id, EXTRACT(VALUE(t), '/B/C/D') d_xml, rownum rn FROM TABLE(XMLSEQUENCE(x.EXTRACT('A/B'))) t ) LOOP FOR j IN ( SELECT EXTRACTVALUE(VALUE(t), '/D/ID') d_id FROM TABLE(XMLSEQUENCE(i.d_xml)) t ) LOOP dbms_output.put_line('b row : ' i.rn ' : id : ' i.b_id ' : d id : ' j.d_id); END LOOP; END LOOP; END;

-----------------------------------------------------------
sample XML file : BookStore.xml


- http://www.books.org" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.books.org BookStore.xsd">- Paul McCartney 1998 1-56592-235-2 McMillin Publishing - Richard Bach 1977 0-440-34319-4 Dell Publishing Co. - J. Krishnamurti 1954 0-06-064831-7 Harper & Row