Saturday, 31 August 2013

Issue with creating XMLQuery for given XPATH

Issue with creating XMLQuery for given XPATH

I have a table having one columns as XMLTYPE being stored with
Object-Relational storage. Below is table ddl.
CREATE TABLE Orders ( Order_id number not null,
Order_status Varchar2(100),
Order_desc XMLType not null)
XMLTYPE Order_desc STORE AS OBJECT RELATIONAL
XMLSCHEMA "http://localhost/public/xsd/Orderstore.xsd"
ELEMENT "OrderVal"
);
I have successfully registered the schema to load XSD with XML DB. Below
is the XML being loaded into the XMLTYPE column.
<?xml version="1.0" encoding="utf-8"?>
<draftorders>
<OrderSumm>
<Ordercod>OrderBookings</Ordercod>
</OrderSumm>
<Orderattrs>
<Orderattr Ordername="Order Name">
<OrderVal>
<listvalue>Node1_Child1_OrderValue_1</value>
<Orderattrs>
<Orderattr Ordername="Node2_Child1">
<OrderVals>
<OrderVal>
<listvalue>Node2_Child1_OrderValue_1</value>
</OrderVal>
</OrderVals>
</Orderattr>
<Orderattr Ordername="Node2_Child2">
<OrderVals>
<OrderVal>
<listvalue>Node2_Child2_OrderValue_1</value>
</OrderVal>
</OrderVals>
</Orderattr>
</Orderattrs>
</OrderVal>
</OrderVals>
</Orderattrs>
</OrderVal>
</OrderVals>
</draftorders>
I have the query using "extract" to print the below output:
SELECT
extract(o.Order_desc,'/OrderVal[1]/Orderattrs/Orderattr[1]/OrderVals/OrderVal[1]/Orderattrs/Orderattr[0]/@Ordername').getStringVal()
"Node1",
extract(o.Order_desc,'/OrderVal[1]/Orderattrs/Orderattr[1]/OrderVals/OrderVal[1]/Orderattrs/Orderattr[0]/OrderVals/OrderVal[1]/listvalue/text()').getStringVal()
"Node1Child",
extract(o.Order_desc,'/OrderVal[1]/Orderattrs/Orderattr[1]/OrderVals/OrderVal[1]/Orderattrs/Orderattr[1]/@Ordername').getStringVal()
"Node2",
extract(c.Order_desc,'/OrderVal[1]/Orderattrs/Orderattr[1]/OrderVals/OrderVal[1]/Orderattrs/Orderattr[1]/OrderVals/OrderVal[1]/listvalue/text()').getStringVal()
"Node2Child"
FROM Orders o;
OUTPUT:-
Node2_Child1
Node2_Child1_OrderValue_1
Node2_Child2
Node2_Child2_OrderValue_1
I want to achieve the same output using XMLQuery, but I am unable to build
query to print the child node. Till now, I can only print the node value
using XMLQuery as given below:-
SELECT XMLQuery(
'/OrderVal[1]/Orderattrs/Orderattr[1]/OrderVals/OrderVal[1]/Orderattrs/Orderattr[0]/@Ordername'
PASSING o.Order_desc RETURNING CONTENT
)
FROM Orders o;
How can I achieve the same output from using "extract", with "XMLQuery" ?
Thanks.

No comments:

Post a Comment