Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2005
    Posts
    6

    Unanswered: problem while Querying xmltype col table (structured)

    HI,
    for all the expert ppl ,,(actually u all experts) here plz 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??

  2. #2
    Join Date
    Nov 2005
    Posts
    6
    umm..well just to remind u that even when i ruun count(*) query for that table i got zero!!

  3. #3
    Join Date
    Nov 2005
    Posts
    6
    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>
    ................
    ..............

  4. #4
    Join Date
    Nov 2005
    Posts
    6
    wish really to get a responde to the peroblem..cuz i don't have much time...

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Specify log=a.log to your sqlldr command line and see if the log says it actually loads succesfully.

    Alan

  6. #6
    Join Date
    Nov 2005
    Posts
    6
    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

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    try doing a google search on 'oracle ora 19007' or go to the XML Database Developer's Guide fo rmore info on the error.

    Alan

  8. #8
    Join Date
    Nov 2005
    Posts
    6
    I added this in the beginning of the xml document….

    <?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>


    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •