| Querying an Element Value from a Schema-based XMLType column [message #130587] |
Mon, 01 August 2005 06:01  |
gantir
Messages: 1
Registered: August 2005
|
Junior Member |
|
|
Hi,
Please find below the details of my question. Thanks in advance.
Task :-
1) I registered an XML schema (po.xsd) with the Database.
2) I created a table (po_tab) which contains a column defined as XMLType datatype which is schema-based.
3) Inserted an XML document in the table po_tab
4) I want to query the value of a particular element in the XML from the table. I am getting "1 row selected" when I do the query in SQL*PLUS but I do not get to see any results.
Is it something to do with the SQL*PLUS environment settings ????
Also the below query returns this :-
SQL> SELECT NVL(ExtractValue(p.po,'/PurchaseOrder/PONum'),'Raghu')
2* FROM po_tab p
SQL> /
NVL(E
-----
Raghu
1 row selected.
Do you have any ideas on this ?????
Please find the statements that I have executed for Steps 1 through 4.
1)
declare
doc varchar2(1000) := '<schema
targetNamespace="http://www.oracle.com/PO.xsd"
xmlns:po="http://www.oracle.com/PO.xsd"
xmlns="http://www.w3.org/2001/XMLSchema">
<complexType name="PurchaseOrderType">
<sequence>
<element name="PONum" type="decimal"/>
<element name="Company">
<simpleType>
<restriction base="string">
<maxLength value="100"/>
</restriction>
</simpleType>
</element>
<element name="Item" maxOccurs="1000">
<complexType>
<sequence>
<element name="Part">
<simpleType>
<restriction base="string">
<maxLength value="1000"/>
</restriction>
</simpleType>
</element>
<element name="Price" type="float"/>
</sequence>
</complexType>
</element>
</sequence>
</complexType>
<element name="PurchaseOrder" type="po:PurchaseOrderType"/>
</schema>';
begin
dbms_xmlschema.registerSchema('http://www.oracle.com/PO.xsd', doc);
end;
/
2)
create table po_tab(
id number,
po sys.XMLType
)
xmltype column po
XMLSCHEMA "http://www.oracle.com/PO.xsd"
element "PurchaseOrder"
/
3)
insert into po_tab values (1,
xmltype('<po:PurchaseOrder xmlns:po="http://www.oracle.com/PO.xsd"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.oracle.com/PO.xsd
http://www.oracle.com/PO.xsd">
<PONum>1001</PONum>
<Company>Oracle Corp</Company>
<Item>
<Part>9i Doc Set</Part>
<Price>2550</Price>
</Item>
<Item>
<Part>8i Doc Set</Part>
<Price>350</Price>
</Item>
</po:PurchaseOrder>'))
/
4)
SELECT ExtractValue(p.po,'/PurchaseOrder/PONum')
FROM po_tab p
/
[Updated on: Mon, 01 August 2005 06:25] Report message to a moderator |
|
|
|
| Re: Querying an Element Value from a Schema-based XMLType column [message #130735 is a reply to message #130587] |
Tue, 02 August 2005 03:32  |
mchadder
Messages: 224
Registered: May 2005
Location: UK
|
Senior Member |
|
|
Hello.
Not sure about the syntax from a purely SQL context, but querying within PL/SQL is relatively easy. One of the problems with your
query is that you don't specify the defined namespace in your EXTRACTVALUE XPath, i.e.
SQL> BEGIN
2 FOR j IN ( SELECT po FROM po_tab )
3 LOOP
4 FOR i IN ( SELECT EXTRACTVALUE(VALUE(t), '//PONum') po_num
5 FROM TABLE(XMLSEQUENCE(EXTRACT(j.po, '/PurchaseOrder', 'xmlns="http://www.oracle.com/PO.xsd"'))) t )
6 LOOP
7 dbms_output.put_line('po_num : ' || i.po_num);
8 END LOOP;
9 END LOOP;
10 END;
11 /
po_num : 1001
PL/SQL procedure successfully completed.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
