Issue with creating XMLQuery for given XPATH

Question

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="HROrder">
        <OrderVals>
            <OrderVal>
                 <listvalue>Order1</listvalue>
                 <Orderattrs>
                      <Orderattr Ordername="Node1_Child1">
                         <OrderVals>
                             <OrderVal>
                                  <listvalue><![CDATA[ Node1_Child1_OrderValue_1]]></listvalue>
                                  <Orderattrs>
                                       <Orderattr Ordername="Node2_Child1">
                                            <OrderVals>
                                                 <OrderVal>
                                                      <listvalue><![CDATA[ Node2_Child1_OrderValue_1]]></listvalue>
                                                 </OrderVal>
                                            </OrderVals>
                                       </Orderattr>
                                       <Orderattr Ordername="Node2_Child2">
                                           <OrderVals>
                                                <OrderVal>
                                                      <listvalue><![CDATA[ Node2_Child2_OrderValue_1]]></listvalue>
                                                </OrderVal>
                                           </OrderVals>
                                       </Orderattr>
                                  </Orderattrs>
                             </OrderVal>
                         </OrderVals>
                      </Orderattr>
                 </Orderattrs>
            </OrderVal>
        </OrderVals>
     </Orderattr>
  </Orderattrs>
</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.

/******** Modified query run:-

SELECT XMLQuery('//OrderVal/Orderattrs/Orderattr/(@Ordername,   OrderVals/OrderVal/listvalue)/data(.)'
             PASSING o.Order_desc RETURNING CONTENT
           )
 FROM Orders o;

Output:-

Node2_Child1
Node2_Child1_OrderValue_1
Node2_Child

Fetching all Nodes and its child's using XMLTABLE.

SELECT ord.OrdName, ord.OrdVal
FROM Orders,   XMLTable('/OrderVal[1]/Orderattrs/Orderattr[1]/OrderVals/OrderVal[1]/Orderattrs/Orderattr'
                 PASSING Order_desc
                 COLUMNS "OrdName"   VARCHAR2(4000) PATH '@Ordername',
                         "OrdVal"    VARCHAR2(4000) PATH  'OrderVals/OrderVal[1]/listvalue') ord;

Output:-

Node2_Child1
Node2_Child1_OrderValue_1
Node2_Child2  
Node2_Child2_OrderValue_1
......
Node2_Child2500
Node2_Child2500_OrderValue_1

How can I achieve the same using XMLQuery ??

Answer 1

You queries and example XML do not seem to match, so I aligned to your queries as far as possible. As your XML was broken, too you might have to adjust some axis steps.

You can "branch" at some point (for example to fetch the attribute value and a subnode) using parentheses. This query will fetch all results at once for an arbitrary number of order attributes:

//OrderVal/Orderattrs/Orderattr/(@Ordername, OrderVals/OrderVal/listvalue)/data(.)