14 Replies Latest reply: Apr 5, 2011 10:41 AM by 823127 RSS

    insert .xml file into xmltype table?

    823127
      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
          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
            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
              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
                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
                  so do I also can base on this DOCUMENT2 to create a relational view?
                  Yes.
                  • 6. Re: insert .xml file into xmltype table?
                    823127
                    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
                      fixed error already.

                      Edited by: Cow on Apr 4, 2011 2:00 PM
                      • 8. Re: insert .xml file into xmltype table?
                        odie_63
                        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
                          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
                            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
                              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
                                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
                                  bfilename('XMLDIR', 'xml_file_name')
                                  should be
                                  bfilename('XMLDIR', xml_file_name)
                                  • 14. Re: insert .xml file into xmltype table?
                                    823127
                                    Thank you GURU!