1 Reply Latest reply: Jun 20, 2012 10:00 PM by 942747 RSS

    XML single row function question.

    942747
      Here is a part of my code .....
      SELECT XMLAGG(XMLELEMENT("CaseData",
                               XMLELEMENT("Case_seq_group_ID",t2.CASE_SEQ_GROUP_ID),
                               XMLELEMENT("Census",
                        ( SELECT XMLAGG(XMLELEMENT("Subscriber",
                                                   XMLELEMENT("SubID",t3.SubID),
                                                   XMLELEMENT("Status",t3.Status),
                                ( SELECT               XMLELEMENT("Dependents",
                                                                       XMLAGG(   XMLELEMENT("Dependent",
                                                                                      XMLELEMENT("DepID",t4.DepID),
                                                                                      XMLELEMENT("Status",t4.Status)
                                                                                            ) 
                                                                               ) )                  
                                                                  FROM hap_renewal_temp_xml4 t4
                                                                 WHERE t4.SubID = t3.SubID 
                                                                    
                                                                    )   
                                                              
                                                                 ) )
                          FROM hap_renewal_temp_xml3 t3
                         WHERE t3.Case_Seq_Group_ID = t2.Case_Seq_Group_ID 
                            
            
             ) 
          )  ) ).getClobVal()
      FROM hap_renewal_temp_xml2 t2;
      The t2 table has case and its details ... t3 has subscriber details and t4 has dependent details ...
      One case can have multiple subscribers and a subscriber can have multiple dependents or no dependents at all .....

      My question is if there are no dependents under the subscribers, it should not display the dependents node at all ... I am able to put a case statement around the dependents node that it should only be displayed when there are no dependents but it gives me not a single-row function error ...
      SELECT XMLAGG(XMLELEMENT("CaseData",
                               XMLELEMENT("Case_seq_group_ID",t2.CASE_SEQ_GROUP_ID),
                               XMLELEMENT("Census",
                        ( SELECT XMLAGG(XMLELEMENT("Subscriber",
                                                   XMLELEMENT("SubID",t3.SubID),
                                                   XMLELEMENT("Status",t3.Status),
                                ( SELECT      CASE WHEN t4.DepID IS NOT NULL THEN             XMLELEMENT("Dependents",
                                                                       XMLAGG(   XMLELEMENT("Dependent",
                                                                                      XMLELEMENT("DepID",t4.DepID),
                                                                                      XMLELEMENT("Status",t4.Status)
                                                                                            ) 
                                                                               ) )                   END
                                                                  FROM hap_renewal_temp_xml4 t4
                                                                 WHERE t4.SubID = t3.SubID 
                                                                    
                                                                    )   
                                                              
                                                                 ) )
                          FROM hap_renewal_temp_xml3 t3
                         WHERE t3.Case_Seq_Group_ID = t2.Case_Seq_Group_ID 
                            
            
             ) 
          )  ) ).getClobVal()
      FROM hap_renewal_temp_xml2 t2;
      can you please tell me how to handle this ....

      Thanks
      Divya
        • 1. Re: XML single row function question.
          942747
          Never mind I got it ... Thanks !
          SELECT XMLAGG(XMLELEMENT("CaseData",
                                   XMLELEMENT("Case_seq_group_ID",t2.CASE_SEQ_GROUP_ID),
                                   XMLELEMENT("Census",
                            ( SELECT XMLAGG(XMLELEMENT("Subscriber",
                                                       XMLELEMENT("SubID",t3.SubID),
                                                       XMLELEMENT("Status",t3.Status),
                                    ( SELECT  CASE WHEN count(*) != 0 THEN      XMLELEMENT("Dependents",
                                                                       
                                                                           XMLAGG(   XMLELEMENT("Dependent",
                                                                                          
                                                                                          XMLELEMENT("DepID",t4.DepID),
                                                                                          XMLELEMENT("Status",t4.Status)
                                                                                                ) 
                                                                                   )  )                 
                                                                                    END
                                                                      FROM hap_renewal_temp_xml4 t4
                                                                     WHERE t4.SubID = t3.SubID 
                                                                      
                                                                        
                                                                        )   
                                                                  
                                                                     ) )
                              FROM hap_renewal_temp_xml3 t3
                             WHERE t3.Case_Seq_Group_ID = t2.Case_Seq_Group_ID 
                               
                                
                
                 ) 
              )  ) ).getClobVal()
          FROM hap_renewal_temp_xml2 t2;