Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

How to iterate over xml tags?

Solved Go to solution
Highlighted

How to iterate over xml tags?

New Contributor

Hi everyone.


I have the following XML: <?xml version="1.0" encoding="UTF-8"?> <flow xmlns:*** xmlns:xsi=*** xsi:schemaLocation=***> <version>1.0</version> <id>15</id> <date>2018-01-01</date> <sender> <senderId> <idType>G</idType> <idCode>code</idCode> </senderId> <senderName>name</senderName> </sender> <paymentsNumber>2</paymentsNumber> <paymentsTotal>800.40</paymentsTotal> <payment> <paymentId>1</paymentId> <paymentAmount>400.20</paymentAmount> <paymentResult>0</paymentResult> <paymentDate>2018-02-01</paymentDate> </payment> <payment> <paymentId>2</paymentId> <paymentAmount>400.20</paymentAmount> <paymentResult>0</paymentResult> <paymentDate>2018-02-02</paymentDate> </payment> </flow>

I need to extract all tags values and to execute insert queries on different tables. In particular:

- <version>, <id>, <date>, <idType>, <idCode>, <senderName>, <paymentsNumber>, <paymentsTotal> need to be inserted in one table

- <version>, <id>, <date> and all tags values inside <payment> need to be inserted in another table (an insert query for every <payment> tag occurrence)

How can I achieve this result? I tried to use ReplaceText + PutSQL, but this combination seems to not be useful.

Any help will be appreciated!

Regards.

1 ACCEPTED SOLUTION

Accepted Solutions
2 REPLIES 2

Re: How to iterate over xml tags?

New Contributor

Thank you @Timothy Spann, I combined TransformXML, SplitXML and EvaluateXPath, and I obtained the desired result.

Don't have an account?
Coming from Hortonworks? Activate your account here