Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

row_numbers in xmlquery to fetch only limited number of nodes for better pagination

user8195117Jan 12 2022

Hi All,
Have the following xml stored in the table and would like to fetch only 1st 3 <ABB> nodes. How can we do this in the xmlquery. Please help
This is xml query used to get the below xml from a complex xml
SELECT XMLQUERY (
'for $i in /ROWSET/ROW/XML_DATA/AA/ABB return
if ((exists($i/ABB_QTY[@Check_Ind="Y"]))) then $i else null'
PASSING XML_DATA RETURNING CONTENT)
FROM tab1
WHERE FILE_ID = 'xxx' AND SEQ_NUM = 'yyy';
<ABB>
<ABB_SEQ_NUM>III_305210500</ABB_SEQ_NUM>
<ABB_ID Check_Ind="N">III</ABB_ID>
<ABB_QTY Check_Ind="Y">-2</ABB_QTY>
<ABB_UOM Check_Ind="N">LTR</ABB_UOM>
<ABB1>
<ABB1_SEQ_NUM>IID_305209921</ABB1_SEQ_NUM>
<ABB1_ID Check_Ind="N" >IID</ABB1_ID>
<ABB1_DESC_TYPE Check_Ind="N" >F</ABB1_DESC_TYPE>
<ABB1_DESC Check_Ind="N" >ACE GUAVA CIDER 4/6PK 12OZ CANS</ABB1_DESC>
</ABB1>
</ABB>
<ABB>
<ABB_SEQ_NUM>III_305210501</ABB_SEQ_NUM>
<ABB_ID Check_Ind="N">III</ABB_ID>
<ABB_QTY Check_Ind="Y">-2</ABB_QTY>
<ABB_UOM Check_Ind="N">LTR</ABB_UOM>
<ABB1>
<ABB1_SEQ_NUM>IID_305209921</ABB1_SEQ_NUM>
<ABB1_ID Check_Ind="N" >IID</ABB1_ID>
<ABB1_DESC_TYPE Check_Ind="N" >F</ABB1_DESC_TYPE>
<ABB1_DESC Check_Ind="N" >ACE GUAVA CIDER 4/6PK 12OZ CANS</ABB1_DESC>
</ABB1>
</ABB>
<ABB>
<ABB_SEQ_NUM>III_305210502</ABB_SEQ_NUM>
<ABB_ID Check_Ind="N">III</ABB_ID>
<ABB_QTY Check_Ind="Y">-2</ABB_QTY>
<ABB_UOM Check_Ind="N">LTR</ABB_UOM>
<ABB1>
<ABB1_SEQ_NUM>IID_305209921</ABB1_SEQ_NUM>
<ABB1_ID Check_Ind="N" >IID</ABB1_ID>
<ABB1_DESC_TYPE Check_Ind="N" >F</ABB1_DESC_TYPE>
<ABB1_DESC Check_Ind="N" >ACE GUAVA CIDER 4/6PK 12OZ CANS</ABB1_DESC>
</ABB1>
</ABB>
<ABB>
<ABB_SEQ_NUM>III_305210503</ABB_SEQ_NUM>
<ABB_ID Check_Ind="N">III</ABB_ID>
<ABB_QTY Check_Ind="Y">-2</ABB_QTY>
<ABB_UOM Check_Ind="N">LTR</ABB_UOM>
<ABB1>
<ABB1_SEQ_NUM>IID_305209921</ABB1_SEQ_NUM>
<ABB1_ID Check_Ind="N" >IID</ABB1_ID>
<ABB1_DESC_TYPE Check_Ind="N" >F</ABB1_DESC_TYPE>
<ABB1_DESC Check_Ind="N" >ACE GUAVA CIDER 4/6PK 12OZ CANS</ABB1_DESC>
</ABB1>
</ABB>
<ABB>
<ABB_SEQ_NUM>III_305210504</ABB_SEQ_NUM>
<ABB_ID Check_Ind="N">III</ABB_ID>
<ABB_QTY Check_Ind="Y">-2</ABB_QTY>
<ABB_UOM Check_Ind="N">LTR</ABB_UOM>
<ABB1>
<ABB1_SEQ_NUM>IID_305209921</ABB1_SEQ_NUM>
<ABB1_ID Check_Ind="N" >IID</ABB1_ID>
<ABB1_DESC_TYPE Check_Ind="N" >F</ABB1_DESC_TYPE>
<ABB1_DESC Check_Ind="N" >ACE GUAVA CIDER 4/6PK 12OZ CANS</ABB1_DESC>
</ABB1>
</ABB>

This post has been answered by Solomon Yakobson on Jan 12 2022
Jump to Answer
Comments
Post Details
Added on Jan 12 2022
5 comments
226 views