This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Jan 23, 2013 10:14 AM by Jason_(A_Non) RSS

multiple xml tag values are not getting in output.

977256 Newbie
Currently Being Moderated
I am using xml file,of which tag ("SourceValues") has 5 values. when im using
the below query I am getting null value for the tag which has multiple value and overall
I am getting 2 record with null in the tag which has multiple value.

but excpected result is : there should be total record as we have multiple records.

Can anybody help me in this. I am attaching the xml which I am getting in my source table (ss_ac_activity_interaction)
current_output:

ACTIVITY_ID   SOURCE_SYSTEM_CODE   SOURCE_VALUE_NAME    SOURCE_VALUE_CONTENT   FILE_ID
AQLA-17K3EA     STA          null                          null                                  311
AQLA-17K3E6     STA          null                          null                                 311




expected output:


ACTIVITY_ID  SOURCE_SYSTEM_CODE     SOURCE_VALUE_NAME     SOURCE_VALUE_CONTENT FILE_I
AQLA-17K3EA   STA          CLASSIFICATION1          Product Messages                            311
AQLA-17K3EA   STA          CLASSIFICATION2          Call                      311
AQLA-17K3EA   STA          CLASSIFICATION3          Service Interaction                            311
AQLA-17K3EA   STA          CLASSIFICATION4          Manager Attended                            311
AQLA-17K3EA   STA          STATUS               Submitted                                 311
AQLA-17K3EA   STA          COMMUNICATIONMODE     Face to Face                 311
AQLA-17K3E    STA          CLASSIFICATION1          Promotional Items Dropped311
AQLA-17K3E6   STA          CLASSIFICATION2          Call                      311
AQLA-17K3E6   STA          CLASSIFICATION3          Service Interaction                            311
AQLA-17K3E6   STA          CLASSIFICATION4          Manager Attended                            311
AQLA-17K3E6   STA          STATUS     Submitted     311
AQLA-17K3E6   STA          COMMUNICATIONMODE     Face to Face                 311


Query used:

select 
 activity_id
 ,source_system_code
 ,source_value_name
 ,source_value_content
 ,file_id
 from (
 select seq_num as file_id 
       ,extractvalue(value(em), '/Activity/ActivityId/ID') as activity_id
       ,extractvalue(value(em), '/Activity/@SourceSystemCode') as source_system_code
       ,extractvalue(value(em), '/Activity/SourceValues/PropertyName') as source_value_name
       ,extractvalue(value(em), '/Activity/SourceValues/PropertyValue') as source_value_content
         from  
 (
select seq_num,XMLParse(CONTENT 
                xml_data
                wellformed) xml_data,event_type from (
select seq_num,replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(xml_data
,'ns0:',null),'ns2:',null),'xsi:',null),'tns:',null),'cust:',null),'comm:',null),'urn:',null),'base:',null),'urn1:',null),'xmlns:',null),'ns7:',null),'urn2:',null),'urn3:',null),'imp1:',null),'init:',null)
,' xmlns="astrazeneca:na:CommonTypes:domain:2"',null)
,' Interactions="astrazeneca:na:Activity:domain:3"',null)
,' xmlns="astrazeneca:na:Activity:domain:3"',null)
,' ns2="astrazeneca:na:CommonTypes:domain:2"',null)
,' xsi="http://www.w3.org/2001/XMLSchema-instance"',null)
,' ns3="astrazeneca:na:Customer:domain:1"',null)
,'<?xml version="1.0" encoding="ISO-8859-1"?>',null)
        xml_data,event_type
  from ( select 
  seq_num,xml_data,event_type from ss_ac_activity_interaction where seq_num=311 ) d) tab ) tb,
  table(xmlsequence(extract(xml_data,
                                 '//Interaction/Activity'))) em) tab



XML which I am getting in my source table:

<CreateActivity init="astrazeneca:na:Activity:services:ActivityServiceInitiatior:3">
     <Interaction SchemaVersion="">
          <InteractionId imp1="astrazeneca:na:Activity:domain:3">
               <ID SystemCode="HAR">AQLA-17K3DZ</ID>
          </InteractionId>
          <InteractionDetails xsd="http://www.w3.org/2001/XMLSchema" ns0="astrazeneca:na:Activity:domain:3" SourceSystemCode="STA" SystemOfRecordCode="STA" RecordCompanyCode="AZN" SourceCountry="HR" SourceRegion="HRV" SourceSystemGroup="HAR" SystemOfRecordGroup="HAR" TransactionCode="I" CreatedOnDate="2012-11-08T09:54:31Z" UpdatedOnDate="2012-11-09T20:44:42Z" SourceCreatedByID="KRVD095" SourceCreatedByFirstName="Sandra Filipa" SourceCreatedByLastName="Cardoso" SourceUpdatedByID="KRVD095" SourceUpdatedByFirstName="Sandra Filipa" SourceUpdatedByLastName="Cardoso" RestrictionGroup="NONE">
               <StartDate>2012-11-08T14:30:00Z</StartDate>
               <EndDate>2012-11-08T15:00:00Z</EndDate>
               <Location type="LocationAddress">
                    <AddressLine LineNo="1">ULICA MARŠALA TITA 188</AddressLine>
                    <AddressLine LineNo="2">ULICA MARŠALA TITA 188/1</AddressLine>
                    <AddressLine LineNo="3"/>
                    <CityName>OPATIJA</CityName>
                    <StateProvName/>
                    <ZIP>51410</ZIP>
                    <CountryName/>
               </Location>
          </InteractionDetails>
          <Activity xsd="http://www.w3.org/2001/XMLSchema" ns0="astrazeneca:na:Activity:domain:3" type="BaseActivityType" TransactionCode="I" CreatedOnDate="2012-11-08T09:55:09Z" UpdatedOnDate="2012-11-08T09:55:09Z" SourceCreatedByID="KRVD095" SourceCreatedByFirstName="Sandra Filipa" SourceCreatedByLastName="Cardoso" SourceUpdatedByID="KRVD095" SourceUpdatedByFirstName="Sandra Filipa" SourceUpdatedByLastName="Cardoso" SourceSystemCode="STA" SystemOfRecordCode="STA" RecordCompanyCode="AZN" SourceCountry="HR" SourceRegion="HRV" SourceSystemGroup="HAR" SystemOfRecordGroup="HAR" RestrictionGroup="NONE">
               <ActivityId>
                    <ID SystemCode="HAR">AQLA-17K3EA</ID>
               </ActivityId>
               <Agent>
                    <EmployeeId>
                         <ID SystemCode="PRID">KRVD095</ID>
                    </EmployeeId>
                    <Territory>
                         <TerritoryId>
                              <ID SystemCode="HAR">AQLA-17FTXR</ID>
                         </TerritoryId>
                         <Role>AZ STA Role(No Lot Mgmt)</Role>
                         <Description>HRV130J-31-12-2012</Description>
                    </Territory>
               </Agent>
               <Customer>
                    <SourceCustomerID SystemCode="QTR">1-67XUDB</SourceCustomerID>
                    <CustomerType>HCP</CustomerType>
                    <CustomerActivityRole>participant</CustomerActivityRole>
                    <RelatedCustomer>
                         <RelationshipType>is_a_child_of</RelationshipType>
                         <RelatedSourceCustomerId SystemCode="QTR">1-1U5SEO</RelatedSourceCustomerId>
                    </RelatedCustomer>
               </Customer>
               <Customer>
                    <SourceCustomerID SystemCode="QTR">1-1U5SEO</SourceCustomerID>
                    <CustomerType>HCA</CustomerType>
                    <CustomerActivityRole>participant</CustomerActivityRole>
               </Customer>
               <ReferenceContent>
                    <ReferenceName>Service successfully delivered</ReferenceName>
                    <ReferenceType>Product message</ReferenceType>
               </ReferenceContent>
               <StartDate>2012-11-08T14:30:00Z</StartDate>
               <EndDate>2012-11-08T15:00:00Z</EndDate>
               <Topic>
                    <Name>SYMBICORT</Name>
                    <Product>
                         <AZBrandId>-1</AZBrandId>
                         <SourceProductId SystemCode="HAR">AQLA-TBJD0</SourceProductId>
                         <ProductName>SYMBICORT</ProductName>
                    </Product>
               </Topic>
               <ActivityAction>discussion</ActivityAction>
               <ActivityStatus>completed</ActivityStatus>
               <ActivityInitiatedBy>AstraZeneca agent</ActivityInitiatedBy>
               <ActivityOutcome>success with identified customer</ActivityOutcome>
               <CommunicationMode>in person</CommunicationMode>
               <LocationSetting>unknown</LocationSetting>
               <SourceValues>
                    <Classification1>Product Messages</Classification1>
                    <Classification2>Call</Classification2>
                    <Classification3>Service </Classification3>
                    <Classification4>Manager Attended</Classification4>
                    <Status>Submitted</Status>
                    <CommunicationMode>Face to Face</CommunicationMode>
               </SourceValues>
               <DataQualityIssueCode>NF_PRODUCT</DataQualityIssueCode>
               <DataQualityIssueCode>NULL_REF_CONTENT_TYP</DataQualityIssueCode>
               <DataQualityIssueCode>NF_CUSTOMER</DataQualityIssueCode>
               <DataQualityIssueCode>NF_CUSTOMER</DataQualityIssueCode>
          </Activity>
          <Activity xsd="http://www.w3.org/2001/XMLSchema" ns0="astrazeneca:na:Activity:domain:3" type="OrderActivityType" TransactionCode="I" CreatedOnDate="2012-11-08T09:54:54Z" UpdatedOnDate="2012-11-08T09:54:54Z" SourceCreatedByID="KRVD095" SourceCreatedByFirstName="Sandra Filipa" SourceCreatedByLastName="Cardoso" SourceUpdatedByID="KRVD095" SourceUpdatedByFirstName="Sandra Filipa" SourceUpdatedByLastName="Cardoso" SourceSystemCode="STA" SystemOfRecordCode="STA" RecordCompanyCode="AZN" SourceCountry="HR" SourceRegion="HRV" SourceSystemGroup="HAR" SystemOfRecordGroup="HAR" RestrictionGroup="NONE">
               <ActivityId>
                    <ID SystemCode="HAR">AQLA-17K3E6</ID>
               </ActivityId>
               <Agent>
                    <EmployeeId>
                         <ID SystemCode="PRID">KRVD095</ID>
                    </EmployeeId>
                    <Territory>
                         <TerritoryId>
                              <ID SystemCode="HAR">AQLA-17FTXR</ID>
                         </TerritoryId>
                         <Role>AZ STA Role(No Lot Mgmt)</Role>
                         <Description>HRV130J-31-12-2012</Description>
                    </Territory>
               </Agent>
               <Customer>
                    <SourceCustomerID SystemCode="QTR">1-67XUDB</SourceCustomerID>
                    <CustomerType>HCP</CustomerType>
                    <CustomerActivityRole>participant</CustomerActivityRole>
                    <RelatedCustomer>
                         <RelationshipType>is_a_child_of</RelationshipType>
                         <RelatedSourceCustomerId SystemCode="QTR">1-1U5SEO</RelatedSourceCustomerId>
                    </RelatedCustomer>
               </Customer>
               <Customer>
                    <SourceCustomerID SystemCode="QTR">1-1U5SEO</SourceCustomerID>
                    <CustomerType>HCA</CustomerType>
                    <CustomerActivityRole>participant</CustomerActivityRole>
               </Customer>
               <ReferenceContent>
                    <ReferenceName>SYM2/10:GOLD POCKET GUIDELINES 2010 (PT)</ReferenceName>
                    <ReferenceType>item</ReferenceType>
                    <ReferenceId SystemCode="HAR">AQLA-WBL8P</ReferenceId>
               </ReferenceContent>
               <StartDate>2012-11-08T14:30:00Z</StartDate>
               <EndDate>2012-11-08T15:00:00Z</EndDate>
               <ActivityAction>item provided</ActivityAction>
               <ActivityStatus>completed</ActivityStatus>
               <ActivityInitiatedBy>AstraZeneca agent</ActivityInitiatedBy>
               <ActivityOutcome>success with identified customer</ActivityOutcome>
               <CommunicationMode>in person</CommunicationMode>
               <LocationSetting>unknown</LocationSetting>
               <SourceValues>
                    <Classification1>Promotional Items Dropped</Classification1>
                    <Classification2>Call</Classification2>
                    <Classification3>Service Interaction</Classification3>
                    <Classification4>Manager Attended</Classification4>
                    <Status>Submitted</Status>
                    <CommunicationMode>Face to Face</CommunicationMode>
               </SourceValues>
               <DataQualityIssueCode>NF_CUSTOMER</DataQualityIssueCode>
               <DataQualityIssueCode>NF_CUSTOMER</DataQualityIssueCode>
               <DataQualityIssueCode>NULL_PRODUCT</DataQualityIssueCode>
               <DataQualityIssueCode>NULL_ORDER_ID</DataQualityIssueCode>
               <OrderId/>
               <OrderedQuantity>1</OrderedQuantity>
          </Activity>
     </Interaction>
</CreateActivity>
Can any body please check and revert I need urgent help.
  • 1. Re: multiple xml tag values are not getting in output.
    odie_63 Guru
    Currently Being Moderated
    Post the original XML document please, that one has got the namespace prefixes removed.

    Give your database version too : SELECT * FROM v$version

    I'll show you how to correctly deal with namespaces instead of this mess.
  • 2. Re: multiple xml tag values are not getting in output.
    AlexAnd Guru
    Currently Being Moderated
    smth
    SQL> select * from v$version where rownum=1;
     
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
     
    SQL> 
    SQL> SELECT x.activity_id, x.source_system_code, y.source_value_name, y.source_value_content
      2    FROM XMLTable('CreateActivity/Interaction/Activity' passing
      3                  XMLTYPE(bfilename('MYDIR', 't.xml'),
      4                          NLS_CHARSET_ID('AL32UTF8')) columns activity_id
      5                  VARCHAR2(100) path 'ActivityId/ID'
      6                  ,source_system_code VARCHAR2(100) path'@SourceSystemCode'
      7                  , source_value xmltype path 'SourceValues/*'
      8                  ) x
      9  , XMLTable('for $p in $d/descendant::*
     10                  return element r {element tag_name {fn:name($p)}, element tag_val {$p}}'
     11                  passing
     12                  x.source_value as "d"
     13                  columns source_value_name VARCHAR2(100) path 'tag_name',
     14                  source_value_content VARCHAR2(100) path 'tag_val'
     15                  ) y
     16  /
     
    ACTIVITY_ID                                                                      SOURCE_SYSTEM_CODE                                                               SOURCE_VALUE_NAME                                                                SOURCE_VALUE_CONTENT
    -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
    AQLA-17K3EA                                                                      STA                                                                              Classification1                                                                  Product Messages
    AQLA-17K3EA                                                                      STA                                                                              Classification2                                                                  Call
    AQLA-17K3EA                                                                      STA                                                                              Classification3                                                                  Service
    AQLA-17K3EA                                                                      STA                                                                              Classification4                                                                  Manager Attended
    AQLA-17K3EA                                                                      STA                                                                              Status                                                                           Submitted
    AQLA-17K3EA                                                                      STA                                                                              CommunicationMode                                                                Face to Face
    AQLA-17K3E6                                                                      STA                                                                              Classification1                                                                  Promotional Items Dropped
    AQLA-17K3E6                                                                      STA                                                                              Classification2                                                                  Call
    AQLA-17K3E6                                                                      STA                                                                              Classification3                                                                  Service Interaction
    AQLA-17K3E6                                                                      STA                                                                              Classification4                                                                  Manager Attended
    AQLA-17K3E6                                                                      STA                                                                              Status                                                                           Submitted
    AQLA-17K3E6                                                                      STA                                                                              CommunicationMode                                                                Face to Face
     
    12 rows selected
     
    SQL> 
  • 3. Re: multiple xml tag values are not getting in output.
    977256 Newbie
    Currently Being Moderated
    Hi odie...

    Please find details you asked for ..and raw xml I am geting.(Note I'll be getting one xml as one record (for one particular interaction_id) and then data from that xml will go to various tables for that particular ID)
    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
    
    
    
    raw xml
    ======
    
    
    
    <init:CreateActivity xmlns:init="urn:astrazeneca:na:Activity:services:ActivityServiceInitiatior:3">
    <init:Interaction SchemaVersion="">
    <imp1:InteractionId xmlns:imp1="urn:astrazeneca:na:Activity:domain:3">
    <imp1:ID SystemCode="HAR">AQLA-14RSY8-SP1</imp1:ID>
    </imp1:InteractionId>
    <ns0:InteractionDetails xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="urn:astrazeneca:na:Activity:domain:3" xmlns:ns0="urn:astrazeneca:na:Activity:domain:3" SourceSystemCode="STA" SystemOfRecordCode="STA" RecordCompanyCode="AZN" SourceCountry="HR" SourceRegion="HRV" SourceSystemGroup="HAR" SystemOfRecordGroup="HAR" TransactionCode="I" CreatedOnDate="2012-09-22T16:24:24Z" UpdatedOnDate="2012-09-22T16:25:51Z" SourceCreatedByID="SYS-ACTINT" SourceCreatedByFirstName="Activity" SourceCreatedByLastName="Interface" SourceUpdatedByID="SYS-ACTINT" SourceUpdatedByFirstName="Activity" SourceUpdatedByLastName="Interface" RestrictionGroup="NONE">
             <ns0:StartDate>2012-09-22T16:24:00Z</ns0:StartDate>
             <ns0:EndDate>2012-09-22T16:54:00Z</ns0:EndDate>
             <ns0:Location xsi:type="LocationAddress">
                <ns0:AddressLine LineNo="1">RAVERSIJDESTRAAT, 20</ns0:AddressLine>
                <ns0:AddressLine LineNo="2"/>
                <ns0:AddressLine LineNo="3"/>
                <ns0:CityName>OOSTENDE</ns0:CityName>
                <ns0:StateProvName>Flandre Occidentale</ns0:StateProvName>
                <ns0:ZIP>B-8400</ns0:ZIP>
                <ns0:CountryName Code="USA">USA</ns0:CountryName>
             </ns0:Location>
          </ns0:InteractionDetails>
    <ns0:Activity xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="urn:astrazeneca:na:Activity:domain:3" xmlns:ns0="urn:astrazeneca:na:Activity:domain:3" xsi:type="BaseActivityType" TransactionCode="I" CreatedOnDate="2012-09-22T16:25:09Z" UpdatedOnDate="2012-09-22T16:25:09Z" SourceCreatedByID="SYS-ACTINT" SourceCreatedByFirstName="Activity" SourceCreatedByLastName="Interface" SourceUpdatedByID="SYS-ACTINT" SourceUpdatedByFirstName="Activity" SourceUpdatedByLastName="Interface" SourceSystemCode="STA" SystemOfRecordCode="STA" RecordCompanyCode="AZN" SourceCountry="HR" SourceRegion="HRV" SourceSystemGroup="HAR" SystemOfRecordGroup="HAR" RestrictionGroup="NONE">
             <ns0:ActivityId>
                <ID xmlns="urn:astrazeneca:na:CommonTypes:domain:2" SystemCode="HAR">AQLA-14RSYD-MS14a</ID>
             </ns0:ActivityId>
             <ns0:Agent>
                <ns0:EmployeeId>
                   <ID xmlns="urn:astrazeneca:na:CommonTypes:domain:2" SystemCode="PRID">SYS-ACTINT</ID>
                </ns0:EmployeeId>
                <ns0:Territory>
                   <TerritoryId xmlns="urn:astrazeneca:na:CommonTypes:domain:2">
                      <ID SystemCode="PRID">565</ID>
                   </TerritoryId>
                   <TerritoryCode xmlns="urn:astrazeneca:na:CommonTypes:domain:2">HAR</TerritoryCode>
                   <Role xmlns="urn:astrazeneca:na:CommonTypes:domain:2">AZ STA Admin Role</Role>
                </ns0:Territory>
             </ns0:Agent>
             <ns0:Customer>
                <ns0:SourceCustomerID SystemCode="QTR">123</ns0:SourceCustomerID>
                <ns0:CustomerType>HCA</ns0:CustomerType>
                <ns0:CustomerActivityRole>participant</ns0:CustomerActivityRole>
                <ns0:RelatedCustomer>
                   <ns0:RelationshipType/>
                   <ns0:RelatedSourceCustomerId SystemCode="SFA"/>
                </ns0:RelatedCustomer>
             </ns0:Customer>
             <ns0:ReferenceContent>
                <ns0:ReferenceName>AQLA-14RSYD</ns0:ReferenceName>
                <ns0:ReferenceType>Product message</ns0:ReferenceType>
             </ns0:ReferenceContent>
             <ns0:StartDate>2012-09-22T16:24:00Z</ns0:StartDate>
             <ns0:EndDate>2012-09-22T16:54:00Z</ns0:EndDate>
             <ns0:Topic>
                <ns0:Product>
                   <ns0:AZBrandId>-1</ns0:AZBrandId>
                   <ns0:AZProductId SystemCode="HAR">AZ-AQLA-L06RS</ns0:AZProductId>
                   <ns0:SourceProductId SystemCode="HAR">AQLA-L06RS</ns0:SourceProductId>
                   <ns0:ReferenceProductId SystemCode="HAR">REF-AQLA-L06RS</ns0:ReferenceProductId>
                   <ns0:ProductName>Nexium</ns0:ProductName>
                </ns0:Product>
             </ns0:Topic>
             <ns0:ActivityAction>discussion</ns0:ActivityAction>
             <ns0:ActivityStatus>completed</ns0:ActivityStatus>
             <ns0:ActivityInitiatedBy>AstraZeneca agent</ns0:ActivityInitiatedBy>
             <ns0:ActivityOutcome>success with identified customer</ns0:ActivityOutcome>
             <ns0:CommunicationMode>-1</ns0:CommunicationMode>
             <ns0:LocationSetting>unknown</ns0:LocationSetting>
             <ns0:SourceValues>
                <ns0:Classification1>Product Messages</ns0:Classification1>
                <ns0:Classification2>Call</ns0:Classification2>
                <ns0:Classification3>Service Interaction</ns0:Classification3>
                <ns0:Status>Submitted</ns0:Status>
                <ns0:CommunicationMode>Yes</ns0:CommunicationMode>
             </ns0:SourceValues>
             <ns0:DataQualityIssueCode>NF_PRODUCT</ns0:DataQualityIssueCode>
             <ns0:DataQualityIssueCode>NULL_REF_CONTENT_TYP</ns0:DataQualityIssueCode>
             <ns0:DataQualityIssueCode>NF_CUSTOMER</ns0:DataQualityIssueCode>
             <ns0:DataQualityIssueCode>UNDEF_STD_COMM_MODE</ns0:DataQualityIssueCode>
          </ns0:Activity>
    <ns0:Activity xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="urn:astrazeneca:na:Activity:domain:3" xmlns:ns0="urn:astrazeneca:na:Activity:domain:3" xsi:type="BaseActivityType" TransactionCode="I" CreatedOnDate="2012-09-22T16:25:09Z" UpdatedOnDate="2012-09-22T16:25:09Z" SourceCreatedByID="SYS-ACTINT" SourceCreatedByFirstName="Activity" SourceCreatedByLastName="Interface" SourceUpdatedByID="SYS-ACTINT" SourceUpdatedByFirstName="Activity" SourceUpdatedByLastName="Interface" SourceSystemCode="STA" SystemOfRecordCode="STA" RecordCompanyCode="AZN" SourceCountry="HR" SourceRegion="HRV" SourceSystemGroup="HAR" SystemOfRecordGroup="HAR" RestrictionGroup="NONE">
             <ns0:ActivityId>
                <ID xmlns="urn:astrazeneca:na:CommonTypes:domain:2" SystemCode="HAR">AQLA-14RSYD-MS14b</ID>
             </ns0:ActivityId>
             <ns0:Agent>
                <ns0:EmployeeId>
                   <ID xmlns="urn:astrazeneca:na:CommonTypes:domain:2" SystemCode="PRID">SYS-ACTINT</ID>
                </ns0:EmployeeId>
                <ns0:Territory>
                   <TerritoryId xmlns="urn:astrazeneca:na:CommonTypes:domain:2">
                      <ID SystemCode="PRID">565</ID>
                   </TerritoryId>
                   <TerritoryCode xmlns="urn:astrazeneca:na:CommonTypes:domain:2">HAR</TerritoryCode>
                   <Role xmlns="urn:astrazeneca:na:CommonTypes:domain:2">AZ STA Admin Role</Role>
                </ns0:Territory>
             </ns0:Agent>
             <ns0:Customer>
                <ns0:AZCustomerMasterID SystemCode="QTR">111</ns0:AZCustomerMasterID>
                <ns0:SourceCustomerID SystemCode="QTR">456</ns0:SourceCustomerID>
                <ns0:CustomerType>HCA</ns0:CustomerType>
                <ns0:CustomerActivityRole>participant</ns0:CustomerActivityRole>
                <ns0:RelatedCustomer>
                   <ns0:RelationshipType/>
                   <ns0:RelatedSourceCustomerId SystemCode="SFA"/>
                </ns0:RelatedCustomer>
             </ns0:Customer>
             <ns0:ReferenceContent>
                <ns0:ReferenceName>AQLA-14RSYD</ns0:ReferenceName>
                <ns0:ReferenceType>Product message</ns0:ReferenceType>
             </ns0:ReferenceContent>
             <ns0:StartDate>2012-09-22T16:24:00Z</ns0:StartDate>
             <ns0:EndDate>2012-09-22T16:54:00Z</ns0:EndDate>
             <ns0:Topic>
                <ns0:Product>
                   <ns0:AZBrandId>-1</ns0:AZBrandId>
                   <ns0:AZProductId SystemCode="HAR">AZ-AQLA-L06RS</ns0:AZProductId>
                   <ns0:SourceProductId SystemCode="HAR">AQLA-L06RS</ns0:SourceProductId>
                   <ns0:ReferenceProductId SystemCode="HAR">REF-AQLA-L06RS</ns0:ReferenceProductId>
                   <ns0:ProductName>Nexium</ns0:ProductName>
                </ns0:Product>
             </ns0:Topic>
             <ns0:ActivityAction>discussion</ns0:ActivityAction>
             <ns0:ActivityStatus>completed</ns0:ActivityStatus>
             <ns0:ActivityInitiatedBy>AstraZeneca agent</ns0:ActivityInitiatedBy>
             <ns0:ActivityOutcome>success with identified customer</ns0:ActivityOutcome>
             <ns0:CommunicationMode>-1</ns0:CommunicationMode>
             <ns0:LocationSetting>unknown</ns0:LocationSetting>
             <ns0:SourceValues>
                <ns0:Classification1>Product Messages</ns0:Classification1>
                <ns0:Classification2>Call</ns0:Classification2>
                <ns0:Classification3>Service Interaction</ns0:Classification3>
                <ns0:Status>Submitted</ns0:Status>
                <ns0:CommunicationMode>Yes</ns0:CommunicationMode>
             </ns0:SourceValues>
             <ns0:DataQualityIssueCode>NF_PRODUCT</ns0:DataQualityIssueCode>
             <ns0:DataQualityIssueCode>NULL_REF_CONTENT_TYP</ns0:DataQualityIssueCode>
             <ns0:DataQualityIssueCode>NF_CUSTOMER</ns0:DataQualityIssueCode>
             <ns0:DataQualityIssueCode>UNDEF_STD_COMM_MODE</ns0:DataQualityIssueCode>
          </ns0:Activity>
    <ns0:Activity xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="urn:astrazeneca:na:Activity:domain:3" xmlns:ns0="urn:astrazeneca:na:Activity:domain:3" xsi:type="BaseActivityType" TransactionCode="I" CreatedOnDate="2012-09-22T16:25:09Z" UpdatedOnDate="2012-09-22T16:25:09Z" SourceCreatedByID="SYS-ACTINT" SourceCreatedByFirstName="Activity" SourceCreatedByLastName="Interface" SourceUpdatedByID="SYS-ACTINT" SourceUpdatedByFirstName="Activity" SourceUpdatedByLastName="Interface" SourceSystemCode="STA" SystemOfRecordCode="STA" RecordCompanyCode="AZN" SourceCountry="HR" SourceRegion="HRV" SourceSystemGroup="HAR" SystemOfRecordGroup="HAR" RestrictionGroup="NONE">
             <ns0:ActivityId>
                <ID xmlns="urn:astrazeneca:na:CommonTypes:domain:2" SystemCode="HAR">AQLA-14RSYD-MS14c</ID>
             </ns0:ActivityId>
             <ns0:Agent>
                <ns0:EmployeeId>
                   <ID xmlns="urn:astrazeneca:na:CommonTypes:domain:2" SystemCode="PRID">SYS-ACTINT</ID>
                </ns0:EmployeeId>
                <ns0:Territory>
                   <TerritoryId xmlns="urn:astrazeneca:na:CommonTypes:domain:2">
                      <ID SystemCode="PRID">565</ID>
                   </TerritoryId>
                   <TerritoryCode xmlns="urn:astrazeneca:na:CommonTypes:domain:2">HAR</TerritoryCode>
                   <Role xmlns="urn:astrazeneca:na:CommonTypes:domain:2">AZ STA Admin Role</Role>
                </ns0:Territory>
             </ns0:Agent>
             <ns0:Customer>
                <ns0:SourceCustomerID SystemCode="QTR">789</ns0:SourceCustomerID>
                <ns0:CustomerType>HCA</ns0:CustomerType>
                <ns0:CustomerActivityRole>participant</ns0:CustomerActivityRole>
                <ns0:RelatedCustomer>
                   <ns0:RelationshipType/>
                   <ns0:RelatedSourceCustomerId SystemCode="SFA"/>
                </ns0:RelatedCustomer>
             </ns0:Customer>
             <ns0:ReferenceContent>
                <ns0:ReferenceName>AQLA-14RSYD</ns0:ReferenceName>
                <ns0:ReferenceType>Product message</ns0:ReferenceType>
             </ns0:ReferenceContent>
             <ns0:StartDate>2012-09-22T16:24:00Z</ns0:StartDate>
             <ns0:EndDate>2012-09-22T16:54:00Z</ns0:EndDate>
             <ns0:Topic>
                <ns0:Product>
                   <ns0:AZBrandId>-1</ns0:AZBrandId>
                   <ns0:AZProductId SystemCode="HAR">AZ-AQLA-L06RS</ns0:AZProductId>
                   <ns0:SourceProductId SystemCode="HAR">AQLA-L06RS</ns0:SourceProductId>
                   <ns0:ReferenceProductId SystemCode="HAR">REF-AQLA-L06RS</ns0:ReferenceProductId>
                   <ns0:ProductName>Nexium</ns0:ProductName>
                </ns0:Product>
             </ns0:Topic>
             <ns0:ActivityAction>discussion</ns0:ActivityAction>
             <ns0:ActivityStatus>completed</ns0:ActivityStatus>
             <ns0:ActivityInitiatedBy>AstraZeneca agent</ns0:ActivityInitiatedBy>
             <ns0:ActivityOutcome>success with identified customer</ns0:ActivityOutcome>
             <ns0:CommunicationMode>-1</ns0:CommunicationMode>
             <ns0:LocationSetting>unknown</ns0:LocationSetting>
             <ns0:SourceValues>
                <ns0:Classification1>Product Messages</ns0:Classification1>
                <ns0:Classification2>Call</ns0:Classification2>
                <ns0:Classification3>Service Interaction</ns0:Classification3>
                <ns0:Status>Submitted</ns0:Status>
                <ns0:CommunicationMode>Yes</ns0:CommunicationMode>
             </ns0:SourceValues>
             <ns0:DataQualityIssueCode>NF_PRODUCT</ns0:DataQualityIssueCode>
             <ns0:DataQualityIssueCode>NULL_REF_CONTENT_TYP</ns0:DataQualityIssueCode>
             <ns0:DataQualityIssueCode>NF_CUSTOMER</ns0:DataQualityIssueCode>
             <ns0:DataQualityIssueCode>UNDEF_STD_COMM_MODE</ns0:DataQualityIssueCode>
          </ns0:Activity>
    </init:Interaction>
    </init:CreateActivity>
    Edited by: 974253 on 07-Dec-2012 01:22
  • 4. Re: multiple xml tag values are not getting in output.
    odie_63 Guru
    Currently Being Moderated
    OK.

    Use XMLTable and the XMLNamespaces clause to declare the namespaces.
    SQL> select x1.activity_id
      2       , x1.source_system_code
      3       , x2.source_value_name
      4       , x2.source_value_content
      5  from ss_ac_activity_interaction t
      6     , xmltable(
      7         xmlnamespaces(
      8           'urn:astrazeneca:na:Activity:services:ActivityServiceInitiatior:3' as "init"
      9         , 'urn:astrazeneca:na:Activity:domain:3' as "ns0"
     10         , 'urn:astrazeneca:na:CommonTypes:domain:2' as "ns1"
     11         )
     12       , '/init:CreateActivity/init:Interaction/ns0:Activity'
     13         passing t.xml_data
     14         columns
     15           activity_id        varchar2(30) path 'ns0:ActivityId/ns1:ID'
     16         , source_system_code varchar2(10)  path '@SourceSystemCode'
     17         , sourcevalues       xmltype       path 'ns0:SourceValues'
     18       ) x1
     19     , xmltable(
     20         xmlnamespaces(
     21           'urn:astrazeneca:na:Activity:domain:3' as "ns0"
     22         )
     23       , 'for $i in /ns0:SourceValues/child::*
     24          return element r {
     25            element source_value_name    {local-name($i)}
     26          , element source_value_content {data($i)}
     27          }'
     28         passing x1.sourcevalues
     29         columns
     30           source_value_name     varchar2(30)  path 'source_value_name'
     31         , source_value_content  varchar2(100) path 'source_value_content'
     32       ) x2
     33  ;
     
    ACTIVITY_ID                    SOURCE_SYSTEM_CODE SOURCE_VALUE_NAME              SOURCE_VALUE_CONTENT
    ------------------------------ ------------------ ------------------------------ --------------------------------------------------------------------------------
    AQLA-14RSYD-MS14a              STA                Classification1                Product Messages
    AQLA-14RSYD-MS14a              STA                Classification2                Call
    AQLA-14RSYD-MS14a              STA                Classification3                Service Interaction
    AQLA-14RSYD-MS14a              STA                Status                         Submitted
    AQLA-14RSYD-MS14a              STA                CommunicationMode              Yes
    AQLA-14RSYD-MS14b              STA                Classification1                Product Messages
    AQLA-14RSYD-MS14b              STA                Classification2                Call
    AQLA-14RSYD-MS14b              STA                Classification3                Service Interaction
    AQLA-14RSYD-MS14b              STA                Status                         Submitted
    AQLA-14RSYD-MS14b              STA                CommunicationMode              Yes
    AQLA-14RSYD-MS14c              STA                Classification1                Product Messages
    AQLA-14RSYD-MS14c              STA                Classification2                Call
    AQLA-14RSYD-MS14c              STA                Classification3                Service Interaction
    AQLA-14RSYD-MS14c              STA                Status                         Submitted
    AQLA-14RSYD-MS14c              STA                CommunicationMode              Yes
     
    15 rows selected
     
  • 5. Re: multiple xml tag values are not getting in output.
    977256 Newbie
    Currently Being Moderated
    Wonderfull Odie...thanks you the man!!!


    currently I am getting some out put by this...as I wanted it..but also I am getiing the error in sqldeveloper...


    I'll let you know the error...my connectivity is gone for now ...:(

    Edited by: 974253 on 07-Dec-2012 01:59

    Edited by: 974253 on Dec 9, 2012 4:57 AM
  • 6. Re: multiple xml tag values are not getting in output.
    977256 Newbie
    Currently Being Moderated
    Hey odie..

    Thanks for previous help...!!

    could you please let me know about, how namespace is clearly identified..
    as if I assume namespace to be comming whatever just after the xmlns:*"whatever"*..
    then why you didn't use imp1 as namespace
    <imp1:InteractionId xmlns:imp1="urn:astrazeneca:na:Activity:domain:3">
    in above query
    also please explain how we did use this
     '/init:CreateActivity/init:Interaction/ns0:Activity'
    in above query's x1 xmltable part.

    and above all why all the variable is not used in same xmltable which x1. is there any restriction to use 2 xmltable like x1 and x2.

    I know this question sound to you..just useless..
    but I am a newbie in XML query writing..and it is my project's essential need to write XML query for testing purpose for.

    source table SQL query
    minus
    target table SQL query.


    if you have any basic and useful material for using xmltable and xmlnamespace please share..I have checked many URLs on xmltables and namespace..but nothing is helping me to write queries..for source xml datatype carrying tables.

    please help..

    Edited by: 974253 on Dec 9, 2012 5:13 AM
  • 7. Re: multiple xml tag values are not getting in output.
    odie_63 Guru
    Currently Being Moderated
    974253 wrote:
    could you please let me know about, how namespace is clearly identified..
    as if I assume namespace to be comming whatever just after the xmlns:*"whatever"*..
    then why you didn't use imp1 as namespace
    <imp1:InteractionId xmlns:imp1="urn:astrazeneca:na:Activity:domain:3">
    in above query
    When using namespaces, the important thing is the target URI, the prefix is just a "shortcut" to qualify elements or attributes in the XML document.
    If you look closely, you'll see that the namespace "urn:astrazeneca:na:Activity:domain:3" is defined multiple times, using different prefixes, including a default definition :
    xmlns:imp1="urn:astrazeneca:na:Activity:domain:3"
    xmlns="urn:astrazeneca:na:Activity:domain:3"
    xmlns:ns0="urn:astrazeneca:na:Activity:domain:3"
    Back in XMLTable, I chose to declare prefix "ns0" for it, but I could very well have chosen any other prefixes I wanted.
    also please explain how we did use this
     '/init:CreateActivity/init:Interaction/ns0:Activity'
    in above query's x1 xmltable part.
    and above all why all the variable is not used in same xmltable which x1. is there any restriction to use 2 xmltable like x1 and x2.
    I'm not sure I understand what you mean.

    As per your requirement, we need to extract each Activity node (that is done with x1), then each elements under SourceValues (with x2).
    The following targets each Activity using the full qualified path to them :
    /init:CreateActivity/init:Interaction/ns0:Activity
    Since <CreateActivity> belongs to the namespace "urn:astrazeneca:na:Activity:services:ActivityServiceInitiatior:3", I have to declare it in the XMLNamespaces clause, I used the same prefix as in the document to avoid confusion.
    <Interaction> belongs to the same namespace, so I prefix it with the "init" prefix as well.
    However, the <Activity> element belongs to another namespace ("urn:astrazeneca:na:Activity:domain:3"), so I declare it with prefix "ns0".

    Basically, XMLTable X1 returns 3 rows, because there are 3 <Activity>.
    Since you need to have <SourceValues> components associated with its parent Activity, those components have to be extracted in a separate XMLTable, that's X2.
    It's a very common technique to extract nested repeating elements.
    if you have any basic and useful material for using xmltable and xmlnamespace please share..I have checked many URLs on xmltables and namespace..but nothing is helping me to write queries..for source xml datatype carrying tables.
    Search for XMLTable and XQuery examples in this forum, as well as the {forum:id=34} forum.
    The official documention is here : http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb_xquery.htm#CBAGCBGJ
  • 8. Re: multiple xml tag values are not getting in output.
    977256 Newbie
    Currently Being Moderated
    Thanks...odie...for understanding laymen person doubts...

    you gave the appropriate explanation...but by your answer I have one doubt raised ..."If I have N nodes in xml then do I have to write the query for each multi-child parent node in xmltable. like x1,x2,x3,x4....and so on" as you did in previous query in which xml has 1-Interaction-3 Activity-5 SourceSytemValue so you took x1,x2.


    As I posted earlier that In my source table I'll be getting one xml as one record..and if 2 xml has different node having different number of multichild, Would then above xmlquery hold good or we'll have to write query xml specific....or can we write generic query for xml, no matter which node is having n child.


    plus I'll post the error log...tomorrow as I was getting error by running above query as I have currently 7 xml in my source table...


    Best Regards,

    Edited by: 974253 on Dec 9, 2012 10:43 AM
  • 9. Re: multiple xml tag values are not getting in output.
    977256 Newbie
    Currently Being Moderated
    Please find the error-log I was taking about and the desc of the source table.
    desc ss_ac_activity_interaction
    Name                           Null     Type                                                                                                                                                                                          
    ------------------------------ -------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
    SEQ_NUM                        NOT NULL NUMBER                                                                                                                                                                                        
    XML_DATA                       NOT NULL CLOB()                                                                                                                                                                                        
                                                                                                                                                                               
    
    2 rows selected
    
    
    Error report:
    SQL Error: ORA-19279: XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
    19279. 00000 -  "XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence" 
    *Cause:    The XQuery sequence passed in had more than one item.
    *Action:   Correct the XQuery expression to return a single item sequence.
    I have xml_data column clob field so I made change to line
    passing xmltype(t.xml_data)
    Please check...
  • 10. Re: multiple xml tag values are not getting in output.
    odie_63 Guru
    Currently Being Moderated
    As I posted earlier that In my source table I'll be getting one xml as one record..and if 2 xml has different node having different number of multichild, Would then above xmlquery hold good or we'll have to write query xml specific....or can we write generic query for xml, no matter which node is having n child.
    Yes, the given query should work.
    The number of children (1 to n) is not important, only the fact that there could be more than one.
  • 11. Re: multiple xml tag values are not getting in output.
    odie_63 Guru
    Currently Being Moderated
    Error report:
    SQL Error: ORA-19279: XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
    Most likely, at least one of your source XML has not the expected relationship cardinalities.
    You'll have to test each XML independently (just add a WHERE clause on SEQ_NUM) and post the offending one(s) here.
  • 12. Re: multiple xml tag values are not getting in output.
    977256 Newbie
    Currently Being Moderated
    many thanks!!

    So for multiple node do I need to write multiple for loops...for tag which is having multiple child??
  • 13. Re: multiple xml tag values are not getting in output.
    977256 Newbie
    Currently Being Moderated
    Hi Sir,

    Need the help on same issue again
  • 14. Re: multiple xml tag values are not getting in output.
    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                                               
    
    MDM9634         MDM874                 Lector                                             
    MDM9634         MDM874                 Expert veterinarian                                
    MDM9652         MDM9634                Lector                                             
    MDM9652         MDM9634                Expert veterinarian                                
    
    4 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 2 Previous Next

Legend

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