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