This discussion is archived
11 Replies Latest reply: Jan 24, 2013 1:11 AM by odie_63 RSS

Need help to get multiple child in xml

977256 Newbie
Currently Being Moderated
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) Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Odie!!

    Many thanks man!!!

    You made the day :)
  • 4. Re: Need help to get multiple child in xml
    Jason_(A_Non) Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    I was wondering, does the explain plan really show one pass...?
  • 7. Re: Need help to get multiple child in xml
    odie_63 Guru
    Currently Being Moderated
    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
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    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
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    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
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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).

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points