common_schema

json_to_xml

NAME

json_to_xml(): Convert valid JSON to equivalent XML

TYPE

Function

DESCRIPTION

json_to_xml() accepts text in JSON format, and converts it to its XML equivalent.

Both JSON and XML are commonly used to describe objects and properties; both allow for tree-like structure. Both are strict (to some level) in data definition.

json_to_xml() assumes a valid JSON input, and returns its XML equivalent, such that:

• Internal structural tests on JSON format apply. In any case of failure the function returns NULL
• Produced XML is consisted of elements and text. No attributes generated.
• Names are mapped to nodes. Simple values are mapped to text. Object values to subnodes. Arrays to multiple nodes.
• XML text is automatically encoded (e.g. the ">" character converted to ">"). XML node names are not encoded.
• Result XML is not beautified (no spaces or indentation between elements)

NOTE: this function is CPU intensive. This solution should ideally be implemented through built-in functions, not stored routines.

SYNOPSIS

json_to_xml(
    json_text TEXT CHARSET utf8
) RETURNS TEXT CHARSET utf8

Input:

• json_text: a valid JSON formatted text.

EXAMPLES

Convert JSON to XML:

mysql> SET @json := '
{
  "menu": {
    "id": "file",
    "value": "File",
    "popup": {
      "menuitem": [
        {"value": "New", "onclick": "CreateNewDoc()"},
        {"value": "Open", "onclick": "OpenDoc()"},
        {"value": "Close", "onclick": "CloseDoc()"}
      ]
    }
  }
}
';

mysql> SELECT json_to_xml(@json) AS xml \G
*************************** 1. row ***************************
xml: <menu><id>file</id><value>File</value><popup><menuitem><value>New</value><onclick>CreateNewDoc()</onclick></menuitem><menuitem><value>Open</value><onclick>OpenDoc()</onclick></menuitem><menuitem><value>Close</value><onclick>CloseDoc()</onclick></menuitem></popup></menu>

Beautified form of the above result:

<menu>
  <id>file</id>
  <value>File</value>
  <popup>
    <menuitem>
      <value>New</value>
      <onclick>CreateNewDoc()</onclick>
    </menuitem>
    <menuitem>
      <value>Open</value>
      <onclick>OpenDoc()</onclick>
    </menuitem>
    <menuitem>
      <value>Close</value>
      <onclick>CloseDoc()</onclick>
    </menuitem>
  </popup>
</menu>

ENVIRONMENT

MySQL 5.1 or newer

SEE ALSO

encode_xml(), extract_json_value()

AUTHOR

Shlomi Noach

common_schema documentation