Structured XMLIndex giving error on creation

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>

This XML contain around 2500 values for Node2 Orderattr. Am using the below query to print out all the Node2 values in one go 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

Now I want to restrict my resultset to only first 25 Node2 Orderattr values and not the entire set. How should I go about restricting the resultset to only first 25 node values.

Able to get it using XMLTable.

I have tried below XMLQuery function to achieve the same above output, but receive truncated output.

SELECT XMLQuery(            '/OrderVal[1]/Orderattrs/Orderattr[1]/OrderVals/OrderVal[1]/Orderattrs/Orderattr/(@listname, OrderVals/OrderVal/listvalue/text())'
PASSING o.Order_desc RETURNING CONTENT)
FROM Orders o;

Output:- Node2_Child1Node2_Child1_OrderValue_1Node2_Child2Node2_Child2_OrderValue_1Node2_Child3Node2_Child3_OrderValue_1Node2_Child4Node2_Child4_OrderValue_1Node2_Child5Node2_Child5_OrderValue_1........Node2_Child25Node2_Child25_OrderValue_1

Thanks...

What is the "first 25?" You need some sort order. Then use ROWNUM to limit the results. — OldProgrammer, Sep 03 '13 at 01:51

ThinkJet · Accepted Answer · 2013-09-04T08:33:01.657

You need to add a condition with position() Xpath function to the end of a XQuery expression:

...
XMLTable(
  '/OrderVal[1]/Orderattrs/Orderattr[1]/OrderVals/OrderVal[1]/Orderattrs/Orderattr[position() <= 25]'
...

SQLFiddle test with your exapmle

Update

Question updated, so a little bit more about XMLQuery() function. First of all, this function return single XMLType, therefore to get individual elements you need to use ExtractValue() function:

with params as (
  select XMLParse(content '
  <draftorders>
   <!-- EXAMPLE XML FROM QUESTION GOES HERE -->
 </draftorders>
  ')
  doc from dual
)
select 
  ExtractValue(xml_val, '/Orderattr/@Ordername') order_name,
  ExtractValue(xml_val, '/Orderattr/OrderVals/OrderVal[1]/listvalue') list_value
from (
  SELECT 
    XMLQuery(
      '//OrderVal[1]/Orderattrs/Orderattr[1]/OrderVals/OrderVal[1]/Orderattrs/Orderattr[position() < 2]'
      PASSING doc returning content
    ) xml_val
  from 
    params
);

SQLFiddle test for XMLQuery variant

Second: if your XQuery return set of nodes then resulting XML not well-formed because there are no single root node.

Last: In your variant with XMLQuery expression returns set of values, not XML. All value sequences in XQuery concatenated without separators, so you got what you got:

Node2_Child1<![CDATA[ Node2_Child1_OrderValue_1]]>Node2_Child2<![CDATA[ Node2_Child2_OrderValue_1]]>

and after evaluation this expression translated to just

Node2_Child1Node2_Child1_OrderValue_1Node2_Child2Node2_Child2_OrderValue_1

Oops... You mistyped @Ordername and changed it to @listname at least in question text, so this part returns empty string and if this expression evaluated without changes result would be like

Node2_Child1_OrderValue_1Node2_Child2_OrderValue_1

Its working with position(). I am more inclined towards using XMLQuery to perform the same operation i.e. print say 25 node-child values.I have edited my question to list the XMLQuery I tried to achieve the same, but I get truncated output when I run the XMLQuery. Please advice. — Sunny, Sep 03 '13 at 15:56
@Sunny Please update also example XML in your question because your XQuery don't work with example you provided as well as XMLTable variant. Just look at top node in query (`/OrderVal`) and top node in example XML (`draftorders`). — ThinkJet, Sep 03 '13 at 21:58
With the XML query I provided above, I added position() to return 25 rows. But all the resultant rows are coming after another and not one row at a time. How can I get the output rows being printed one row at a time like we get it when I use XMLTable ?? — Sunny, Sep 04 '13 at 08:09
Modified question to show the Output I am getting when running the XMLQuery (as given in my question)... — Sunny, Sep 04 '13 at 08:10
@Sunny Why don't use [XMLTable](http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions228.htm)? It acts exactly as [XMLQuery](http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions224.htm) from XQuery point of view, but [designed](http://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb_xquery.htm#sthref1705) exactly to return results "row by row" as you required. Just look at this thing: you `select` XQuery `from` some table and you `select` some data `from` XMLTable ... — ThinkJet, Sep 04 '13 at 08:30

Structured XMLIndex giving error on creation

1 Answers1