Skip to content

Example 3. XML to JSON and back again

Jump to bottom
jameskrobinson edited this page Aug 8, 2021 · 6 revisions

The examples below show how it's possible to work with JSON data using the UDTF. The templates used in the examples are available in the repository.

1. XML to JSON

An example with FPML:

select xsltable(xml_text,'/home/james_k_robinson/xsl/xml_to_json.xsl') from fpml.trade_xml t;

xml to JSON 1

It's necessary to join all of the output up into a single row (or use a different row separator...):

select t.trade_id, concat_ws('',collect_list(x.col1)) from fpml.trade_xml t LATERAL VIEW summit.xsltable(xml_text,'/home/james_k_robinson/xsl/xml_to_json.xsl') x where t.trade_id = 'TW9235' group by t.Trade_id;

XML to JSON 2

The pretty-printed output looks as follows:

XML to JSON pretty

2. JSON to XML

It's also possible to convert JSON to XML. It is necessary enclose the JSON is opening and closing XML tags however. For example, if I have the JSON shown above, I need to put it inside an tag:

json_as_xml

Assuming we have this data in a table, converting from JSON to XML is done as follows:

select xsltable (xml_text, '/home/james_k_robinson/xsl/json_to_xml.xsl', 'OUTPUT_METHOD=xml') from fpml.json_trade t;

json_to_xml

Note this following:

  • The actual structure of the XML depends on the contents of the json_to_xml template. In this example, we've just used a very basic approach: <xsl:template match="data"> <xsl:copy-of select="json-to-xml(.)"/> </xsl:template>

  • With a bit of work, it's possible to get the JSON back into much the same format as the source XML (FPML) shown earlier. But that's outside of the scope of this PoC;

  • We pass a parameter, OUTPUT_METHOD=xml, to the UDTF. This is to tell Saxon that we expect the output to be XML, which is not normally the case (the default output method used by the UDTF is "text");

3. JSON to CSV

If we can convert JSON to XML, it stands to reason we can also convert it to csv. Again, here's a very basic example, as expanding this nice-to-have is beyond the scope of this PoC.

Source JSON (which needs to be unclosed in xml tags):

json_to_csv_data

SQL: select xsltable (xml_text, '/home/james_k_robinson/xsl/json_to_csv.xsl') from fpml.json_legs l;

Results:

json_to_csv_results

4. Conclusion

It's possible to use XSL to transform JSON into XML, XML to JSON and JSON to just about anything else. The ability to do this within Hive, where your JSON or XML data resides, will hopefully save you a lot of time, and reduce the amount of copying / pasting / needlessly converting you do on a daily basis.

Next section: Example 4

Clone this wiki locally