We are approaching the most interesting part of these article series. I have mentioned that developers give preferences to JSON as it has clear structure and smaller data size comparing to XML.
In this article, we are going to compare JSON vs XML performance.
In this article, we will explore how we can store and index JSON objects in a database.
As we have already discussed, developers did not add a separate type for JSON to SQL Server 2016 as they did for XML.
Parsing data from XML using XQuery is a routine practice. In order to do this most effectively, little effort is required.
Suppose we need to parse data from the disk file with the following structure:
|
1 2 3 4 5 6 7 8 9 10 11 |
<tables> <table name="Accounting" schema="Production" object="Accounting"> <column name="Date" order="3" visible="1" /> <column name="DateFrom" order="5" visible="1" /> <column name="DateTo" order="6" visible="1" /> <column name="Description" order="4" visible="1" /> <column name="DocumentUID" order="1" visible="0" /> <column name="Number" order="2" visible="1" /> <column name="Warehouse" order="7" visible="1" /> </table> </tables> |
Use BULK INSERT, if you need to read data from a file:
|
1 2 |
SELECT BulkColumn FROM OPENROWSET(BULK 'D:\data.xml', SINGLE_BLOB) x sample xml file |