Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Oracle XMLQuery won't work with registered Schema

Avatar of scottvandenberg
scottvandenberg asked on
Oracle DatabaseXML
5 Comments1 Solution986 ViewsLast Modified:
Oracle's XMLQuery works fine when the XMLType field is NOT constrained to conform to an XML Schema but does not return any results when the XMLType field IS constrained to conform to an XML Schema.  For instance, in the following the final query incorrectly returns nothing:

CREATE TABLE Agents (id NUMBER, clients XMLType)
    XMLType COLUMN clients  
    XMLSCHEMA "customerListSchema"  
    ELEMENT "customerList";  

INSERT INTO Agents VALUES (47,
    XMLTYPE (bfilename ('HOME', 'myCustomers.xml'),
     nls_charset_id('AL32UTF8')));

SELECT XMLQuery ('for $i in //Address
           return $i/Street'
           PASSING A.Clients RETURNING CONTENT) AS StreetInfo
        FROM Agents A;

The data in "myCustomers.xml" is fine; it gets validated and inserted; it shows up fine when I do a "select * from agents".

However, doing the exact same thing but without constraining the schema, works fine, as follows:

CREATE TABLE Agents2 (id NUMBER, clients XMLType);

INSERT INTO Agents2 VALUES (47,
    XMLTYPE (bfilename ('HOME', 'myCustomers.xml'),
     nls_charset_id('AL32UTF8')));

SELECT XMLQuery ('for $i in //Address
           return $i/Street'
           PASSING A.Clients RETURNING CONTENT) AS StreetInfo
        FROM Agents2 A;

Any ideas on what I might be missing or doing wrong?  Thank you very much for your help.  I'm happy to provide the ".xsd" and ".xml" files if necessary, but they don't seem to be the problem,