Handling special characters in JSON keys during transformation

Archives CData Virtuality [Archived]
,
1

When transforming JSON documents into a tabular format, you might stumble upon certain keys that produce an error message.

This document is valid JSON, but generates an error, such as “XPath syntax error at char 0 in {80x}:
Separator needed after numeric literal” when parsing it in Data Virtuality.

{  "80x60": "https://datavirtuality.com/wp-content/uploads/sites/2/2016/09/solutions@3x.png",}

SELECT xmlTable.*FROM (call "file".getFiles('key_example.json')) f,XMLTABLE(XMLNAMESPACES( 'http://www.w3.org/2001/XMLSchema-instance' as "xsi" ),'/root' PASSING JSONTOXML('root',to_chars(f.file,'UTF-8')) COLUMNS "80x60" STRING PATH '80x60') "xmlTable"

This is due to the fact that Data Virtuality internally transforms the JSON document into an XML document for easier parsing. Since an identifier in XML may not start with a number, it gets escaped and rewritten to _u0038_0x60. Hence, you will need to pass this value as an XPath expression for referring to your JSON key.

SELECT xmlTable.*FROM (call "file".getFiles('key_example.json')) f,XMLTABLE(XMLNAMESPACES( 'http://www.w3.org/2001/XMLSchema-instance' as "xsi" ),'/root' PASSING JSONTOXML('root',to_chars(f.file,'UTF-8')) COLUMNS "80x60" STRING PATH '_u0038_0x60') "xmlTable"

In order to look up this transformed key name, you can run the XMLESCAPENAME function (SELECT XMLESCAPENAME(‘80x60’, true)) which will return the correct name of the node in the XML document.

1 Like