Skip to Main Content
Forums

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

multiple xml tag values are not getting in output.

977256Dec 7 2012 — edited Jan 23 2013
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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 20 2013
Added on Dec 7 2012
#oracle, #pl-sql-xml-programming, #plsql, #xml
15 comments
2,168 views