13.5 JSON and XML in Relational Databases

It is sometimes desirable to store XML or JSON data in a relational or object-relational (OR) database in a manner that allows retrieval and update of the data. The basic issue is how to map the hierarchical structure of XML or JSON documents into a relational or OR model. A simple but not very flexible solution is to let the entire document be represented as a single attribute. A more satisfactory solution is to shred the document into parts that are represented as attributes. However, this requires an intelligent mapping algorithm. We might also wish to be able to transform relational data into XML or JSON form and to publish it in that form. Ultimately, what is needed is the ability to do round-tripping between documents and databases, which means being able to accept data in XML or JSON form, transform it into a relational database, query and update the database in the usual way, access it using SQL, and transform the output back into XML or JSON format, without loss of content.

Many vendors of relational database management systems have extended their native data types to allow storage of XML and JSON content. JSON data can be stored in a column of JSON type, and special functions can be used to extract and manipulate JSON components stored in the field such as objects and arrays. At present, relational database management systems (RDBMSs) generally do not support creation of object tables of JSON type or automatic shredding of JSON documents.

XML documents can also be stored in a column of XML type in a relational database. Another method of handling XML documents is to create an object table in which each document is a single tuple of XML type. For example, if we have a schema such as the one shown in Figure 13.7, we can create a table in which each CUSTOMER element is a tuple, such as

This solution is limited because the documents are stored as large object (LOB) type and there are limitations on LOB operators. If we wish to access individual elements of the documents, we must resort to XPath or XQuery operators. An alternative is to use an XSD or DTD and to create a table in which the XML elements are mapped to objects in an OR database.

We have to specify where the schema is stored (usually as a URL) and create an association between the stored schema and the object table. The instance document that contains the data that corresponds to the schema is read in as a unit. A parser can then be used to convert the elements of the XML document to a relational or object-relational structure and to store the data in that form. The columns of the table correspond to the elements of CUSTOMER in the XML document. Once the data is stored in a table, it can be treated as a relational database using SQL. The DBMS can also generate XML as output from a relational table. This technique can be used to facilitate data exchange between existing heterogeneous databases, which can be queried using standard languages such as SQL, with the query results being placed into XML instance documents to be used as input to another database.