problem while loading xml doc to a schema based table]

From: amal <amalkaau_at_yahoo-dot-com.no-spam.invalid>
Date: Tue, 29 Nov 2005 02:11:23 -0600
Message-ID: <e9OdnStSbaC2kBHeRVn_vA@giganews.com>

HI,every one hope you may help me with this problem… Well …
One stage of my project is to store an xml file into a table of XMLType column in structured way (using the schema) So that’s what I did:
1. I copied the schema file into this folder http://localhost:8080/public/

2. Then I register the schema

SQL> ALTER SESSION SET EVENTS='31098 trace name context forever';

Session altered.

  1 begin
  2 dbms_xmlschema.registeruri(
  3 'http://localhost:8080/public/auction.xsd',   4 '/public/auction.xsd',
  5 local=>true, gentypes=>true, genbean=>false, gentables=>false);
  6* end;
SQL> r
  1 begin
  2 dbms_xmlschema.registeruri(
  3 'http://localhost:8080/public/auction.xsd',   4 '/public/auction.xsd',
  5 local=>true, gentypes=>true, genbean=>false, gentables=>false);
  6* end;

PL/SQL procedure successfully completed.

3. after that I create the table as it mentioned below

SQL> create table struc_100kb( xmldoc sys.xmltype)   2 xmltype column xmldoc store as object relational   3 xmlschema "http://localhost:8080/public/auction.xsd"   4 element "site";

Table created.

SQL>SQL> describe struc_100kb

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 XMLDOC                                            
SYS.XMLTYPE(XMLSchema "http:
                                                   
//localhost:8080/public/auct
                                                    ion.xsd" Element
"site") STO
                                                    RAGE
Object-relational TYPE
                                                    "site171_T"

4.	then I used he sqlldr to load the xml document , and that what

included in the control file
load data
infile *
replace
into table struc_100kb
fields terminated by ','
(

fname filler char(40),
xmldoc lobfile(fname) terminated by EOF
)
begindata
c:\amal\xmldoc\xmldoc_used\doc100kb.xml

C:\oracle\product\10.1.0\db_1\BIN>sqlldr scott/**** control=c:\amal\work.ctl

And after I load the document I got this error in the log file associated with sqlldr..

Record 1: Rejected - Error on table STRUC_100KB. ORA-21700: object does not exist or is marked for delete

Do u know how 2 handle this problem

--

I thought to show u part of the schema and xml files
auction.xsd

<?xml version="1.0" encoding="UTF-8"?>
<!-- edited with XML Spy v4.4 U (http://www.xmlspy.com) by Brian
Murphy (wpi) -->
<!--W3C Schema generated by XML Spy v4.4 U


(http://www.xmlspy.com)-->

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
elementFormDefault="qualified">
	<xs:element name="address">
		<xs:complexType>
			<xs:sequence>
				<xs:element ref="street"/>
				<xs:element ref="city"/>
				<xs:element ref="country"/>
				<xs:element ref="province" minOccurs="0"/>
				<xs:element ref="zipcode"/>
			</xs:sequence>
		</xs:complexType>
	</xs:element>

…
….

doc100kb.xml

<?xml version="1.0" standalone="yes" ?> 
- <site xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://localhost:8080/public/auction.xsd">
- <regions>
- <africa>
- <item id="item0">
  <location>United States</location> 
  <quantity>1</quantity> 
  <name>duteous nine eighteen</name> 
  <payment>Creditcard</payment> 
- <description>
- <parlist>
- <listitem>
- <text>
  page rous lady idle authority capt professes stabs monster petition
heave humbly removes rescue runs shady peace most piteous worser oak
assembly holes patience but malice whoreson mirrors master tenants
smocks yielded 
  <keyword>officer embrace such fears distinction
attires</keyword> 
  </text>
.


one more Q...
is the first lines on my xml document are correct ??

Received on Tue Nov 29 2005 - 02:11:23 CST