topic Re: tExtractXMLField only gets the first element in Talend Studio

tExtractXMLField only gets the first element

Anonymous — Sat, 16 Nov 2024 12:48:01 GMT

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 😞

Re: tExtractXMLField only gets the first element

Anonymous — Sat, 16 Jul 2011 07:48:00 GMT

Hi
In you case, you need to set the Loop XPath query as: "/search_results/products/product"
Best regards
Shong

Re: tExtractXMLField only gets the first element

Anonymous — Sat, 16 Jul 2011 13:05:19 GMT

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?

Re: tExtractXMLField only gets the first element

Anonymous — Mon, 18 Jul 2011 02:10:40 GMT

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

Re: tExtractXMLField only gets the first element

Anonymous — Mon, 18 Jul 2011 02:19:39 GMT

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.

Re: tExtractXMLField only gets the first element

Anonymous — Mon, 18 Jul 2011 02:53:19 GMT

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<N>/@id
Please see my screenshot.

Best regard
Shong

Re: tExtractXMLField only gets the first element

Anonymous — Mon, 18 Jul 2011 03:39:28 GMT

.. 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.

Re: tExtractXMLField only gets the first element

Anonymous — Mon, 18 Jul 2011 04:26:44 GMT

.. 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

Re: tExtractXMLField only gets the first element

Anonymous — Tue, 09 Aug 2011 08:11:45 GMT

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

Re: tExtractXMLField only gets the first element

Anonymous — Tue, 09 Aug 2011 08:14:18 GMT

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

Re: tExtractXMLField only gets the first element

Anonymous — Wed, 10 Aug 2011 02:27:23 GMT

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

Re: tExtractXMLField only gets the first element

Anonymous — Wed, 10 Aug 2011 02:34:56 GMT

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