This discussion is archived
14 Replies Latest reply: Apr 5, 2011 8:41 AM by 823127 RSS

insert .xml file into xmltype table?

823127 Newbie
Currently Being Moderated
Hi experts,

I am in I am in Oracle Enterprise Manager 11g 11.2.0.1.0.
SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 22 11:40:23 2011

Thanks!

My one .xml file is listed below:
<?xml version="1.0" encoding="UTF-8"?><?xml-stylesheet href="http://www.accessdata.fda.gov/spl/stylesheet/spl.xsl" type="text/xsl"?>
<document xmlns="urn:hl7-org:v3" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:hl7-org:v3 http://localhost:8080/home/DEV/xsd/spl.xsd">
</value>
</observationMedia>
</component>
</section>
</component>
</structuredBody>
</component>
</document>

Edited by: Cow on Apr 4, 2011 12:59 PM

Edited by: Cow on Apr 4, 2011 2:06 PM
  • 1. Re: insert .xml file into xmltype table?
    odie_63 Guru
    Currently Being Moderated
    Hi,
    Is there any way to manually create an xmltype table without involve any .xsd
    and insert these xml file’s into this xmltype table? or is ther any other way I can get the data
    from xml file?
    Use BINARY XML storage and query all required data with XMLTable, e.g. :
    CREATE TABLE documents OF XMLTYPE
    XMLTYPE STORE AS SECUREFILE BINARY XML
    ;
    
    INSERT INTO documents VALUES( 
     xmltype(bfilename('XML_DIR','hl7_doc.xml'),nls_charset_id('AL32UTF8'))
    );
    
    SELECT x1.root_id,
           x1.title,
           x2.*
    FROM documents t
       , XMLTable(
           XMLNamespaces(default 'urn:hl7-org:v3')
         , '/document'
           passing t.object_value
           columns root_id    varchar2(50) path 'id/@root'
                 , title      varchar2(80) path 'title'
                 , components xmltype      path 'component'
         ) x1
       , XMLTable(
           XMLNamespaces(default 'urn:hl7-org:v3')
         , '/component/structuredBody/component'
           passing x1.components
           columns section_root_id   varchar2(50) path 'section/id/@root'
                 , code_display_name varchar2(80) path 'section/code/@displayName'
         ) x2
    ;
  • 2. Re: insert .xml file into xmltype table?
    823127 Newbie
    Currently Being Moderated
    I got following error.
    is this meaning I have to login as sys user to do all these?


    SQL> desc document2;
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    TABLE of SYS.XMLTYPE STORAGE BINARY


    SQL> INSERT INTO document2
    2 VALUES(xmltype(bfilename('XML_DIR',xtz.xml'), nls_charset_id('AL32UTF8')));
    VALUES(xmltype(bfilename('XML_DIR',zfx.xml'), nls_charset_id('AL32UTF8')))
    *
    ERROR at line 2:
    ORA-22288: file or LOB operation FILEOPEN failed
    The system cannot find the file specified.
    ORA-06512: at "SYS.XMLTYPE", line 296
    ORA-06512: at line 1

    Edited by: Cow on Apr 2, 2011 5:49 PM

    Edited by: Cow on Apr 4, 2011 5:11 AM

    Edited by: Cow on Apr 4, 2011 5:13 AM
  • 3. Re: insert .xml file into xmltype table?
    odie_63 Guru
    Currently Being Moderated
    You've created directory "XMLDIR", but you're trying to use "XML_DIR"... Is that a typo?

    Also, directories must reside on the db server, or a network location accessible by the db.
  • 4. Re: insert .xml file into xmltype table?
    823127 Newbie
    Currently Being Moderated
    Thanks a million!
    I have corrected my typo and it inserted successfully.

    SQL> INSERT INTO document2
    2 VALUES(xmltype(bfilename('XMLDIR', '7d2.xml'), nls_charset_id('AL32UTF8')));

    1 row created.

    SQL> select count (*) from document2;

    COUNT(*)
    ----------
    1

    so do I also can base on this DOCUMENT2 to create a relational view?

    Edited by: Cow on Apr 3, 2011 10:06 AM

    Edited by: Cow on Apr 5, 2011 11:37 AM
  • 5. Re: insert .xml file into xmltype table?
    odie_63 Guru
    Currently Being Moderated
    so do I also can base on this DOCUMENT2 to create a relational view?
    Yes.
  • 6. Re: insert .xml file into xmltype table?
    823127 Newbie
    Currently Being Moderated
    It is very helpful.

    Thanks a llot GURU!

    Edited by: Cow on Apr 4, 2011 5:22 AM
  • 7. Re: insert .xml file into xmltype table?
    823127 Newbie
    Currently Being Moderated
    fixed error already.

    Edited by: Cow on Apr 4, 2011 2:00 PM
  • 8. Re: insert .xml file into xmltype table?
    odie_63 Guru
    Currently Being Moderated
    SQL> select object_value from document2;

    no rows selected
    I guess that, now, you may need to insert a document? ;)
  • 9. Re: insert .xml file into xmltype table?
    823127 Newbie
    Currently Being Moderated
    I found that I forget commit after insert yesterday.
    so I have to reinsert and commit today.

    now everything is fine.

    Thanks a lot!
  • 10. Re: insert .xml file into xmltype table?
    823127 Newbie
    Currently Being Moderated
    Hi Guru,

    I have created two PROCEDUREs to insert the xml file into two different xmltype tables DOCUMENT and DOCUMENT2.
    The first one works well, but the second one got error. I don't know why?
    If you know please help.

    Thanks.

    Edited by: Cow on Apr 5, 2011 11:36 AM
  • 11. Re: insert .xml file into xmltype table?
    odie_63 Guru
    Currently Being Moderated
    CREATE or REPLACE PROCEDURE loadxml_bin (xml_file_name IN blob) IS
    You're mixing things up.
    Is the argument a file name (VARCHAR2) or a BLOB?
         INSERT INTO document2 VALUES(xmltype(bfilename('XMLDIR', 'xml_file_name')));
    OK, so apparently it's a file name.
    Then, the correct syntax of the constructor is :
    xmltype( 
      bfilename('XMLDIR', 'xml_file_name')
    , nls_charset_id('AL32UTF8')
    )
    You need to specify the character set of the source document (here I used AL32UTF8 arbitrarily).
  • 12. Re: insert .xml file into xmltype table?
    823127 Newbie
    Currently Being Moderated
    Thanks for your help. I found my missing code.
    this time PROCEDURE has been successfully created, but when I run it I got error

    begin
    *
    ERROR at line 1:
    ORA-20101: Exception occurred in loadxml_bin procedure :ORA-22288: file or LOB
    operation FILEOPEN failed
    The system cannot find the file specified.
    ORA-06512: at "SYS.XMLTYPE", line 296
    ORA-06512: at line 1
    ORA-06512: at "FDA_XML.LOADXML_BIN", line 7
    ORA-06512: at line 2

    Edited by: Cow on Apr 5, 2011 11:39 AM
  • 13. Re: insert .xml file into xmltype table?
    odie_63 Guru
    Currently Being Moderated
    bfilename('XMLDIR', 'xml_file_name')
    should be
    bfilename('XMLDIR', xml_file_name)
  • 14. Re: insert .xml file into xmltype table?
    823127 Newbie
    Currently Being Moderated
    Thank you GURU!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points