TO_JSON won't work in cases where.. One file contains: <pet>dog</pet> which converts to {"pet":"dog"}
But another file contains: <pet>dog</pet> <pet>cat</pet> which converts to: {"pet": ["dog", "cat"]} pet as a column in Drill can't be both a varchar and an array of varchar There are a ton of gotcha(s) when dealing with XML.. numeric vs string scalar vs array -----Original Message----- From: Lee, David Sent: Wednesday, April 6, 2022 10:54 AM To: u...@drill.apache.org; dev@drill.apache.org Subject: RE: [DISCUSS] Add schema support for the XML format I wrote something to convert XML to JSON using an XSD schema file to solve fields, types, nested structures, etc.. It's the only real way to ensure column level data integrity. https://github.com/davlee1972/xml_to_json Converts XML to valid JSON or JSONL Requires only two files to get started. Your XML file and the XSD schema file for that XML file. -----Original Message----- From: luoc <l...@apache.org> Sent: Wednesday, April 6, 2022 5:01 AM To: u...@drill.apache.org; dev@drill.apache.org Subject: [DISCUSS] Add schema support for the XML format External Email: Use caution with links and attachments Hello dear driller, Before starting the topic, I would like to do a simple survey : 1. Did you know that Drill already supports XML format? 2. If yes, what is the maximum size for the XML files you normally read? 1MB, 10MB or 100MB 3. Do you expect that reading XML will be as easy as JSON (Schema Discovery)? Thank you for responding to those questions. XML is different from the JSON file, and if we rely solely on the Drill drive to deduce the structure of the data. (or called SCHEMA), the code will get very complex and delicate. For example, inferring array structure and numeric range. So, "provided schema" or "TO_JSON" may be good medicine : Provided Schema We can add the DTD or XML Schema (XSD) support for the XML. It can build all value vectors (Writer) before reading data, solving the fields, types, and complex nested. However, a definition file is actually a rule validator that allows elements to appear 0 or more times. As a result, it is not possible to know if all elements exist until the data is read. Therefore, avoid creating a large number of value vectors that do not actually exist before reading the data. We can build the top schema at the initial stage and add new value vectors as needed during the reading phase. TO_JSON Read and convert XML directly to JSON, using the JSON Reader for data resolution. It makes it easier for us to query the XML data such as JSON, but requires reading the whole XML file in memory. I think the two can be done, so I look forward to your spirited discussion. Thanks. - luoc This message may contain information that is confidential or privileged. If you are not the intended recipient, please advise the sender immediately and delete this message. See http://www.blackrock.com/corporate/compliance/email-disclaimers for further information. Please refer to http://www.blackrock.com/corporate/compliance/privacy-policy for more information about BlackRock’s Privacy Policy. For a list of BlackRock's office addresses worldwide, see http://www.blackrock.com/corporate/about-us/contacts-locations. © 2022 BlackRock, Inc. All rights reserved.