tExtractXMLField only gets the first element

Highlighted
Four Stars

tExtractXMLField only gets the first element

I have XML coming into the tExtractXMLField that looks like this:
<?xml version="1.0" encoding="UTF-8"?>
<search_results product_limit="25" product_offset="0" brand_id="3595">
<products total_count="24">
<product retail_price="239.99" merchant_id="2826" id="286777730"/>
<product retail_price="239.99" merchant_id="2431" id="297971660"/>
<product retail_price="219.99" merchant_id="2431" id="397971661"/>
<product retail_price="239.99" merchant_id="2823" id="354015223"/>
</products>
<merchants>
<merchant product_count="8" network_id="5" merchant_type_id="9" id="2826"/>
<merchant product_count="7" network_id="2" merchant_type_id="9" id="2431"/>
<merchant product_count="3" network_id="2" merchant_type_id="9" id="2823"/>
</merchants>
</search_results>
In the tExtractXMLField, the Loop XPath query is set to: "/search_results"
And the Mapping to get the "id" field from the "product" elements is set to "./products/product/@id"
But I only get 1 row of output, and it's always the first product in the set (id="286777730" in example above)
I've tried setting the "Get Nodes" checkbox for that column (the doc says that setting this will "recuperate" the contents of the select node - not sure what this means)
I've also tried setting the Loop Xpath query to "/search_results/products" and the mapping for "id" to "./product/@id" ... no luck.
It must be something wrong with either my Loop Xpath query or my mapping Xpath ...
Anyone have any idea why I'm not seeing 4 rows of output?
Edit: The <products> section of the example XML was shortened for use in this example, but does actually have 24 <product> elements. And I only get #1 :-(
Community Manager

Re: tExtractXMLField only gets the first element

Hi
In you case, you need to set the Loop XPath query as: "/search_results/products/product"
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Four Stars

Re: tExtractXMLField only gets the first element

Wooo hooo! Thank you Shong!
I set the Loop Xpath query to "/search_results/products/product", and then the Xpath query for a column to "./@id" and now I get all the elements!
But now what if I also want to get the id for each of the "merchant" elements? I think that's why I was attempting to use a higher-level Loop Xpath query like "/search_results", thinking that I could then populate columns for the different sections using "./products/product/@id" and then also "./merchants/merchant/@id"
But I guess that's not possible?
Community Manager

Re: tExtractXMLField only gets the first element

Hi
Using another tExtractXMLField to extract the emrchant element just like you did on product element, and then do a join between the two output result if needed.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Four Stars

Re: tExtractXMLField only gets the first element

I just tried the "backing up" syntax to back out of the /products/product and try and then get to merchants/merchant in the Xpath query:
"../../merchants/merchant/@id"
But that only give me the first merchant of the set.
Do I really have to use 2 tExtractXMLField components, one for products and one for merchants? They are in the same XML document -- there must be some easy way to get at both sets of data that I'm not seeing.
Community Manager

Re: tExtractXMLField only gets the first element

Hi
If you are sure there are always a fixed number of product elements and merchant elements, you can set the xpath query as below to get the value of the N element:
products/product/@id
Please see my screenshot.

Best regard
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Four Stars

Re: tExtractXMLField only gets the first element

.. No, the number could vary all the time. Thanks anyway.
I'll file a request in the Suggestions forum for a more flexible looping Xpath.
Community Manager

Re: tExtractXMLField only gets the first element

.. No, the number could vary all the time. Thanks anyway.
I'll file a request in the Suggestions forum for a more flexible looping Xpath.

If the number could vary all the time, you have to use tExtractXMLField components, one for product elements and another one for merchant elements, for example:
the xml document flow---tReplicate--main--tExtractXMLField _1--main--file or memory
--main--tExtractXMLField _2--main--file or memory
and then do the join between the two output if needed.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: tExtractXMLField only gets the first element

This is a somewhat related question so I'll post it in here. I'm trying to use tExtractXMLFields to parse some output from tStandardizeRow.
Example input coming from tStandardizeRow:
<record>
<NumberOnly>469-491</NumberOnly>
<NumberOnly>499-523</NumberOnly>
<UNMATCHED>
<UNDEFINED>&</UNDEFINED>
</UNMATCHED>
</record>

When I run this through tExtractXMLFields with path of "/record" it will only extract the first NumberOnly field. Is there any way to extract all of them?
Thanks
Community Manager

Re: tExtractXMLField only gets the first element

When I run this through tExtractXMLFields with path of "/record" it will only extract the first NumberOnly field. Is there any way to extract all of them?
Thanks

To get all of NumberOnly field, you should set the loop path as "/record/NumberOnly", so now the loop element is NumberOnly, not record.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: tExtractXMLField only gets the first element

Hi Shong,
I have set the xpath loop to "record/NumberOnly" as you suggested, but now I'm having trouble setting the xPathQuery to extract the field. I have tried "NumberOnly" but that gave blank since there is no NumberOnly element inside itself. What should the xPathQuery be set to?
Thanks
Community Manager

Re: tExtractXMLField only gets the first element

Hi
I suggest you to create a XML metadata with wizards, you are able to set the xpath loop element and the xpathQuery by drag&drop columns.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business

2019 GARNER 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

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch