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 convert XML with many child fields to JSON and then to DB table?

How to convert XML with many child fields to JSON and then to DB table?

Contributor

Hi,folks!

Please tell me how I need to parse this XML below with .xsd file with ValidateXML processor and .xsl file with TransformXML processor then send all of those attributes into relational DB with PutSQL and get the final table like (below)?:


|CNUM|clientName|qualInvestor|agreementNum|agreementService|agreementDate| => there are column names

|AAAAA|Batman Fargo| true | 33067 | blablabla | 2018-10-01 | => column values in row

|AAAAA|Batman Fargo| true | 55121 | blablabla | 2018-10-01 | => column values in row

|AAAAA|Batman Fargo| true | 60012 | blablabla | 2018-10-01 | => column values in row


Sorry for this "nice" table :)

<?xml version="1.0" encoding="UTF-8"?>
<clientList xmlns="http:blablabla" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="blablabla ClientList.xsd">
	<client>
		<CNUM>AAAAA</CNUM>
		<clientName>Batman Fargo</clientName>
		<qualInvestor>true</qualInvestor>
		<agreementList>
			<agreement>
				<agreementNum>33067</agreementNum>
				<agreementService>blablabla</agreementService>
				<agreementDate>2018-10-01</agreementDate>
			</agreement>    
			<agreement>
				<agreementNum>55121</agreementNum>
				<agreementService>blablabla</agreementService>
				<agreementDate>2018-10-01</agreementDate>
			</agreement>  <agreement>
				<agreementNum>60012</agreementNum>
				<agreementService>blablabla</agreementService>
				<agreementDate>2018-10-01</agreementDate>
			</agreement>  
		</agreementList>
	</client>
</clientList>
1 REPLY 1

Re: How to convert XML with many child fields to JSON and then to DB table?

Contributor

I've all ready have an answer by my own :) I'll try to public tutorial a little bit later