Home » SQL & PL/SQL » SQL & PL/SQL » Problem querying node values from a XMLType table defined from a schema. (Oracle 10.2G Windows XP)
Problem querying node values from a XMLType table defined from a schema. [message #363296] Fri, 05 December 2008 13:36 Go to next message
Rustican
Messages: 51
Registered: July 2006
Member
Problem querying from a XMLType table defined from a schema.

I registered a schema, and created a table from it. Then inserted a xml file into the table. I'm trying to query specific values from the table and i'm having the following problem.

Query:
SELECT extract(object_value,'/company/*') FROM xmltestfile;
Result:
<department><id>10</id><name>Accounting</name></department>

Query:
SELECT extract(object_value,'/company/department/name') FROM xmltestfile;
Result:
NULL

Why is the result for my second query NULL when the node for the company name is "Accounting"? Can some one help?

Below is my schema and inserted xml file for reference:

Registered Schema:
<schema xmlns="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.oradev.com/sampleFile.xsd"
xmlns:samp="http://www.oradev.com/sampleFile.xsd"
version="1.0">
<simpleType name="departmentIdType">
<restriction base="integer">
<enumeration value="10"/>
<enumeration value="20"/>
<enumeration value="30"/>
<enumeration value="40"/>
</restriction>
</simpleType>
<element name="company">
<complexType>
<sequence>
<element name="department">
<complexType>
<sequence>
<element name = "id" type = "samp:departmentIdType"/>
<element name = "name" type = "string"/>
</sequence>
</complexType>
</element>
</sequence>
</complexType>
</element>
</schema>

XMLType Table Created:

CREATE TABLE XmlTestFile OF XMLType
XMLSCHEMA "http://www.oradev.com/samplefile.xsd"
ELEMENT "company";

Inserted xml:
<?xml version="1.0"?>
<samp:company xmlns:samp="http://www.oradev.com/sampleFile.xsd" >
<department>
<id>10</id>
<name>Accounting</name>
</department>
</samp:company>

[Updated on: Fri, 05 December 2008 13:40]

Report message to a moderator

Re: Problem querying node values from a XMLType table defined from a schema. [message #363304 is a reply to message #363296] Fri, 05 December 2008 14:54 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
My XML is rusty, but try:

SELECT extract(object_value,'/company/department/name/text()') FROM xmltestfile;
Re: Problem querying node values from a XMLType table defined from a schema. [message #363306 is a reply to message #363304] Fri, 05 December 2008 15:00 Go to previous message
Rustican
Messages: 51
Registered: July 2006
Member
andrew again wrote on Fri, 05 December 2008 14:54
My XML is rusty, but try:

SELECT extract(object_value,'/company/department/name/text()') FROM xmltestfile;




Nope, that also returns a NULL.

I got help from another forum when someone posted this:

SELECT
extract(
object_value,
'/samp:company/department/name',
'xmlns:samp="http://www.oradev.com/sampleFile.xsd"'
)
FROM xmltestfile;

It works but i'm trying to understand why the extra xml line is necessary. Also if there is a way of setting up my schema better so that i don't need the extra xml in the future.
Previous Topic: How to combine 2 fields from 1 table and insert it into another table?
Next Topic: look for & in string
Goto Forum:
  


Current Time: Wed Dec 07 14:24:39 CST 2016

Total time taken to generate the page: 0.14610 seconds