Converting XML to JSON using xmlTransform()

December 22, 2018
Wizard 20 posts
Followers: 10 people
2

Converting XML to JSON using xmlTransform()

Wizard 20 posts
Followers: 10 people
December 22, 2018

I know very little about XML other than it’s a pain in the !@#$% to parse, search, and use.  I’m much more familiar with JSON and using ColdFusion structures and arrays.  That being said, I’m working on a project that requires that I pull a data feed of a big, ugly XML document that’s complex and cumbersome to utilize.  The XML is coming from TransUnion (one of the “big three” credit companies in the United States) and it’s a representation of a credit report on a consumer.

For sake of understanding my pain, here’s the XML.  (Don’t worry… this is test data from their testing environment; this isn’t someone’s actual credit report.)

<?xml version="1.0" encoding="UTF-8"?>
<creditBureau xmlns="http://www.transunion.com/namespace" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.transunion.com/namespace">
 <document>response</document>
 <version>2.24</version>
 <transactionControl>
 <userRefNumber>20999</userRefNumber>
 <subscriber>
 <industryCode>Z</industryCode>
 <memberCode>3011613</memberCode>
 <inquirySubscriberPrefixCode>1204</inquirySubscriberPrefixCode>
 </subscriber>
 <options>
 <country>us</country>
 <language>en</language>
 <productVersion>standard</productVersion>
 </options>
 <tracking>
 <transactionTimeStamp>2018-12-22T13:24:41.751-06:00</transactionTimeStamp>
 </tracking>
 </transactionControl>
 <product>
 <code>07000</code>
 <subject>
 <number>1</number>
 <subjectRecord>
 <fileNumber>01</fileNumber>
 <fileSummary>
 <fileHitIndicator>regularHit</fileHitIndicator>
 <ssnMatchIndicator>exact</ssnMatchIndicator>
 <consumerStatementIndicator>false</consumerStatementIndicator>
 <market>03</market>
 <submarket>PE</submarket>
 <creditDataStatus>
 <suppressed>false</suppressed>
 <doNotPromote>
 <indicator>false</indicator>
 </doNotPromote>
 <freeze>
 <indicator>false</indicator>
 </freeze>
 <minor>false</minor>
 <disputed>false</disputed>
 </creditDataStatus>
 <inFileSinceDate estimatedDay="false" estimatedMonth="false" estimatedCentury="false" estimatedYear="false">1995-05-31</inFileSinceDate>
 </fileSummary>
 <indicative>
 <name source="file">
 <person>
 <first>SAUCY</first>
 <last>CRANBERRY</last>
 </person>
 </name>
 <address source="file">
 <status>current</status>
 <qualifier>personal</qualifier>
 <street>
 <number>876</number>
 <name>TURKEY</name>
 <preDirectional>W</preDirectional>
 <type>WY</type>
 </street>
 <location>
 <city>FANTASY ISLAND</city>
 <state>IL</state>
 <zipCode>60750</zipCode>
 </location>
 <dateReported estimatedDay="false" estimatedMonth="false" estimatedCentury="false" estimatedYear="false">2016-10-05</dateReported>
 </address>
 <address source="file">
 <status>previous</status>
 <qualifier>personal</qualifier>
 <street>
 <number>3</number>
 <name>HOLIDAY</name>
 <type>AV</type>
 </street>
 <location>
 <city>FANTASY ISLAND</city>
 <state>IL</state>
 <zipCode>60750</zipCode>
 </location>
 <dateReported estimatedDay="false" estimatedMonth="false" estimatedCentury="false" estimatedYear="false">2014-09-24</dateReported>
 </address>
 <socialSecurity source="file">
 <number>666222525</number>
 </socialSecurity>
 <dateOfBirth estimatedDay="false" estimatedMonth="false" estimatedCentury="false" estimatedYear="false" source="file">1982-01-01</dateOfBirth>
 <employment source="file">
 <employer>
 <unparsed>U OF M</unparsed>
 </employer>
 <occupation>NOT APPLICABLE</occupation>
 <dateHired estimatedDay="false" estimatedMonth="false" estimatedCentury="false" estimatedYear="false">2013-12-01</dateHired>
 <dateOnFileSince estimatedDay="false" estimatedMonth="false" estimatedCentury="false" estimatedYear="false">2018-11-01</dateOnFileSince>
 <dateEffective estimatedDay="false" estimatedMonth="false" estimatedCentury="false" estimatedYear="false">2018-11-10</dateEffective>
 </employment>
 <employment source="file">
 <employer>
 <unparsed>XYZEE CO</unparsed>
 </employer>
 <dateOnFileSince estimatedDay="false" estimatedMonth="false" estimatedCentury="false" estimatedYear="false">2018-11-01</dateOnFileSince>
 <dateEffective estimatedDay="false" estimatedMonth="false" estimatedCentury="false" estimatedYear="false">2018-11-10</dateEffective>
 </employment>
 </indicative>
 <custom>
 <credit>
 <trade>
 <subscriber>
 <industryCode>C</industryCode>
 <memberCode>02163001</memberCode>
 <name>
 <unparsed>NBGL-SAKS</unparsed>
 </name>
 </subscriber>
 <portfolioType>revolving</portfolioType>
 <accountNumber>4</accountNumber>
 <ECOADesignator>jointContractLiability</ECOADesignator>
 <dateOpened estimatedDay="false" estimatedMonth="false" estimatedCentury="false" estimatedYear="false">2009-05-19</dateOpened>
 <dateEffective estimatedDay="false" estimatedMonth="false" estimatedCentury="false" estimatedYear="false">2018-11-01</dateEffective>
 <currentBalance>000000607</currentBalance>
 <highCredit>000003032</highCredit>
 <accountRating>09</accountRating>
 <remark>
 <code>PRL</code>
 <type>affiliate</type>
 </remark>
 <account>
 <type>CH</type>
 </account>
 <pastDue>000000140</pastDue>
 <paymentHistory />
 <updateMethod>locked</updateMethod>
 </trade>
 <trade>
 <subscriber>
 <industryCode>B</industryCode>
 <memberCode>01597029</memberCode>
 <name>
 <unparsed>BK OF AMER</unparsed>
 </name>
 </subscriber>
 <portfolioType>revolving</portfolioType>
 <accountNumber>7550</accountNumber>
 <ECOADesignator>individual</ECOADesignator>
 <dateOpened estimatedDay="false" estimatedMonth="false" estimatedCentury="false" estimatedYear="false">2017-01-12</dateOpened>
 <dateEffective estimatedDay="false" estimatedMonth="false" estimatedCentury="false" estimatedYear="false">2018-11-01</dateEffective>
 <highCredit>000001005</highCredit>
 <creditLimit>000002500</creditLimit>
 <accountRating>01</accountRating>
 <account>
 <type>CH</type>
 </account>
 <paymentHistory>
 <paymentPattern>
 <startDate estimatedDay="false" estimatedMonth="false" estimatedCentury="false" estimatedYear="false">2018-10-01</startDate>
 <text>111111111111111111111</text>
 </paymentPattern>
 <historicalCounters>
 <monthsReviewedCount>21</monthsReviewedCount>
 <late30DaysTotal>00</late30DaysTotal>
 <late60DaysTotal>00</late60DaysTotal>
 <late90DaysTotal>00</late90DaysTotal>
 </historicalCounters>
 </paymentHistory>
 <updateMethod>automated</updateMethod>
 </trade>
 <trade>
 <subscriber>
 <industryCode>B</industryCode>
 <memberCode>0848R015</memberCode>
 <name>
 <unparsed>WILM TRUST</unparsed>
 </name>
 </subscriber>
 <portfolioType>mortgage</portfolioType>
 <accountNumber>698007</accountNumber>
 <ECOADesignator>individual</ECOADesignator>
 <dateOpened estimatedDay="false" estimatedMonth="false" estimatedCentury="false" estimatedYear="false">1989-11-20</dateOpened>
 <dateEffective estimatedDay="false" estimatedMonth="false" estimatedCentury="false" estimatedYear="false">2018-11-01</dateEffective>
 <currentBalance>000003327</currentBalance>
 <highCredit>000036500</highCredit>
 <accountRating>01</accountRating>
 <terms>
 <paymentFrequency>monthly</paymentFrequency>
 <paymentScheduleMonthCount>300</paymentScheduleMonthCount>
 <scheduledMonthlyPayment>000000163</scheduledMonthlyPayment>
 </terms>
 <account>
 <type>RE</type>
 </account>
 <paymentHistory>
 <paymentPattern>
 <startDate estimatedDay="false" estimatedMonth="false" estimatedCentury="false" estimatedYear="false">2018-10-01</startDate>
 <text>111111111111111111111111111111111X1111X111X11111</text>
 </paymentPattern>
 <historicalCounters>
 <monthsReviewedCount>48</monthsReviewedCount>
 <late30DaysTotal>00</late30DaysTotal>
 <late60DaysTotal>00</late60DaysTotal>
 <late90DaysTotal>00</late90DaysTotal>
 </historicalCounters>
 </paymentHistory>
 <updateMethod>manual</updateMethod>
 </trade>
 <trade>
 <subscriber>
 <industryCode>F</industryCode>
 <memberCode>0321E430</memberCode>
 <name>
 <unparsed>AVCO FINANCE</unparsed>
 </name>
 </subscriber>
 <portfolioType>installment</portfolioType>
 <accountNumber>3244003</accountNumber>
 <ECOADesignator>individual</ECOADesignator>
 <dateOpened estimatedDay="false" estimatedMonth="false" estimatedCentury="false" estimatedYear="false">2015-06-12</dateOpened>
 <dateEffective estimatedDay="false" estimatedMonth="false" estimatedCentury="false" estimatedYear="false">2018-11-01</dateEffective>
 <currentBalance>000000456</currentBalance>
 <highCredit>000003591</highCredit>
 <accountRating>01</accountRating>
 <terms>
 <paymentFrequency>monthly</paymentFrequency>
 <paymentScheduleMonthCount>036</paymentScheduleMonthCount>
 <scheduledMonthlyPayment>000000145</scheduledMonthlyPayment>
 </terms>
 <account>
 <type>SH</type>
 </account>
 <paymentHistory>
 <paymentPattern>
 <startDate estimatedDay="false" estimatedMonth="false" estimatedCentury="false" estimatedYear="false">2018-10-01</startDate>
 <text>X11111111111111X1111111111X1</text>
 </paymentPattern>
 <historicalCounters>
 <monthsReviewedCount>28</monthsReviewedCount>
 <late30DaysTotal>00</late30DaysTotal>
 <late60DaysTotal>00</late60DaysTotal>
 <late90DaysTotal>00</late90DaysTotal>
 </historicalCounters>
 </paymentHistory>
 <updateMethod>manual</updateMethod>
 </trade>
 <trade>
 <subscriber>
 <industryCode>F</industryCode>
 <memberCode>0832D042</memberCode>
 <name>
 <unparsed>WM FINANCE</unparsed>
 </name>
 </subscriber>
 <portfolioType>revolving</portfolioType>
 <accountNumber>20</accountNumber>
 <ECOADesignator>jointContractLiability</ECOADesignator>
 <dateOpened estimatedDay="false" estimatedMonth="false" estimatedCentury="false" estimatedYear="false">2011-01-13</dateOpened>
 <dateEffective estimatedDay="false" estimatedMonth="false" estimatedCentury="false" estimatedYear="false">2018-11-01</dateEffective>
 <currentBalance>000000072</currentBalance>
 <highCredit>000001005</highCredit>
 <creditLimit>000002500</creditLimit>
 <accountRating>01</accountRating>
 <paymentHistory>
 <paymentPattern>
 <startDate estimatedDay="false" estimatedMonth="false" estimatedCentury="false" estimatedYear="false">2018-10-01</startDate>
 <text>1111111111111111111111111X11111111111X11111</text>
 </paymentPattern>
 <historicalCounters>
 <monthsReviewedCount>43</monthsReviewedCount>
 <late30DaysTotal>00</late30DaysTotal>
 <late60DaysTotal>00</late60DaysTotal>
 <late90DaysTotal>00</late90DaysTotal>
 </historicalCounters>
 </paymentHistory>
 <updateMethod>manual</updateMethod>
 </trade>
 <trade>
 <subscriber>
 <industryCode>B</industryCode>
 <memberCode>01DTV001</memberCode>
 <name>
 <unparsed>CAPITAL ONE</unparsed>
 </name>
 </subscriber>
 <portfolioType>revolving</portfolioType>
 <accountNumber>8690</accountNumber>
 <ECOADesignator>jointContractLiability</ECOADesignator>
 <dateOpened estimatedDay="false" estimatedMonth="false" estimatedCentury="false" estimatedYear="false">2012-05-18</dateOpened>
 <dateEffective estimatedDay="false" estimatedMonth="false" estimatedCentury="false" estimatedYear="false">2018-11-01</dateEffective>
 <currentBalance>000000677</currentBalance>
 <creditLimit>000001000</creditLimit>
 <accountRating>01</accountRating>
 <paymentHistory>
 <paymentPattern>
 <startDate estimatedDay="false" estimatedMonth="false" estimatedCentury="false" estimatedYear="false">2018-10-01</startDate>
 <text>1111111XX11X1XX1X1X11111</text>
 </paymentPattern>
 <historicalCounters>
 <monthsReviewedCount>24</monthsReviewedCount>
 <late30DaysTotal>00</late30DaysTotal>
 <late60DaysTotal>00</late60DaysTotal>
 <late90DaysTotal>00</late90DaysTotal>
 </historicalCounters>
 </paymentHistory>
 <updateMethod>manual</updateMethod>
 </trade>
 <inquiry>
 <ECOADesignator>individual</ECOADesignator>
 <subscriber>
 <industryCode>Z</industryCode>
 <memberCode>03011613</memberCode>
 <inquirySubscriberPrefixCode>12MC</inquirySubscriberPrefixCode>
 <name>
 <unparsed>APM</unparsed>
 </name>
 </subscriber>
 <date estimatedDay="false" estimatedMonth="false" estimatedCentury="false" estimatedYear="false">2018-12-22</date>
 </inquiry>
 <inquiry>
 <ECOADesignator>individual</ECOADesignator>
 <subscriber>
 <industryCode>B</industryCode>
 <memberCode>02382255</memberCode>
 <inquirySubscriberPrefixCode>40LO</inquirySubscriberPrefixCode>
 <name>
 <unparsed>HSBC</unparsed>
 </name>
 </subscriber>
 <date estimatedDay="false" estimatedMonth="false" estimatedCentury="false" estimatedYear="false">2018-11-12</date>
 </inquiry>
 <creditSummary>
 <revolvingAmount>
 <percentAvailableCredit>077</percentAvailableCredit>
 <highCredit>000005042</highCredit>
 <creditLimit>000006000</creditLimit>
 <currentBalance>000001356</currentBalance>
 <pastDue>000000140</pastDue>
 <monthlyPayment>000000000</monthlyPayment>
 </revolvingAmount>
 <installmentAmount>
 <highCredit>000003591</highCredit>
 <creditLimit>000000000</creditLimit>
 <currentBalance>000000456</currentBalance>
 <pastDue>000000000</pastDue>
 <monthlyPayment>000000145</monthlyPayment>
 </installmentAmount>
 <mortgageAmount>
 <highCredit>000036500</highCredit>
 <creditLimit>000000000</creditLimit>
 <currentBalance>000003327</currentBalance>
 <pastDue>000000000</pastDue>
 <monthlyPayment>000000163</monthlyPayment>
 </mortgageAmount>
 <totalAmount>
 <highCredit>000045133</highCredit>
 <creditLimit>000006000</creditLimit>
 <currentBalance>000005139</currentBalance>
 <pastDue>000000140</pastDue>
 <monthlyPayment>000000308</monthlyPayment>
 </totalAmount>
 <recordCounts reportingPeriod="totalHistory">
 <publicRecordCount>000</publicRecordCount>
 <collectionCount>000</collectionCount>
 <totalTradeCount>006</totalTradeCount>
 <negativeTradeCount>001</negativeTradeCount>
 <historicalNegativeTradeCount>000</historicalNegativeTradeCount>
 <historicalNegativeOccurrencesCount>000</historicalNegativeOccurrencesCount>
 <revolvingTradeCount>004</revolvingTradeCount>
 <installmentTradeCount>001</installmentTradeCount>
 <mortgageTradeCount>001</mortgageTradeCount>
 <openTradeCount>000</openTradeCount>
 <unspecifiedTradeCount>000</unspecifiedTradeCount>
 <totalInquiryCount>002</totalInquiryCount>
 </recordCounts>
 </creditSummary>
 </credit>
 </custom>
 <addOnProduct>
 <code>06500</code>
 <status>delivered</status>
 <highRiskFraudAlert searchStatus="availableClear" />
 </addOnProduct>
 <addOnProduct>
 <code>00P02</code>
 <status>delivered</status>
 <scoreModel>
 <score>
 <results>+633</results>
 <derogatoryAlert>false</derogatoryAlert>
 <fileInquiriesImpactedScore>true</fileInquiriesImpactedScore>
 <factors>
 <factor>
 <rank>1</rank>
 <code>039</code>
 </factor>
 <factor>
 <rank>2</rank>
 <code>013</code>
 </factor>
 <factor>
 <rank>3</rank>
 <code>010</code>
 </factor>
 <factor>
 <rank>4</rank>
 <code>018</code>
 </factor>
 </factors>
 </score>
 </scoreModel>
 </addOnProduct>
 </subjectRecord>
 </subject>
 </product>
</creditBureau>

Ugly, right?  It would be much better to represent that data via JSON.  Here’s where ColdFusion comes in.

At first, I was going to try and use xmlParse() and xmlSearch() to be able to extrapolate the data from the resulting XML document.  Here’s what I was using:

variables.dateOfBirth = xmlSearch(thisSubject,"//*[local-name()='dateOfBirth']")[1].xmlText;

And it just seemed kludgey and wrong.  That’s when I remembered the xmlTransform() function.  I thought to myself, “Self… you should be able to find an .xsl stylesheet that will transform this XML into JSON and then use different means of being able to address the information inside it.

A little searching and I came across this excellent xml-to-json.xsl file that does the trick.  I downloaded this file to my server and modified the transUnion.cfc file I’ve been working on to include the following code in the XML parser and data extrapolation function I’m building.

local.Response = deserializeJSON(
   xmlTransform(
      xmlParse(arguments.XML),
      expandPath('/Assets/XML/xmlToJSON.xsl')
   )
).creditBureau;

Voila! My complicated XML file is now a nice neat ColdFusion structure that I can fiddle around with.

Enjoy!

Comments (2)
2018-12-26 18:00:49
2018-12-26 18:00:49

Applause needs to go out for that one.

Like
(1)
2018-12-26 14:41:41
2018-12-26 14:41:41

I received the exact same results using 1 line of Java using built-in JSON-Java w/toJSONObject().  I wasn’t required to provide XSL file and the performance was consistently faster (0-3ms versus 119-910ms)

Here’s a link so you can try it out. (Paste your XML in to see the result.)
https://www.trycf.com/gist/00bb362672f772fab56d26f3e01ad3fa/acf11

<CFSET JSONText = createObject(“java”,”org.json.XML”).toJSONObject(XMLText)>
<CFSET JSONObject = DeserializeJSON(JSONText)>

My short blog entry regarding this is located here:
https://gamesover2600.tumblr.com/post/180776378949/convert-xml-to-json-in-1-line-of-code-using

NOTE: If you want to retain numeric data types,  you’ll need to use JavaLoader and use a newer version of JSON-Java that supports a secondary “true” argument. This will safely convert number values to text strings instead of converting to scientific notation or rounding.

Like
(2)
Add your comment