4 Replies Latest reply: May 9, 2013 7:12 AM by beta32c RSS

    Need to read parent tag data even if child fragment doesnot have data

    beta32c
      Hi, I am using Binary method of XML Storage,
      Here is my Oracle installation details
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      PL/SQL Release 11.2.0.3.0 - Production
      "CORE 11.2.0.3.0 Production"

      XML Structure Details ---> The provided sample xml has repetitive Party tag under which a PolicyNumber tag might be present, under the Party tag a AddressList may or not be present.
      I have 3 such Party tags, the first one has no PolicyNumber tag altough the AddressList tag and has multiple Adress under the same, the second Party tag has the PolicyNumber however it doesnot have the AdressList data in it, 3rd party tag is irrelevant for this issue.

      What i want to acheive ---> Even though there are no Address present for the PolicyNumber tag i want PolicyNumber to appear in my result set, since the PolicyNumber's Party tag doesnot have a Address tag i guess the result set is ignoring the PolicyNumber in the final result set.
      In a nut shell i need all these data i cannot miss the PolicyNumber neither the Address


      Please go through the XML and the subsequent query for further assitance

      Inserting a sample XML
      INSERT INTO XML_TEST
      VALUES
      (XMLTYPE('<?xml version="1.0"; ?>
      <workfile>
      <claimnumber>abcdefgh</claimnumber>
      <Party>
      <AutoInsurancePolicy>
      <AddressList>
      <Address>
      <Address1>data1 goes here</Address1>
      <Address2>data1 goes here</Address2>
      </Address>
      <Address>
      <Address1>data2 goes here</Address1>
      <Address2>data2 goes here</Address2>
      </Address>
      </AddressList>
      </AutoInsurancePolicy>
      </Party>
      <Party>
      <AutoInsurancePolicy>
      <PolicyNumber>123456</PolicyNumber>
      </AutoInsurancePolicy>
      </Party>
      <Party>
      <AutoInsurancePolicy>
      <ContactList>
      <ContactNumber>
      <Phone>917656723</Phone>
      </ContactNumber>
      </ContactList>
      </AutoInsurancePolicy>
      </Party>
      </workfile>
      ')
      );


      Query that i am using to extract CLAIM, PolicyNumber and Corresponding Addresses

      select
      m.CLAIM,
      n.PolicyNumber,
      l.ADDRESS_1,
      l.ADDRESS_2
      from XML_TEST,
      xmltable(
      '$WK/workfile' passing XMLDATA as "WK"
      columns
      CLAIM VARCHAR2 (4000) PATH 'claimnumber',
      Party_fragment XMLTYPE PATH 'Party'
      ) m,
      XMLTABLE(
      '$PY/Party' passing m.Party_fragment as "PY"
      columns
      PolicyNumber VARCHAR2 (4000) PATH 'AutoInsurancePolicy/PolicyNumber',
      Address_Fragment XMLTYPE PATH 'AutoInsurancePolicy/AddressList/Address'
      ) n,
      XMLTABLE(
      '$AD/Address' passing n.Address_Fragment as "AD"
      columns
      ADDRESS_1 VARCHAR2 (4000) PATH 'Address1',
      ADDRESS_2 VARCHAR2 (4000) PATH 'Address2'
      ) l;

      But this query returns no PolicyNumber, since the address under the Party node where the PolicyNumber is present does not have a corresponding Address value
      Heres the result set returned:-
      CLAIM POLICY_NUMBER Address1 Address2
      abcdefgh <NULL> data1 goes here data1 goes here
      abcdefgh <NULL> data2 goes here data2 goes here


      Is there a way to specify to the above query to fetch all the data even if its corresponding Address fragment does not have data against the tag.

      Result set that i want to acheive:-
      CLAIM POLICY_NUMBER Address1 Address2
      abcdefgh 123456 <NULL> <NULL>
      abcdefgh <NULL> data1 goes here data1 goes here
      abcdefgh <NULL> data2 goes here data2 goes here


      Regards,
      Arghyadip

      Edited by: beta32c on May 9, 2013 2:06 AM
        • 1. Re: Need to read parent tag data even if child fragment doesnot have data
          odie_63
          beta32c wrote:
          Is there a way to specify to the above query to fetch all the data even if its corresponding Address fragment does not have data against the tag.
          Use an outer join on the last XMLTable :
          select m.CLAIM, 
                 n.PolicyNumber, 
                 l.ADDRESS_1, 
                 l.ADDRESS_2 
          from XML_TEST, 
               xmltable( 
                 '$WK/workfile' passing XMLDATA as "WK" 
                 columns 
                   CLAIM VARCHAR2 (4000) PATH   'claimnumber', 
                   Party_fragment XMLTYPE PATH 'Party' 
               ) m, 
               XMLTABLE( 
                 '$PY/Party' passing m.Party_fragment as "PY" 
                 columns 
                   PolicyNumber VARCHAR2 (4000) PATH  'AutoInsurancePolicy/PolicyNumber', 
                   Address_Fragment XMLTYPE PATH 'AutoInsurancePolicy/AddressList/Address' 
               ) n, 
               XMLTABLE( 
                 '$AD/Address' passing n.Address_Fragment as "AD" 
                 columns 
                   ADDRESS_1 VARCHAR2 (4000) PATH 'Address1', 
                   ADDRESS_2 VARCHAR2 (4000) PATH 'Address2' 
               ) (+) l
          ; 
          • 2. Re: Need to read parent tag data even if child fragment doesnot have data
            beta32c
            That was a quick answer, thanks for your time

            Howver i think i am unable to express my problems correctly , let me try again i need both the policynumber and the address details in my result set

            the outer join fetches me the policy number but at the cost of the adress data , heres the result set which i got when i ran the outer join query

            abcdefgh     <null>     <null>     <null>
            abcdefgh     123456     <null>     <null>
            abcdefgh     <null>     <null>     <null>

            I want something like, i dont mind if the corss join results more thatn 3 rows but i need all the data so that i can summarize them into distinct rows with data

            abcdefgh     <null>     data1 goes here     data1 goes here
            abcdefgh     123456     <null>     <null>
            abcdefgh     <null>     data2 goes here     data2 goes here
            • 3. Re: Need to read parent tag data even if child fragment doesnot have data
              odie_63
              beta32c wrote:
              Howver i think i am unable to express my problems correctly , let me try again i need both the policynumber and the address details in my result set
              No problem, you expressed your problem correctly, and the query I've suggested should have worked.
              However, there seems to have a bug when the outer join is only used at the deepest level.

              Try with outer joins at every level :
              SQL> select m.CLAIM,
                2         n.PolicyNumber,
                3         l.ADDRESS_1,
                4         l.ADDRESS_2
                5  from XML_TEST,
                6       xmltable(
                7         '$WK/workfile' passing XMLDATA as "WK"
                8         columns
                9           CLAIM VARCHAR2 (4000) PATH   'claimnumber',
               10           Party_fragment XMLTYPE PATH 'Party'
               11       ) (+) m,
               12       XMLTABLE(
               13         '$PY/Party' passing m.Party_fragment as "PY"
               14         columns
               15           PolicyNumber VARCHAR2 (4000) PATH  'AutoInsurancePolicy/PolicyNumber',
               16           Address_Fragment XMLTYPE PATH 'AutoInsurancePolicy/AddressList/Address'
               17       ) (+) n,
               18       XMLTABLE(
               19         '$AD/Address' passing n.Address_Fragment as "AD"
               20         columns
               21           ADDRESS_1 VARCHAR2 (4000) PATH 'Address1',
               22           ADDRESS_2 VARCHAR2 (4000) PATH 'Address2'
               23       ) (+) l
               24  ;
               
              CLAIM        POLICYNUMBER    ADDRESS_1          ADDRESS_2
              ------------ --------------- ------------------ ------------------
              abcdefgh                     data1 goes here    data1 goes here
              abcdefgh                     data2 goes here    data2 goes here
              abcdefgh     123456                             
              abcdefgh                                        
               
              • 4. Re: Need to read parent tag data even if child fragment doesnot have data
                beta32c
                Exactly what i was looking for, thanks a ton!