3 Replies Latest reply: Mar 26, 2013 3:42 AM by odie_63 RSS

    Fetch 1-n relation data from XML Data using XMLTable

    Hari_639
      Dear All,

      Following query is running fine.
      with t as (select XMLType('<env:Envelope xmlns:env="http://www.w3.org/2003/05/soap-envelope">
         <env:Header/>
         <env:Body>
            <nm:CustomerCRMByIDResponse xmlns:nm="http://sap.com/xi/CRM/Global2" xmlns:prx="urn:sap.com:proxy:DCT:/1SAI/TAS57DF0B317943DEAE3C49:702">
               <MessageHeader/>
               <BusinessPartner>
                  <InternalID>2200117598</InternalID>            
                  <AddressInformation>
                     <UUID>51471396-9ae8-3cc0-e100-80000a031a28</UUID>
                                          <DefaultIndicator>true</DefaultIndicator>
                     <Address>
                        <PostalAddress>
                           <CountryCode>DE</CountryCode>
                           <CountryName>Country Name</CountryName>
                        </PostalAddress>
                        <Telephone>
                           <Number>
                              <SubscriberID>0711/123456</SubscriberID>
                              <ExtensionID>0</ExtensionID>
                              <CountryCode>DE</CountryCode>
                              <CountryDiallingCode>+49</CountryDiallingCode>
                              <CountryName languageCode="de">Country Name</CountryName>
                           </Number>
                           <UsageDeniedIndicator>false</UsageDeniedIndicator>
                           <MobilePhoneNumberIndicator>false</MobilePhoneNumberIndicator>
                           <SMSEnabledIndicator>false</SMSEnabledIndicator>
                           <DefaultIndicator>true</DefaultIndicator>
                        </Telephone>
                        <Telephone>
                           <Number>
                              <SubscriberID>0711/999999</SubscriberID>
                              <CountryCode>DE</CountryCode>
                              <CountryDiallingCode>+49</CountryDiallingCode>
                              <CountryName languageCode="de">Country Name</CountryName>
                           </Number>
                           <UsageDeniedIndicator>false</UsageDeniedIndicator>
                           <MobilePhoneNumberIndicator>true</MobilePhoneNumberIndicator>
                           <SMSEnabledIndicator>true</SMSEnabledIndicator>
                           <DefaultIndicator>false</DefaultIndicator>
                        </Telephone>
                        <Facsimile>
                           <Number>
                              <SubscriberID>0711/999888</SubscriberID>
                              <ExtensionID>99</ExtensionID>
                              <CountryCode>DE</CountryCode>
                              <CountryDiallingCode>+49</CountryDiallingCode>
                              <CountryName languageCode="de">Country Name</CountryName>
                           </Number>
                           <UsageDeniedIndicator>false</UsageDeniedIndicator>
                           <DefaultIndicator>true</DefaultIndicator>
                        </Facsimile>
                        <EMail>
                           <URI>info@xyz.com</URI>
                           <UsageDeniedIndicator>false</UsageDeniedIndicator>
                           <DefaultIndicator>true</DefaultIndicator>
                        </EMail>
                        <EMail>
                           <URI>contact@xyz.com</URI>
                           <UsageDeniedIndicator>false</UsageDeniedIndicator>
                           <DefaultIndicator>false</DefaultIndicator>
                        </EMail>
                        <Web>
                           <URI>www.xyz.com</URI>
                           <UsageDeniedIndicator>false</UsageDeniedIndicator>
                           <DefaultIndicator>true</DefaultIndicator>
                        </Web>
                     </Address>
                  </AddressInformation>
                  <AddressInformation>
                     <UUID>514a519b-39a2-4890-e100-80000a031a28</UUID>
                                          <DefaultIndicator>false</DefaultIndicator>
                     <Address>
                        <PostalAddress>
                           <CountryCode>AT</CountryCode>
                           <CountryName>Österreich</CountryName>
                        </PostalAddress>
                     </Address>
                  </AddressInformation>
               </BusinessPartner>
            </nm:CustomerCRMByIDResponse>
         </env:Body>
      </env:Envelope>') xml_data from dual)
      SELECT xmlresponse.*
      FROM t, XMLTable(Xmlnamespaces('http://www.w3.org/2003/05/soap-envelope' AS "env",
                                                                        'http://sap.com/xi/CRM/Global2' AS "nm",
                                                                        'urn:sap.com:proxy:DCT:/1SAI/TAS57DF0B317943DEAE3C49:702' AS "prx"
                                                                        ),
                                              'for $BusinessPartner in /env:Envelope/env:Body/nm:CustomerCRMByIDResponse/BusinessPartner                  
                          return $BusinessPartner'
                                              PASSING xml_data
                                              COLUMNS
                                              Internalid Varchar2(4000) Path 'InternalID'  
                                              ) xmlresponse;
      As you can see, one "BusinessPartner" can have multiple "AddressInformation"
      and one "AddressInformation" can have multiple "Telephone".

      Can someone suggest me how can I extract both InternalID & UUID in one query? For above example output should look as follows..
      InternalID UUID 
      2200117598 51471396-9ae8-3cc0-e100-80000a031a28
      2200117598 514a519b-39a2-4890-e100-80000a031a28
      Thank you very much in advance.

      Regards,
      Hari
        • 1. Re: Fetch 1-n relation data from XML Data using XMLTable
          Jason_(A_Non)
          Here is a basic example of one method to achieve what you need
          SELECT xmlresponse.Internalid, xml2.uuid
            FROM t, 
                 XMLTable(Xmlnamespaces('http://www.w3.org/2003/05/soap-envelope' AS "env",
                                        'http://sap.com/xi/CRM/Global2' AS "nm"
                                       ),
                          '/env:Envelope/env:Body/nm:CustomerCRMByIDResponse/BusinessPartner'
                          PASSING xml_data
                          COLUMNS
                          Internalid   Varchar2(20) Path 'InternalID',
                          addrinfoxml  XMLType      PATH 'AddressInformation'
                         ) xmlresponse,
                 XMLTable('/AddressInformation'
                          PASSING xmlresponse.addrinfoxml
                          COLUMNS
                          UUID         Varchar2(80) Path 'UUID'
                         ) xml2;
          Changes:
          I removed one of namespaces as you only need to include those included in an XPath statement.
          I shortened your datatypes.
          I went for the simple XMLTable joined to an XMLTable approach, instead of a single XMLTable using a FLWOR statement.
          You can include the addrinfoxml column to your SELECT list to see the data being passed between the two if you want.

          Addition:
          Here's one approach for a single XMLTable.
          SELECT xmlresponse.*
            FROM t, 
                 XMLTable(Xmlnamespaces('http://www.w3.org/2003/05/soap-envelope' AS "env",
                                        'http://sap.com/xi/CRM/Global2' AS "nm"
                                       ),
                          'for $BP in /env:Envelope/env:Body/nm:CustomerCRMByIDResponse/BusinessPartner
                            for $ai in $BP /AddressInformation
                             return <e>{$BP/InternalID}{$ai/UUID}</e>'
                          PASSING xml_data
                          COLUMNS
                          Internalid   Varchar2(20) Path 'InternalID',
                          UUID         Varchar2(80) Path 'UUID'
                         ) xmlresponse;
          Edited by: A_Non on Mar 25, 2013 9:41 AM
          Added in XQuery solution
          • 2. Re: Fetch 1-n relation data from XML Data using XMLTable
            Hari_639
            Hello Jason,

            Thank you, both the approaches works fine.
            for $BP in /env:Envelope/env:Body/nm:CustomerCRMByIDResponse/BusinessPartner
            for $ai in $BP /AddressInformation
            return <e>{$BP/InternalID}{$ai/UUID}</e>
            If I understood correctly, two "for" are similar to cascading "for" loops in PL/SQL. So I think "return" will return following XML in first iteration.
            <e><InternalID>2200117598</InternalID><UUID>51471396-9ae8-3cc0-e100-80000a031a28</UUID></e>
            and similar XML block in second iteration.

            So we should refer path as 'e/InternalID' and 'e/UUID'? But you have not specified 'e'? Can you please explain how it works?

            Best Regards,
            Hari

            Edited by: Hari_639 on Mar 26, 2013 10:31 AM
            • 3. Re: Fetch 1-n relation data from XML Data using XMLTable
              odie_63
              So we should refer path as 'e/InternalID' and 'e/UUID'? But you have not specified 'e'? Can you please explain how it works?
              The XQuery expression in the PATH clause is relative to the context item passed from the main XQuery expression.
              So, implicitly
              PATH 'InternalID'
              is actually evaluated as :
              PATH '/e/InternalID'
              If you use 'e/InternalID', it'll be evaluated as '/e/e/InternalID' which is not correct.