Hi,
I have a XML Doc where I have multiple nodes in a tag and I need to extract the data using extractvalue funtion.
My XMLtype doc stores a xml doc in this form:
<CYKDoc xmlns="CYKdocument.bankxyz.com">
<CYK>
<gci>12345678</gci>
<system>LION</system>
<recordType>1</recordType>
<CYKId>987654</CYKId>
<policyVersion>DDL 1.0</policyVersion>
</CYK>
<CYKResponse>
<id>q_grid</id>
<grid>
<data>
<dataRow>
<dataColumn>
<id>q_grid_id</id>
<name>q_grid_id</name>
<codes>
<code>704</code>
</codes>
</dataColumn>
<dataColumn>
<id>q_owner_type</id>
<name>q_owner_type</name>
<codes>
**<code>IN</code>**
** <code>OUT</code>**
** <code>BETWEEN</code>**
</codes>
<catName>ben_owner_type</catName>
</dataColumn>
</dataRow>
</data>
</grid>
</CYKResponse>
</CYKResponses>
</CYKDoc>
In the above XML, I have a tag of name "q_owner_type" which has multiple child nodes..but I am not able to fetch the codes of any tag at all..
My Query is :
SELECT CYK_id,q_id,code,catname
FROM ((SELECT p.CYK_id CYK_id,
EXTRACTVALUE (VALUE (tab1), 'dataColumn/id') q_id,
EXTRACTVALUE(VALUE(tab2), '/code') AS code,
EXTRACTVALUE (VALUE (tab1),
'dataColumn/catName'
) catname
FROM CYK.CYK_doc p,
TABLE
(XMLSEQUENCE
(EXTRACT
(p.CYK_doc, '/CYKDoc/CYKResponses/CYKResponse[id="q_grid"]/grid/data/dataRow[1]/dataColumn'))) tab1,
TABLE (XMLSEQUENCE (EXTRACT (VALUE (tab1), 'dataColumn/codes/code'))) tab2
)) a
WHERE a.CYK_id = 227209;
I get 4 rows having one question as q_grid_id and 3 questions as q_owner_type but the extractvalue( tab2) doesnt fetch the values of codes....
I am assuming that its an issue with XPATH I mentioned in alias tab2 but How else to handle it, is an issue here...
If In TABLE (XMLSEQUENCE (EXTRACT (VALUE (tab1), 'dataColumn/codes/code'))) tab2 I refer the path only till codes, it errors out with saying that "single row fetches multiple records" because there are multiple child records..
How to handle this??
Please HELP
Thanks
Mahesh