Talend Connect
Virtual Summit
JOIN US!
And visit the Customer
& Community Lounge.
May 27-28, wherever you are.

Extract sf:AccountId from following message String

Highlighted
Six Stars

Extract sf:AccountId from following message String

Hi,

 

I have a below string message and in the output I want to extract sf:AccountId from message String:

 

[{"data":"<?xml version="1.0" encoding="UTF-8"?> <soapenv:Envelope 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"> <soapenv:Body> <notifications xmlns="http://soap.sforce.com/2005/09/outbound"> <OrganizationId>00D2F000000DGMUUA4</OrganizationId> <ActionId>04k2F0000004CGOQA2</ActionId> <SessionId xsi:nil="true"/> <EnterpriseUrl>https://behavecareus--dev.my.salesforce.com/services/Soap/c/44.0/00D2F000000DGMU</EnterpriseUrl> <PartnerUrl>https://behavecareus--dev.my.salesforce.com/services/Soap/u/44.0/00D2F000000DGMU</PartnerUrl> <Notification> <Id>04l2F000007wBMHQA2</Id> <sObject xsi:type="sf:Contact" xmlns:sf="urn:sobject.enterprise.soap.sforce.com"> <sf:Id>0032F00000KAlWDQA1</sf:Id> <sf:AccountId>0012F00000M5ACgQAN</sf:AccountId> </sObject> </Notification> </notifications> </soapenv:Body> </soapenv:Envelope>","type":"text/xml; charset=UTF-8"}]
 
I tried to get output with the following components:
tFileInputDelimited --> tLogRow --> tMap --> tLogRow
 
Can I get the help from anyone to get the proper output. 
Thank you in advance! 

Accepted Solutions
Highlighted
Employee

Re: Extract sf:AccountId from following message String

@pgajghate 

 

I hope you are looking for below solution :-)

image.png

 

Below are the component screenshots. You need to read the record tFileInputFullRow so that entire data will be in a single line. The next step is tMap as shown below.

image.png

 

var1   ->         row1.line.indexOf("<?xml version=")
var2   ->         row1.line.indexOf("\",\"type")

xml_out ->     row1.line.substring(  Var.var1 ,Var.var2 )

Please note that var1 and var2 should be in integer format.

 

Convert the xml_output to Document format.

image.png

 

Connect it to tXMLMap and right click the xml as shown below to load the structure from a file. Please note that you need to copy the XML to a file so that you can select the file to create the structure automatically.

 

image.png

 

Now you have the entire structure ready in front of you and you just have to drag and drop to output.

image.png

 And you have the output ready !!! :-)

 

Please note that in the first tMap, if the search for XML did not get any result, job may fail. So make sure that all the records have this pattern or add the extra logic there (its a task for you :-) )

 

Hope I have answered your query.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

View solution in original post


All Replies
Highlighted
Employee

Re: Extract sf:AccountId from following message String

Hi,

 

    You are having an XML message inside a JSON array under an object field.

 

    Problem is that the double quotes without escape is breaking the record while reading the data.

 

    If you are going to follow the same method, you will need custom routines to parse the data from the input message. Could you please check with your source team whether its really required to complicate message like this?

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

 

 

Highlighted
Eleven Stars

Re: Extract sf:AccountId from following message String

 Regular expression could help.Below is example with HTML response .

 

https://etladvisors.com/2014/01/07/extracting-data-via-http-part-1/

Regards
Abhishek KUMAR
Highlighted
Six Stars

Re: Extract sf:AccountId from following message String

Thank you for your reply Nikhil!

Can we use other components to extract the sf:AccountId?

Highlighted
Six Stars

Re: Extract sf:AccountId from following message String

Thanks Abhishek! but the given link is not working. 

Highlighted
Eleven Stars

Re: Extract sf:AccountId from following message String

Looks like , They took it off because I checked the website Smiley Happy

 

if you will have only one sf:AccountId if your Input ,you could use tExtractRegexField and the string between <sf:AccountId>xxxxxxxxx</sf:AccountId>

 

try regex "<\\s*sf:AccountId[^>]*>(.*?)<\\s*/\\s*sf:AccountId>.*"

 

Otherwise, using tJavarow , get with regex Pattern get all <sf:AccountId>xxxxxxxxx</sf:AccountId> in a response 

 

so output of this

<sf:AccountId>xxxxxxxxx</sf:AccountId><sf:AccountId>xxxxxxxxx2</sf:AccountId>

 

then denormalize  into 

xxxxxxxxx

xxxxxxxxx2

 

etc.

 

Also 

 

 

Regards
Abhishek KUMAR
Highlighted
Six Stars

Re: Extract sf:AccountId from following message String

Thanks Abhishek!

I am getting an error. Can you give me a tjavarow code pls.

 

I am executing the below code: 

Pattern p = Pattern.compile("0012F00000M5ACgQAN");
Matcher m = p.matcher("0012F00000M5ACgQAN");
while(m.find()) {
System.out.println(m.group());
}

 

Output is:

[statistics] connecting to socket on port 3495
[statistics] connected
0012F00000M5ACgQAN
.----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------.
| tLogRow_3 |
|=--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------=|
|Column0 |
|=--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------=|
|[{"data":"<?xml version="1.0" encoding="UTF-8"?> <soapenv:Envelope 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"> <soapenv:Body> <notifications xmlns="http://soap.sforce.com/2005/09/outbound"> <OrganizationId>00D2F000000DGMUUA4</OrganizationId> <ActionId>04k2F0000004CGOQA2</ActionId> <SessionId xsi:nil="true"/> <EnterpriseUrl>https://behavecareus--dev.my.salesforce.com/services/Soap/c/44.0/00D2F000000DGMU</EnterpriseUrl> <PartnerUrl>https://behavecareus--dev.my.salesforce.com/services/Soap/u/44.0/00D2F000000DGMU</PartnerUrl> <Notification> <Id>04l2F000007wBMHQA2</Id> <sObject xsi:type="sf:Contact" xmlns:sf="urn:sobject.enterprise.soap.sforce.com"> <sf:Id>0032F00000KAlWDQA1</sf:Id> <sf:AccountId>0012F00000M5ACgQAN</sf:AccountId> </sObject> </Notification> </notifications> </soapenv:Body> </soapenv:Envelope>","type":"text/xml; charset=UTF-8"}]|
'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------'

.-------.
|tLogRow_4|
|=-----=|
|Column0|
|=-----=|
|null |
'-------'

[statistics] disconnected

Highlighted
Employee

Re: Extract sf:AccountId from following message String

@pgajghate 

 

I hope you are looking for below solution :-)

image.png

 

Below are the component screenshots. You need to read the record tFileInputFullRow so that entire data will be in a single line. The next step is tMap as shown below.

image.png

 

var1   ->         row1.line.indexOf("<?xml version=")
var2   ->         row1.line.indexOf("\",\"type")

xml_out ->     row1.line.substring(  Var.var1 ,Var.var2 )

Please note that var1 and var2 should be in integer format.

 

Convert the xml_output to Document format.

image.png

 

Connect it to tXMLMap and right click the xml as shown below to load the structure from a file. Please note that you need to copy the XML to a file so that you can select the file to create the structure automatically.

 

image.png

 

Now you have the entire structure ready in front of you and you just have to drag and drop to output.

image.png

 And you have the output ready !!! :-)

 

Please note that in the first tMap, if the search for XML did not get any result, job may fail. So make sure that all the records have this pattern or add the extra logic there (its a task for you :-) )

 

Hope I have answered your query.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

View solution in original post

Highlighted
Six Stars

Re: Extract sf:AccountId from following message String

Many Thanks Nikhil! Great explanation.

Highlighted
Eleven Stars

Re: Extract sf:AccountId from following message String

Above approach is better for current Request. Adding Just for Logging purpose.

How to achieve this with Regex Pattern

TalendImage.JPG

Regards
Abhishek KUMAR
Highlighted
Six Stars

Re: Extract sf:AccountId from following message String

Thank you Guys!

 

I have found one more approach here:WithoutUnicode.png

 

 

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog