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>