2 Replies Latest reply: May 10, 2010 10:47 AM by Donatello Settembrino RSS

    EXTRACTVALUE + MULTIPLE CHILD NODES

    770398
      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
        • 1. Re: EXTRACTVALUE + MULTIPLE CHILD NODES
          770398
          Hi,

          Is there no way to handle this?

          I am sort of running out of time, but not able to think of any solution for this.. There must be a way to do so..

          I found the below link while surfing :

          http://www.componentworkshop.com/blog/2009/07/21/advanced-oracle-parsing-xml-fragments-in-oracle-functions-and-procedures

          I think this link has some answer to my solution in terms of joins but I managed to find no solution to it.

          <EmailAddresses>
          <EmailAddress>hubert@pretend.com</EmailAddress>
          <EmailAddress>marjory@acme.com</EmailAddress>
          <EmailAddress>kenneth@fish.net</EmailAddress>
          </EmailAddresses>

          How do i extract the EmailAddress from the parent tag ?? while using the XPath to extract the values..??

          Please let me know.

          Thanks in advance
          • 2. Re: EXTRACTVALUE + MULTIPLE CHILD NODES
            Donatello Settembrino
            I do not know if I understood correctly, but if you want to extract all email addresses
            try to follow this suggestion

            SELECT VALUE(p) AS elemento
            FROM (SELECT XMLTYPE('<EmailAddresses><EmailAddress> Hubert@pretend.com </EmailAddress><EmailAddress> Marjory@acme.com </EmailAddress><EmailAddress> Kenneth@fish.net </EmailAddress></EmailAddresses>') AS cxml FROM dual) t ,
            TABLE(xmlsequence(extract(cxml, '/EmailAddresses/EmailAddress'))) p

            replacing

            (SELECT XMLTYPE('<EmailAddresses><EmailAddress> Hubert@pretend.com </EmailAddress><EmailAddress> Marjory@acme.com </EmailAddress><EmailAddress> Kenneth@fish.net </EmailAddress></EmailAddresses>') AS cxml FROM dual) t

            with your table

            Regards

            Donatello Settembrino