topic Re: XML DATA EXTRACT in Talend Studio

XML DATA EXTRACT

Anonymous — Sat, 16 Nov 2024 02:42:13 GMT

Team, I have below set XML data where i need to extract List ID and Subscriber ID data to target table column: Please help me to know how the "x path query" should be defined in textractxmlfield

<RetrieveResponseMsg xmlns="http://exacttarget.com/wsdl/partnerAPI">
<OverallStatus>MoreDataAvailable</OverallStatus>
<RequestID>35a6ff32-5217-4aa1-9cd3-710259f60988</RequestID>
<Results xsi:type="SentEvent">
<Client>
<ID>100011660</ID>
</Client>
<PartnerKey xsi:nil="true"/>
<PartnerProperties>
<Name>ListID</Name>
<Value>387</Value>
</PartnerProperties>
<PartnerProperties>
<Name>SubscriberID</Name>
<Value>146851</Value>
</PartnerProperties>
<ObjectID xsi:nil="true"/>
<SendID>30095</SendID>
<SubscriberKey>bhartings@denaviary.com</SubscriberKey>
<EventDate>2018-04-20T13:49:37.757</EventDate>
<EventType>Sent</EventType>
<TriggeredSendDefinitionObjectID/>
<BatchID>0</BatchID>

Re: XML DATA EXTRACT

TRF — Tue, 14 Apr 2020 18:01:52 GMT

Had to change a namespace to your XML and closing names at the end giving:

<RetrieveResponseMsg xmlns="http://exacttarget.com/wsdl/partnerAPI" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<OverallStatus>MoreDataAvailable</OverallStatus>
	<RequestID>35a6ff32-5217-4aa1-9cd3-710259f60988</RequestID>
	<Results xsi:type="SentEvent">
		<Client>
			<ID>100011660</ID>
		</Client>
		<PartnerKey xsi:nil="true"/>
		<PartnerProperties>
			<Name>ListID</Name>
			<Value>387</Value>
		</PartnerProperties>
		<PartnerProperties>
			<Name>SubscriberID</Name>
			<Value>146851</Value>
		</PartnerProperties>
		<ObjectID xsi:nil="true"/>
		<SendID>30095</SendID>
		<SubscriberKey>bhartings@denaviary.com</SubscriberKey>
		<EventDate>2018-04-20T13:49:37.757</EventDate>
		<EventType>Sent</EventType>
		<TriggeredSendDefinitionObjectID/>
		<BatchID>0</BatchID>
	</Results>
</RetrieveResponseMsg>

Now, you can do what you want with the following tExtractXMLField component:

The associated schema have 2 fields called ListId and SubscriberId.

The 1rst mapping line is easy, it gives you the values for "Name" targets.

The 2nd is a little more tricky. It starts from "Name" target, moves back to the parent then get the "Value" target field value.

Here is the result:

Starting job test0 at 18:58 14/04/2020.

[statistics] connecting to socket on port 3866
[statistics] connected
.------------+------------.
|        tLogRow_6        |
|=-----------+-----------=|
|ListId      |SubscriberID|
|=-----------+-----------=|
|ListID      |387         |
|SubscriberID|146851      |
'------------+------------'
[statistics] disconnected
Job test0 ended at 18:58 14/04/2020. [exit code=0]

Re: XML DATA EXTRACT

TRF — Wed, 15 Apr 2020 07:53:01 GMT

Apologize, a simple XPath query such as "Value" gives the result!

I also suggest you to change the schema fields name by Name and Value giving the following result (much more readable):

.------------+------.
|     tLogRow_6     |
|=-----------+-----=|
|Name        |Value |
|=-----------+-----=|
|ListID      |387   |
|SubscriberID|146851|
'------------+------'