| xml query problem [message #147822] |
Mon, 21 November 2005 05:03  |
Amal
Messages: 9
Registered: January 2005
|
Junior Member |
|
|
Hi for all ppl here...i Hope u 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
But after that when I run any query such as this
select e.xmldoc.extract( 'site/people/person[@id="person12"]/name/text()') from struc_100kb e
I got a message no rows selected
So whats my problem??
|
|
|
|
|
|
| Re: xml query problem [message #147944 is a reply to message #147822] |
Mon, 21 November 2005 19:43  |
Amal
Messages: 9
Registered: January 2005
|
Junior Member |
|
|
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>
- <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>
................
..............
|
|
|
|
|
|
| Re: xml query problem [message #148611 is a reply to message #148511] |
Fri, 25 November 2005 05:49 |
Amal
Messages: 9
Registered: January 2005
|
Junior Member |
|
|
This is the log file generated through inserting:
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table STRUC_100KB, loaded from every logical record.
Insert option in effect for this table: REPLACE
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
FNAME FIRST 40 , CHARACTER
(FILLER FIELD)
XMLDOC DERIVED * EOF CHARACTER
Dynamic LOBFILE. Filename in field FNAME
Record 1: Rejected - Error on table STRUC_100KB.
ORA-19007: Schema - does not match expected http://localhost:8080/public/auction.xsd.
Table STRUC_100KB:
0 Rows successfully loaded.
1 Row not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 2944 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 1
Total logical records rejected: 1
Total logical records discarded: 0
Run began on Sun Nov 06 14:40:25 2005
Run ended on Sun Nov 06 14:40:31 2005
Elapsed time was: 00:00:05.79
CPU time was: 00:00:00.03
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
.