• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4012
  • Last Modified:

Insert into XML TYPE table in Oracle

I am trying to repeat the samples in this post, but run into some errors:
http://www.oracle.com/technology/pub/articles/quinlan-xml.html
I have 0 knowledge about XML and Oracle XMLDB
If you would like to repeat,  please note that xsd file needs being revised:
1.  change xmlns:xdb=http://xmlns.oracle.com/xdb    to
                  xmlns:xdb="http://xmlns.oracle.com/xdb"
2.  Add line </xs:complexType> right after <xs:attribute name="id" type="xs:string" use="required"/>.  
Before I made the changes,  I could not register the schema.  (I knows nothing about XML,  but somehow figured out that "bug" :-).
After I register the schema,  I tried to do the insert and erroed out
(See the last step in the Code section).
I copied the two txt and the xsd files here.  All 3 files are stored in /tmp directory.
Could some Oracle XMLDB or XML expert out here shed me some light what I
missing and how I can insert into the table after I register the schema?
I really appreciate it.  Thx  If you could,  please email your response to me:
musicbeginner@yahoo.com
Sean
=======================================================

invoicexml.txt file:
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
           xmlns:xdb="http://xmlns.oracle.com/xdb" version="1.0">
<xs:element name="INVOICESCHEMA" xdb:defaultTable="INVOICESCHEMA">
   <xs:complexType>
        <xs:sequence>
             <xs:element name="MailAddressTo">
                   <xs:complexType>
                         <xs:sequence>
                              <xs:element name="Person" type="xs:string"/>
                              <xs:element name="Street" type="xs:string"/>
                         </xs:sequence>
                       <xs:attribute name="id" type="xs:string" use="required"/>
                   </xs:complexType>
             </xs:element>
        </xs:sequence>
  </xs:complexType>
</xs:element>
</xs:schema>

===============
invoiceformtest.xsd  and invoiceformtest.txt files:

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
           xmlns:xdb="http://xmlns.oracle.com/xdb" version="1.0">
<xs:element name="INVOICEFORMTEST" xdb:defaultTable="INVOICEFORMTEST">
   <xs:complexType>
      <xs:sequence>
         <xs:element name="MailAddressTo">
            <xs:complexType>
               <xs:sequence>
                  <xs:element name="Person" type="xs:string"/>
                  <xs:element name="Street" type="xs:string"/>
                  <xs:element name="City" type="xs:string"/>
                  <xs:element name="State" type="xs:string"/>
                  <xs:element name="Zipcode" type="xs:string"/>
               </xs:sequence>
               <xs:attribute name="id" type="xs:string" use="required"/>
            </xs:complexType>
         </xs:element>
      </xs:sequence>
   </xs:complexType>
</xs:element>
</xs:schema>




Here are the code I used try to repeat the samples:
 
create directory XMLDIR as '/tmp';    
 
create table invoiceXML_col (
inv_id number primary key,
inv_doc XMLType);
 
-- create table invoiceXML_tbl of XMLtype;
 
Insert into invoicexml_col values (1,
XMLType(bfilename('XMLDIR', 'invoicexml.txt'),
nls_charset_id('AL32UTF8') ));
 
Insert into invoicexml_tbl values
(XMLType(bfilename('XMLDIR', 'invoicexml.txt'),
 nls_charset_id('AL32UTF8')));
-- register schema
BEGIN
    DBMS_XMLSCHEMA.registerSchema(
        SCHEMAURL => 'http://xmlns.oracle.com/xdb/invoiceformtest.xsd',
        SCHEMADOC => bfilename('XMLDIR','invoiceformtest.xsd'),
        CSID => nls_charset_id('AL32UTF8'));
   END;
/
Insert into invoiceformtest values
(XMLType(bfilename('XMLDIR', 'invoiceformtest.txt'), nls_charset_id('AL32UTF8')
        )
);
 
ERROR at line 1:
ORA-30937: No schema definition for 'schema' (namespace 'http://www.w3.org/2001/XMLSchema') in parent '/'

Open in new window

0
musicbeginner
Asked:
musicbeginner
  • 2
1 Solution
 
schwertnerCommented:
Look in Oracle magazine:
When Rows and Columns Won't Do
November/December 2003 Issue

See the Snippet for my simple example:
THE REAL RESULT:
 
 
SQL*Plus: Release 9.2.0.4.0 - Production on Tue Jan 13 14:56:19 2004
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
SQL> connect xml/xml@test
 
SQL> CREATE TABLE empleados (empid   NUMBER PRIMARY KEY, 
  2                          empname VARCHAR2(30), 
  3                          empjob  VARCHAR2(30), 
  4                          empsal  NUMBER); 
 
Table created.
 
SQL> create or replace procedure InsertXML(xmlDoc IN VARCHAR2, tableName IN VARCHAR2) is 
  2     insCtx DBMS_XMLSave.ctxType; 
  3     rows number; 
  4   begin 
  5      insCtx := DBMS_XMLSave.newContext(tableName); -- get the context handle 
  6      rows := DBMS_XMLSave.insertXML(insCtx,xmlDoc); -- this inserts the document 
  7      dbms_output.put_line(to_char(rows) || ' rows inserted'); 
  8      DBMS_XMLSave.closeContext(insCtx);            -- this closes the handle 
  9  end; 
 10  / 
 
Procedure created.
SQL> exec InsertXML('<?xml version="1.0"?><ROWSET><ROW num="1"><EMPID>10</EMPID><EMPNAME>Perry Smith
</EMPNAME><EMPJOB>Manager</EMPJOB><EMPSAL>800</EMPSAL></ROW><ROW num="1"><EMPID>20</EMPID><EMPNAME>J
ohn Calvach</EMPNAME><EMPJOB>Principal Support Consultant</EMPJOB><EMPSAL>900</EMPSAL></ROW><ROW num
="1"><EMPID>30</EMPID><EMPNAME>Louis Bald</EMPNAME><EMPJOB>Technical Specialist</EMPJOB><EMPSAL>400<
/EMPSAL></ROW><ROW num="1"><EMPID>40</EMPID><EMPNAME>Anthony Flowers</EMPNAME><EMPJOB>Technical Team
 Leader</EMPJOB><EMPSAL>500</EMPSAL></ROW><ROW num="1"><EMPID>50</EMPID><EMPNAME>George Monk</EMPNAM
E><EMPJOB>Support Consultant</EMPJOB><EMPSAL>200</EMPSAL></ROW></ROWSET>','empleados');
 
PL/SQL procedure successfully completed.
 
SQL> select * from empleados;
 
     EMPID EMPNAME                        EMPJOB
---------- ------------------------------ ------------------------------
    EMPSAL
----------
        10 Perry Smith                    Manager
       800
 
        20 John Calvach                   Principal Support Consultant
       900
 
        30 Louis Bald                     Technical Specialist
       400
 
 
     EMPID EMPNAME                        EMPJOB
---------- ------------------------------ ------------------------------
    EMPSAL
----------
        40 Anthony Flowers                Technical Team Leader
       500
 
        50 George Monk                    Support Consultant
       200
 
 
SQL> 
 
 
 
SQL> select SYS_XMLGEN(empid) FROM empleados;
 
SYS_XMLGEN(EMPID)
---------------------------------------------------------
<?xml version="1.0"?>
<EMPID>10</EMPID>
 
<?xml version="1.0"?>
<EMPID>20</EMPID>
 
<?xml version="1.0"?>
<EMPID>30</EMPID>
 
<?xml version="1.0"?>
<EMPID>40</EMPID>
 
SYS_XMLGEN(EMPID)
 
<?xml version="1.0"?>
<EMPID>50</EMPID>
 
 
SQL> select SYS_XMLAGG(SYS_XMLGEN(empid)) FROM empleados;
<?xml version="1.0"?>
<ROWSET>
<EMPID>10</EMPID>
<EMPID>20</EMPID>
<EMPID>30</EM

Open in new window

0
 
musicbeginnerAuthor Commented:
Another solution to my origina question:  I created this txt file and the last step in my original question would work;
<INVOICEFORMTEST>
     <MailAddressTo id="1">
            <Person>Joe Smith</Person>
            <Street>10 Apple Tree Lane</Street>
            <City>New York</City>
            <State>NY</State>
            <Zipcode>12345</Zipcode>
     </MailAddressTo>
</INVOICEFORMTEST>

Thx
0
 
schwertnerCommented:
Good luck!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now