How to map Salesforce notification message to DB table

Four Stars

How to map Salesforce notification message to DB table

Hi,

I'm designing a Salesforce CRM to DB system that will accept a SF outbound message and put that message on a ActiveMQ queue. So I created a route for that and it works well. It's the next step that gives me trouble. I want a job that listens to the queue so I'm using tMomInput for that. The XML that is on the queue looks like this:

<notifications xmlns="http://soap.sforce.com/2005/09/outbound" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<OrganizationId>00D1l0000008bK1EAI</OrganizationId>
<ActionId>04k1l000000001sAAA</ActionId>
<SessionId>00D1l0000008bK1!AQ4AQN2LeygvbuUNV_bdRPSKLJaF6ACxi6LCLUhxwsiSAESdzLZb5J.VYJ6C9YZoxOTKADTEUCyII0fVY8EV0n6ZkPSMmLy_</SessionId>
<EnterpriseUrl>https://crunchaccounting--apex.my.salesforce.com/services/Soap/c/44.0/00D1l0000008bK1</EnterpriseUrl>
<PartnerUrl>https://crunchaccounting--apex.my.salesforce.com/services/Soap/u/44.0/00D1l0000008bK1</PartnerUrl>
<Notification>
<Id>04l1l0000001I5uAAE</Id>
<sObject xmlns:sf="urn:sobject.enterprise.soap.sforce.com" xsi:type="sf:Account">
<sf:Id>0011l000003x9E5AAI</sf:Id>
<sf:CreatedById>0051t000000n6rEAAQ</sf:CreatedById>
<sf:CreatedDate>2018-10-23T15:07:57.000Z</sf:CreatedDate>
<sf:IsPersonAccount>false</sf:IsPersonAccount>
<sf:LastModifiedById>0051t000000n6rEAAQ</sf:LastModifiedById>
<sf:LastModifiedDate>2018-10-25T04:53:13.000Z</sf:LastModifiedDate>
<sf:LastReferencedDate>2018-10-25T04:53:13.000Z</sf:LastReferencedDate>
<sf:LastViewedDate>2018-10-25T04:53:13.000Z</sf:LastViewedDate>
<sf:Name>Johan Mig 1</sf:Name>
<sfSmiley SurprisedwnerId>0051t000000n6rEAAQ</sfSmiley SurprisedwnerId>
<sfSmiley TongueAYE_Registered__c>false</sfSmiley TongueAYE_Registered__c>
<sf:RecordTypeId>0121t000000Y7YIAA0</sf:RecordTypeId>
<sfSmiley FrustratedystemModstamp>2018-10-25T04:53:13.000Z</sfSmiley FrustratedystemModstamp>
</sObject>
</Notification>
</notifications>|
This is to be mapped to a SF Account (created SF Metadata for this) and then send that Account to a tDbOutput object. 

So far I have (which doesn't work):

Selection_029.png

I dropped the SF metadata schema to tExtractXmLField and the connect tMomInput with tExtractXmLField and copied over the schema tMomInput but as I suspected it doesn't work. tMomInput will expect an SF Account object which it doesn't get. I need something in between tMomInput and tExtractXMLField (don't even know if that is right component for the scenario) but what? That component needs to map the sObject within the Notification tag to Account and send that to tExtractXMLField that will then send it to tDBOutput.

Any suggestions how to read a SF notification message from an ActiveMQ queu and store it in a DB as a SF Account?

BTW tLogRow in the picture above is supposed to be the tDBOutput object. 

 

/Johan

 

Four Stars

Re: How to map Salesforce notification message to DB table

So to clarify my own ramblings.

My job looks like this:

Selection_030.png

tMomInput receives XML (attachment account-schema.xml) which is based on WSDL schema (attachment wfoutbound.wsdl) and map it to a DB table (attachment account_new_sf.xml). The problem seems to be that account-schema contains name spaces because no values are transferred over to tDBOutput. I'm only interested in what is in and within the sObject tag.

    <sObject xmlns:sf="urn:sobject.enterprise.soap.sforce.com" xsi:type="sf:Account">
      <sf:Id>0011l000003x9E5AAI</sf:Id>
      <sf:Name>Johan Mig 10</sf:Name>
      <sf:OwnerId>0051t000000n6rEAAQ</sf:OwnerId>
    </sObject>

So the table in the DB is xsi:type="sf:Account" and the rest are the columns without the namespaces. 

Can tXMLMap do this or do I need some other component(s)?

 

/Johan

 

What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch

Downloads and Trials

Test drive Talend's enterprise products.

Downloads

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Download