Blend JSON & XML in same table with SQL search

BlobCity DB is an open source database we created with the aim to supposedly store any and every format of data. We seem to have come a long way from where we started. This article focuses on how BlobCity DB can blend both JSON & XML data to allow collective querying using SQL.

Inserting JSON into a table

{"name": "John", "age": 45}

Let’s say we insert this JSON into a table called Persons. The DB would automatically created 2 columns, one called name and the other called age . The datatype of the columns is automatically inferred.

If you were to do a select * from Persons the schema and contents are properly arranged in the respective columns. Although you inserted JSON, the data and schema is well aligned for SQL querying.

Ignore the _id column, which is the internally generated record id that is automatically assigned to each record by the DB.

More info on how BlobCity DB stores nested JSON’s and JSON Arrays

Now inserting XML into the same table

<xml>
    <name>Tom</name>
    <age>26</age>
</xml>

Now what would happen if you just happened to insert this XML into the same Persons table? The beauty is that BlobCity DB would take it without complaints, and would also automatically logically blend-in your data.

select * from Persons on the table now contains 2 records.

The XML is seamlessly blent into the same SQL type table structure as the JSON. The column names are automatically inferred from the XML as well. It is needless to say that the effect would be the same if the XML record was inserted first followed by the JSON record.

More info on complete XML storage capabilities of BlobCity DB

Now that you have the data in, select * from Persons where age > 40 would give you back the record for John. Well it’s not just simple SQL, but you can fire any SQL query on this table including JOIN query with other tables. The data is fully structured and automatically blended and prepared to allow SQL querying.

Is this something you need?

Do try this out: https://github.com/blobcity/db. I would love to hear your thoughts.

We built this capability because we strongly believe a lot of software projects would find value for saving multiple formats of data in a single database, with the ability to query all of this data using SQL. We would love to hear your needs for such a database? We are working on adding support for many more formats of data that can be seamlessly blended into a single table.