https://community.cloudera.com/t5/Support-Questions/How-to-iterate-over-xml-tags/m-p/231546#M193390 <P>Hi everyone. </P><P><BR />I have the following XML: &lt;?xml version="1.0" encoding="UTF-8"?&gt; &lt;flow xmlns:*** xmlns:xsi=*** xsi:schemaLocation=***&gt; &lt;version&gt;1.0&lt;/version&gt; &lt;id&gt;15&lt;/id&gt; &lt;date&gt;2018-01-01&lt;/date&gt; &lt;sender&gt; &lt;senderId&gt; &lt;idType&gt;G&lt;/idType&gt; &lt;idCode&gt;code&lt;/idCode&gt; &lt;/senderId&gt; &lt;senderName&gt;name&lt;/senderName&gt; &lt;/sender&gt; &lt;paymentsNumber&gt;2&lt;/paymentsNumber&gt; &lt;paymentsTotal&gt;800.40&lt;/paymentsTotal&gt; &lt;payment&gt; &lt;paymentId&gt;1&lt;/paymentId&gt; &lt;paymentAmount&gt;400.20&lt;/paymentAmount&gt; &lt;paymentResult&gt;0&lt;/paymentResult&gt; &lt;paymentDate&gt;2018-02-01&lt;/paymentDate&gt; &lt;/payment&gt; &lt;payment&gt; &lt;paymentId&gt;2&lt;/paymentId&gt; &lt;paymentAmount&gt;400.20&lt;/paymentAmount&gt; &lt;paymentResult&gt;0&lt;/paymentResult&gt; &lt;paymentDate&gt;2018-02-02&lt;/paymentDate&gt; &lt;/payment&gt; &lt;/flow&gt; </P><P> I need to extract all tags values and to execute insert queries on different tables. In particular: </P><P> - &lt;version&gt;, &lt;id&gt;, &lt;date&gt;, &lt;idType&gt;, &lt;idCode&gt;, &lt;senderName&gt;, &lt;paymentsNumber&gt;, &lt;paymentsTotal&gt; need to be inserted in one table </P><P>- &lt;version&gt;, &lt;id&gt;, &lt;date&gt; and all tags values inside &lt;payment&gt; need to be inserted in another table (an insert query for every &lt;payment&gt; tag occurrence) </P><P>How can I achieve this result? I tried to use ReplaceText + PutSQL, but this combination seems to not be useful. </P><P> Any help will be appreciated! </P><P> Regards.</P> Tue, 27 Feb 2018 22:40:55 GMT basiler82 2018-02-27T22:40:55Z https://community.cloudera.com/t5/Support-Questions/How-to-iterate-over-xml-tags/m-p/231546#M193390 <P>Hi everyone. </P><P><BR />I have the following XML: &lt;?xml version="1.0" encoding="UTF-8"?&gt; &lt;flow xmlns:*** xmlns:xsi=*** xsi:schemaLocation=***&gt; &lt;version&gt;1.0&lt;/version&gt; &lt;id&gt;15&lt;/id&gt; &lt;date&gt;2018-01-01&lt;/date&gt; &lt;sender&gt; &lt;senderId&gt; &lt;idType&gt;G&lt;/idType&gt; &lt;idCode&gt;code&lt;/idCode&gt; &lt;/senderId&gt; &lt;senderName&gt;name&lt;/senderName&gt; &lt;/sender&gt; &lt;paymentsNumber&gt;2&lt;/paymentsNumber&gt; &lt;paymentsTotal&gt;800.40&lt;/paymentsTotal&gt; &lt;payment&gt; &lt;paymentId&gt;1&lt;/paymentId&gt; &lt;paymentAmount&gt;400.20&lt;/paymentAmount&gt; &lt;paymentResult&gt;0&lt;/paymentResult&gt; &lt;paymentDate&gt;2018-02-01&lt;/paymentDate&gt; &lt;/payment&gt; &lt;payment&gt; &lt;paymentId&gt;2&lt;/paymentId&gt; &lt;paymentAmount&gt;400.20&lt;/paymentAmount&gt; &lt;paymentResult&gt;0&lt;/paymentResult&gt; &lt;paymentDate&gt;2018-02-02&lt;/paymentDate&gt; &lt;/payment&gt; &lt;/flow&gt; </P><P> I need to extract all tags values and to execute insert queries on different tables. In particular: </P><P> - &lt;version&gt;, &lt;id&gt;, &lt;date&gt;, &lt;idType&gt;, &lt;idCode&gt;, &lt;senderName&gt;, &lt;paymentsNumber&gt;, &lt;paymentsTotal&gt; need to be inserted in one table </P><P>- &lt;version&gt;, &lt;id&gt;, &lt;date&gt; and all tags values inside &lt;payment&gt; need to be inserted in another table (an insert query for every &lt;payment&gt; tag occurrence) </P><P>How can I achieve this result? I tried to use ReplaceText + PutSQL, but this combination seems to not be useful. </P><P> Any help will be appreciated! </P><P> Regards.</P> Tue, 27 Feb 2018 22:40:55 GMT https://community.cloudera.com/t5/Support-Questions/How-to-iterate-over-xml-tags/m-p/231546#M193390 basiler82 2018-02-27T22:40:55Z https://community.cloudera.com/t5/Support-Questions/How-to-iterate-over-xml-tags/m-p/231547#M193391 <P>Use TransformXML</P><P>Use SplitXML</P><P>or use EvaluateXPath or EvaluateXQuery</P><P>XSLT will work great.</P><P><A href="https://www.linkedin.com/pulse/parsing-xml-logs-nifi-part-1-3-chris-gambino/" target="_blank">https://www.linkedin.com/pulse/parsing-xml-logs-nifi-part-1-3-chris-gambino/</A></P><P><A href="https://pierrevillard.com/2017/09/07/xml-data-processing-with-apache-nifi/" target="_blank">https://pierrevillard.com/2017/09/07/xml-data-processing-with-apache-nifi/</A></P><P><A href="https://community.hortonworks.com/articles/65400/xml-processing-encoding-validation-parsing-splitti.html" target="_blank">https://community.hortonworks.com/articles/65400/xml-processing-encoding-validation-parsing-splitti.html</A></P> Tue, 27 Feb 2018 22:45:14 GMT https://community.cloudera.com/t5/Support-Questions/How-to-iterate-over-xml-tags/m-p/231547#M193391 TimothySpann 2018-02-27T22:45:14Z https://community.cloudera.com/t5/Support-Questions/How-to-iterate-over-xml-tags/m-p/231548#M193392 <P>Thank you <A rel="user" href="https://community.cloudera.com/users/9304/tspann.html" nodeid="9304">@Timothy Spann</A>, I combined TransformXML, SplitXML and EvaluateXPath, and I obtained the desired result. </P> Thu, 01 Mar 2018 23:28:46 GMT https://community.cloudera.com/t5/Support-Questions/How-to-iterate-over-xml-tags/m-p/231548#M193392 basiler82 2018-03-01T23:28:46Z