11 Replies Latest reply: Jan 24, 2013 3:11 AM by odie_63 RSS

    Need help to get multiple child in xml

    977256
      Hi Odie,

      Many thanks for helping me last time

      Please help for namespace this time too . I am using below query to get some multiple chile tag values but I am getting no rows.

      Please let me know where I am doing wrong..
      desc ss_customer
      Name                           Null     Type                                                                                                                                                                                          
      ------------------------------ -------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
      SEQ_NUM                        NOT NULL NUMBER                                                                                                                                                                                        
      EVENT_TYPE                     NOT NULL VARCHAR2(50 CHAR)                                                                                                                                                                             
      XML_DATA                       NOT NULL CLOB()                                                                                                                                                                                        
                                                                                                                                                                                 
      
      
      my query
      ========
      
       select x1.mdm_customer_id
            , x1.Affiliated_customer_id
            , x2.Role
       from ss_customer t
          , xmltable(
              xmlnamespaces(
                'urn:astrazeneca:commercial:Customer:services:CustomerPubSubServiceInitiatior:1' as "init"
              , 'urn:astrazeneca:commercial:Customer:PubSub:domain:1' as "ns0"
              , 'urn:astrazeneca:commercial:CommonTypes:domain:5' as "ns1"
              )
            , '/ns0:PersonType/ns1:MasterID'
              passing xmltype(t.xml_data)
              columns
                mdm_customer_id        varchar2(30) path 'ns0:PersonType/ns1:MasterID'
              , Affiliated_customer_id varchar2(10)  path 'nso:AffiliatedAZMasterCustomerID'
              , Role       xmltype       path 'ns0:Role'
            ) x1
          , xmltable(
              xmlnamespaces(
                'urn:astrazeneca:commercial:Customer:PubSub:domain:1' as "ns0"
              )
            , 'for $i in /ns0:Role/child::*
               return element r {
                 element Role    {local-name($i)}
               }'
              passing x1.Role
              columns
                Role     varchar2(30)  path 'Role'
               ) x2
      
      
      
      Output: no rows return
      
      Expected output:
      ===============
      
      MDM_CUSTOMER_ID AFFILIATED_CUSTOMER_ID ROLE                                               
                                  
      MDM9652         MDM9634                Lector                                             
      MDM9652         MDM9634                Expert veterinarian                                
      
      2 rows selected
      
      
      
      
      
      Raw xml
      ========
      <urn:CustomerUpdated xmlns:urn="urn:astrazeneca:commercial:Customer:services:CustomerPubSubServiceInitiatior:1">
           <urn1:MarketingCompany xmlns:urn1="urn:astrazeneca:commercial:Customer:PubSub:domain:1">CROA</urn1:MarketingCompany>
           <urn1:CustomerClass xmlns:urn1="urn:astrazeneca:commercial:Customer:PubSub:domain:1">HCP</urn1:CustomerClass>
           <urn1:AZCustomerId xmlns:urn1="urn:astrazeneca:commercial:Customer:PubSub:domain:1">MDM9652</urn1:AZCustomerId>
           <urn1:UpdateMetadata xmlns:urn1="urn:astrazeneca:commercial:Customer:PubSub:domain:1">
                <urn1:AttributeUpdateMetadata>
                     <urn1:UpdateType>Update</urn1:UpdateType>
                     <urn1:FieldName>HCP/CustomerStatusReason</urn1:FieldName>
                </urn1:AttributeUpdateMetadata>
           </urn1:UpdateMetadata>
           <urn1:UpdateMetadata xmlns:urn1="urn:astrazeneca:commercial:Customer:PubSub:domain:1">
                <urn1:ElementUpdateMetadata>
                     <urn1:UpdateType>Add</urn1:UpdateType>
                     <urn1:FieldName>HCP/Affiliations/Affiliation/Role</urn1:FieldName>
                     <urn1:EntityNewToMDM>true</urn1:EntityNewToMDM>
                </urn1:ElementUpdateMetadata>
           </urn1:UpdateMetadata>
           <urn:HCP>
                <urn1:ValidationPendingIndicator xmlns:urn1="urn:astrazeneca:commercial:Customer:domain:4">false</urn1:ValidationPendingIndicator>
                <urn1:CustomerClass xmlns:urn1="urn:astrazeneca:commercial:Customer:domain:4">HCP</urn1:CustomerClass>
                <urn1:CustomerStatus xmlns:urn1="urn:astrazeneca:commercial:Customer:domain:4">ACTI</urn1:CustomerStatus>
                <urn1:CustomerStatusReason xmlns:urn1="urn:astrazeneca:commercial:Customer:domain:4">PRDE</urn1:CustomerStatusReason>
                <urn1:PostalContactInfo xmlns:urn1="urn:astrazeneca:commercial:Customer:domain:4">
                     <urn1:PostalContact>
                          <urn1:Comments/>
                          <urn1:MasterAddressID>2402          </urn1:MasterAddressID>
                          <urn1:SystemAddressID SystemCode="SRA">ADDR_WS_TEST_04</urn1:SystemAddressID>
                          <urn1:PostalInfo>
                               <urn2:AddressLine xmlns:urn2="urn:astrazeneca:commercial:CommonTypes:domain:5" LineNo="1">LUCKO 14/B</urn2:AddressLine>
                               <urn2:City xmlns:urn2="urn:astrazeneca:commercial:CommonTypes:domain:5">Macclesfield</urn2:City>
                               <urn2:CityTier xmlns:urn2="urn:astrazeneca:commercial:CommonTypes:domain:5">FAGERFJÄLL</urn2:CityTier>
                               <urn2:District xmlns:urn2="urn:astrazeneca:commercial:CommonTypes:domain:5">Cheshire</urn2:District>
                               <urn2:StateProvince xmlns:urn2="urn:astrazeneca:commercial:CommonTypes:domain:5">West</urn2:StateProvince>
                               <urn2:Region xmlns:urn2="urn:astrazeneca:commercial:CommonTypes:domain:5">North West</urn2:Region>
                               <urn2:PostalCode xmlns:urn2="urn:astrazeneca:commercial:CommonTypes:domain:5">SK11 8JR</urn2:PostalCode>
                               <urn2:GeographicCode xmlns:urn2="urn:astrazeneca:commercial:CommonTypes:domain:5">NA</urn2:GeographicCode>
                               <urn2:Country xmlns:urn2="urn:astrazeneca:commercial:CommonTypes:domain:5">GB</urn2:Country>
                               <urn2:MiniBrickName xmlns:urn2="urn:astrazeneca:commercial:CommonTypes:domain:5">NA</urn2:MiniBrickName>
                               <urn2:Status xmlns:urn2="urn:astrazeneca:commercial:CommonTypes:domain:5">ACTI</urn2:Status>
                          </urn1:PostalInfo>
                          <urn1:AddressType>BUSI</urn1:AddressType>
                          <urn1:PrimaryAddressIndicator>false</urn1:PrimaryAddressIndicator>
                          <urn1:Comments/>
                          <urn1:ActiveFlag>true</urn1:ActiveFlag>
                     </urn1:PostalContact>
                </urn1:PostalContactInfo>
                <urn1:TelephonicContactInfo xmlns:urn1="urn:astrazeneca:commercial:Customer:domain:4"/>
                <urn1:DigitalContactInfo xmlns:urn1="urn:astrazeneca:commercial:Customer:domain:4"/>
                <urn1:Affiliations xmlns:urn1="urn:astrazeneca:commercial:Customer:domain:4">
                     <urn1:Affiliation>
                          <urn1:AffiliatedCustomerID SystemCode="SRA SYS0">HCA_WSTEST_5 9634</urn1:AffiliatedCustomerID>
                          <urn1:AffiliatedAZMasterCustomerID>MDM9634</urn1:AffiliatedAZMasterCustomerID>
                          <urn1:PrimaryAffiliation>true</urn1:PrimaryAffiliation>
                          <urn1:StartDate>1999-02-01</urn1:StartDate>
                          <urn1:EndDate>2019-09-07</urn1:EndDate>
                          <urn1:AffiliationType>HCP_TO_HCA</urn1:AffiliationType>
                          <urn1:AffiliationSubType>Person</urn1:AffiliationSubType>
                          <urn1:Status>ACTI</urn1:Status>
                          <urn1:Role>Lector</urn1:Role>
                          <urn1:Role>Expert veterinarian</urn1:Role>
                          <urn1:AZRole>Doctor</urn1:AZRole>
                          <urn1:GroupRole>OTH</urn1:GroupRole>
                          <urn1:GroupFunction>CHCO</urn1:GroupFunction>
                          <urn1:InfluenceType>COAC</urn1:InfluenceType>
                          <urn1:Brand>Merck</urn1:Brand>
                     </urn1:Affiliation>
                </urn1:Affiliations>
                <urn1:InternalCustomer xmlns:urn1="urn:astrazeneca:commercial:Customer:domain:4">true</urn1:InternalCustomer>
                <urn1:DataPrivacyConsentIndicator xmlns:urn1="urn:astrazeneca:commercial:Customer:domain:4">true</urn1:DataPrivacyConsentIndicator>
                <urn1:PersonType xmlns:urn1="urn:astrazeneca:commercial:Customer:domain:4">
                     <urn2:MasterID xmlns:urn2="urn:astrazeneca:commercial:Party:domain:3">MDM9652</urn2:MasterID>
                     <urn2:SystemID xmlns:urn2="urn:astrazeneca:commercial:Party:domain:3" SystemCode="SRA">HCP_WSTEST_9</urn2:SystemID>
                     <urn2:SystemID xmlns:urn2="urn:astrazeneca:commercial:Party:domain:3" SystemCode="SYS0">9652</urn2:SystemID>
                     <urn2:References xmlns:urn2="urn:astrazeneca:commercial:Party:domain:3"/>
                     <urn2:Name xmlns:urn2="urn:astrazeneca:commercial:Party:domain:3">
                          <urn2:FirstName>TONY</urn2:FirstName>
                          <urn2:LastName>WALKER</urn2:LastName>
                          <urn2:Suffix>NA</urn2:Suffix>
                          <urn2:Salutation>Dr.</urn2:Salutation>
                     </urn2:Name>
                     <urn2:PersonalInfo xmlns:urn2="urn:astrazeneca:commercial:Party:domain:3">
                          <urn2:BirthInfo>
                               <urn2:BirthDate>1979-05-12</urn2:BirthDate>
                               <urn2:Gender>1</urn2:Gender>
                          </urn2:BirthInfo>
                          <urn2:PrimaryLanguage>eng</urn2:PrimaryLanguage>
                     </urn2:PersonalInfo>
                </urn1:PersonType>
                <urn1:ProfessionalInfo xmlns:urn1="urn:astrazeneca:commercial:Customer:domain:4">
                     <urn1:Titles>
                          <urn1:Title TransactionCode="" CreatedOnDate="" UpdatedOnDate="" SourceCreatedByID="" SourceCreatedByFirstName="" SourceCreatedByLastName="" SourceCreatedByRole="" SourceUpdatedByID="" SourceUpdatedByFirstName="" SourceUpdatedByLastName="" SourceUpdatedByRole="" SourceExtractTimeStamp=""/>
                     </urn1:Titles>
                     <urn1:Specialities>
                          <urn1:Specialties>
                               <urn1:PrimarySpecialtyIndicator>true</urn1:PrimarySpecialtyIndicator>
                               <urn1:Specialty>
                                    <urn1:SpecialtyType>FISIATRIA</urn1:SpecialtyType>
                                    <urn1:SpecialtyCode>TOXI</urn1:SpecialtyCode>
                                    <urn1:SpecialtyStatus>ACTI</urn1:SpecialtyStatus>
                               </urn1:Specialty>
                          </urn1:Specialties>
                     </urn1:Specialities>
                     <urn1:KeyOpinionLeadIndicator>true</urn1:KeyOpinionLeadIndicator>
                     <urn1:SpeakerIndicator>true</urn1:SpeakerIndicator>
                     <urn1:PrescriberIndicator>true</urn1:PrescriberIndicator>
                </urn1:ProfessionalInfo>
                <urn1:Availability xmlns:urn1="urn:astrazeneca:commercial:Customer:domain:4">Agenda</urn1:Availability>
                <urn1:CallCenterPriority xmlns:urn1="urn:astrazeneca:commercial:Customer:domain:4">2</urn1:CallCenterPriority>
                <urn1:HCPCustomerType xmlns:urn1="urn:astrazeneca:commercial:Customer:domain:4">ACDE</urn1:HCPCustomerType>
                <urn1:HCPCustomerSubType xmlns:urn1="urn:astrazeneca:commercial:Customer:domain:4"/>
           </urn:HCP>
      </urn:CustomerUpdated>
      =============
      
      
      Oracle version
      
      Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
      PL/SQL Release 10.2.0.5.0 - Production
      "CORE     10.2.0.5.0     Production"
      TNS for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Productio
      NLSRTL Version 10.2.0.5.0 - Production
        • 1. Re: Need help to get multiple child in xml
          Jason_(A_Non)
          Here is a way to accomplish the task without using XQuery within the XMLTable commands.
           select x2.mdm_customer_id
                , x3.Affiliated_customer_id
                , x4.Role
           from ss_customer t
              , xmltable(
                  xmlnamespaces(
                    'urn:astrazeneca:commercial:Customer:services:CustomerPubSubServiceInitiatior:1' as "init"
                  , 'urn:astrazeneca:commercial:Customer:domain:4' as "ns4"
                  )
                , '/init:CustomerUpdated/init:HCP'
                  passing xmltype(t.xml_data)
                  columns
                    affili_xml             XMLType PATH 'ns4:Affiliations/ns4:Affiliation',
                    person_xml             XMLType PATH 'ns4:PersonType'
                ) x1
              , xmltable(
                  xmlnamespaces(
                    'urn:astrazeneca:commercial:Party:domain:3' as "ns3"
                  , 'urn:astrazeneca:commercial:Customer:domain:4' as "ns4"
                  )
                , '/ns4:PersonType'
                  passing x1.person_xml
                  columns
                    mdm_customer_id     varchar2(30)  path 'ns3:MasterID'
                   ) x2
              , xmltable(
                  xmlnamespaces(
                    'urn:astrazeneca:commercial:Customer:domain:4' as "ns4"
                  )
                , '/ns4:Affiliation'
                  passing x1.affili_xml
                  columns
                    Affiliated_customer_id varchar2(30) path 'ns4:AffiliatedAZMasterCustomerID'
                  , Role_xml               xmltype      path 'ns4:Role'
                   ) x3
              , xmltable(
                  xmlnamespaces(
                    'urn:astrazeneca:commercial:Customer:domain:4' as "ns4"
                  )
                , '/ns4:Role'
                  passing x3.Role_xml
                  columns
                    Role               varchar2(30)      path '.'
                   ) x4
          I know a better shorter version exists that uses XQuery but I started with this version. It assumes one Person with multiple Affiliation, each of which can have multiple Role in the XML.
          • 2. Re: Need help to get multiple child in xml
            odie_63
            SQL> select x.*
              2  from ss_customer t
              3     , xmltable(
              4         xmlnamespaces(
              5           'urn:astrazeneca:commercial:Customer:services:CustomerPubSubServiceInitiatior:1' as "init"
              6         , 'urn:astrazeneca:commercial:Customer:domain:4' as "ns0"
              7         , 'urn:astrazeneca:commercial:Party:domain:3' as "ns1"
              8         )
              9       , 'for $i in /init:CustomerUpdated/init:HCP/ns0:PersonType
             10            , $j in /init:CustomerUpdated/init:HCP/ns0:Affiliations/ns0:Affiliation
             11            , $k in $j/ns0:Role
             12          return element r {
             13            $i/ns1:MasterID
             14          , $j/ns0:AffiliatedAZMasterCustomerID
             15          , $k
             16          }'
             17         passing xmltype(t.xml_data)
             18         columns mdm_customer_id          varchar2(30) path 'ns1:MasterID'
             19               , affiliated_customer_id   varchar2(10) path 'ns0:AffiliatedAZMasterCustomerID'
             20               , role                     varchar2(30) path 'ns0:Role'
             21       ) x ;
             
            MDM_CUSTOMER_ID                AFFILIATED_CUSTOMER_ID ROLE
            ------------------------------ ---------------------- ------------------------------
            MDM9652                        MDM9634                Lector
            MDM9652                        MDM9634                Expert veterinarian
             
            ;)
            • 3. Re: Need help to get multiple child in xml
              977256
              Odie!!

              Many thanks man!!!

              You made the day :)
              • 4. Re: Need help to get multiple child in xml
                Jason_(A_Non)
                Even shorter than I was expecting as I didn't realize you could do $i and $j like that together in one pass.
                • 5. Re: Need help to get multiple child in xml
                  odie_63
                  974253 wrote:
                  Odie!!

                  Many thanks man!!!

                  You made the day :)
                  You're welcome.

                  I suggest you pay attention to Jason's version too.
                  His detailed approach should help you understand the concepts more clearly and understand where you've gone wrong.
                  • 6. Re: Need help to get multiple child in xml
                    Marco Gralike
                    I was wondering, does the explain plan really show one pass...?
                    • 7. Re: Need help to get multiple child in xml
                      odie_63
                      I can't test on OP's version (10.2.0.5) but on my 11g XE I get the following :

                      w/o hint
                      Connected to:
                      Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
                      
                      SQL> set autotrace traceonly explain
                      SQL> set lines 200
                      SQL> select x.*
                        2  from ss_customer t
                        3     , xmltable(
                        4         xmlnamespaces(
                        5           'urn:astrazeneca:commercial:Customer:services:CustomerPubSubServiceInitiatior:1' as "init"
                        6         , 'urn:astrazeneca:commercial:Customer:domain:4' as "ns0"
                        7         , 'urn:astrazeneca:commercial:Party:domain:3' as "ns1"
                        8         )
                        9       , 'for $i in /init:CustomerUpdated/init:HCP/ns0:PersonType
                       10            , $j in /init:CustomerUpdated/init:HCP/ns0:Affiliations/ns0:Affiliation
                       11            , $k in $j/ns0:Role
                       12          return element r {
                       13            $i/ns1:MasterID
                       14          , $j/ns0:AffiliatedAZMasterCustomerID
                       15          , $k
                       16          }'
                       17         passing xmltype(t.xml_data)
                       18         columns mdm_customer_id          varchar2(30) path 'ns1:MasterID'
                       19               , affiliated_customer_id   varchar2(10) path 'ns0:AffiliatedAZMasterCustomerID'
                       20               , role                     varchar2(30) path 'ns0:Role'
                       21       ) x ;
                      
                      Execution Plan
                      ----------------------------------------------------------
                      Plan hash value: 1318728361
                      
                      ---------------------------------------------------------------------------------------------------------------
                      | Id  | Operation                            | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
                      ---------------------------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT                     |                        |   544G|    46T|  1813M  (1)|999:59:59 |
                      |   1 |  NESTED LOOPS                        |                        |   544G|    46T|  1813M  (1)|999:59:59 |
                      |   2 |   NESTED LOOPS                       |                        |    66M|  5789M|   221K  (1)| 00:44:24 |
                      |   3 |    NESTED LOOPS                      |                        |  8168 |   709K|    32   (0)| 00:00:01 |
                      |   4 |     TABLE ACCESS FULL                | SS_CUSTOMER            |     1 |    87 |     3   (0)| 00:00:01 |
                      |   5 |     COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |  8168 | 16336 |    29   (0)| 00:00:01 |
                      |   6 |    COLLECTION ITERATOR PICKLER FETCH | XMLSEQUENCEFROMXMLTYPE |  8168 | 16336 |    27   (0)| 00:00:01 |
                      |   7 |   COLLECTION ITERATOR PICKLER FETCH  | XMLSEQUENCEFROMXMLTYPE |  8168 | 16336 |    27   (0)| 00:00:01 |
                      ---------------------------------------------------------------------------------------------------------------
                      
                      Note
                      -----
                         - Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)
                      w/ hint
                      SQL> select /*+ no_xml_query_rewrite*/ x.*
                        2  from ss_customer t
                        3     , xmltable(
                        4         xmlnamespaces(
                        5           'urn:astrazeneca:commercial:Customer:services:CustomerPubSubServiceInitiatior:1' as "init"
                        6         , 'urn:astrazeneca:commercial:Customer:domain:4' as "ns0"
                        7         , 'urn:astrazeneca:commercial:Party:domain:3' as "ns1"
                        8         )
                        9       , 'for $i in /init:CustomerUpdated/init:HCP/ns0:PersonType
                       10            , $j in /init:CustomerUpdated/init:HCP/ns0:Affiliations/ns0:Affiliation
                       11            , $k in $j/ns0:Role
                       12          return element r {
                       13            $i/ns1:MasterID
                       14          , $j/ns0:AffiliatedAZMasterCustomerID
                       15          , $k
                       16          }'
                       17         passing xmltype(t.xml_data)
                       18         columns mdm_customer_id          varchar2(30) path 'ns1:MasterID'
                       19               , affiliated_customer_id   varchar2(10) path 'ns0:AffiliatedAZMasterCustomerID'
                       20               , role                     varchar2(30) path 'ns0:Role'
                       21       ) x ;
                      
                      Execution Plan
                      ----------------------------------------------------------
                      Plan hash value: 3861061647
                      
                      ------------------------------------------------------------------------------------------------------------
                      | Id  | Operation                          | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
                      ------------------------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT                   |                       |  8168 |   709K|    32   (0)| 00:00:01 |
                      |   1 |  NESTED LOOPS                      |                       |  8168 |   709K|    32   (0)| 00:00:01 |
                      |   2 |   TABLE ACCESS FULL                | SS_CUSTOMER           |     1 |    87 |     3   (0)| 00:00:01 |
                      |   3 |   COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE |  8168 | 16336 |    29   (0)| 00:00:01 |
                      ------------------------------------------------------------------------------------------------------------
                      
                      Note
                      -----
                         - Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)
                      
                      SQL>
                      • 8. Re: Need help to get multiple child in xml
                        Marco Gralike
                        I thought so (regarding expected outcome) although I don't remember when I noticed this behavior for the first time. The again maybe via Binary XML Securefile the balance could tip the other way (hopefully)

                        I am thinking that "ideally" Oracle maybe should maybe decide to do it all in memory anyway, for both mentioned situations, when it's concerning CLOBs and/or even maybe XMLType CLOBs (basicfile). There isn't that much performance gain to achieve, if there are no XMLIndexes in play.

                        Edited by: Marco Gralike on Jan 24, 2013 12:00 AM
                        • 9. Re: Need help to get multiple child in xml
                          Marco Gralike
                          Did you notice the "2 CBO cost difference" between the COLLECTION ITERATOR PICKLER FETCH loops. Interesting also to see that the initial XML/SEQUENCEFROMXMLTYPE and XQ/SEQUENCEFROMXMLTYPE are equal in cost. I wouldn't have expected that outcome beforehand.
                          • 10. Re: Need help to get multiple child in xml
                            Marco Gralike
                            Small question, thinking about COSTs, have you set the compatible database parameter to 11.2.0.2 or is it still the default value on your 11gR2 XE environment... (Thanks)
                            • 11. Re: Need help to get multiple child in xml
                              odie_63
                              Marco Gralike wrote:
                              Small question, thinking about COSTs, have you set the compatible database parameter to 11.2.0.2 or is it still the default value on your 11gR2 XE environment... (Thanks)
                              Still the default (11.2.0.0.0).