Hi how can I insert a JSON file into a Cell in Database? I don't want to store the File path but want to store the whole Content of the JSON file into the field.
What can I do?
-
search if there are similar questions before you post. See stackoverflow.com/questions/2497678/…naresh– naresh2013-08-15 03:28:44 +00:00Commented Aug 15, 2013 at 3:28
Add a comment
|
3 Answers
JSON data stored/transferred in as a string. You can store it in a normal NVARCHAR column.
2 Comments
ethanjyx
But there seems to be a length limit for the NVARCHAR? It's 4000 as said here technet.microsoft.com/en-us/library/ms186939.aspx. What if my JSON text file has more than 4000 characters?
Comfortably Numb
@ethanjyx the max size for NVARCHAR(MAX) is 2 GB
how large is the json text?, depending on that you should have a varchar field if content is not large or CLOB if is a lot of json text, json is just text , so you just have something to read the content of the file, maybe some transact-sql script , and insert it in your table
Comments
As mentioned, you can store the json file in a NVARCHAR(MAX) column. Here is an example how to load a json file into a NVARCHAR(MAX) variable.
Consider this sample .json file top-posts.json:
[
{
"Id":6107,
"Score":176,
"ViewCount":155988,
"Title":"What are deconvolutional layers?",
"OwnerUserId":8820
},
{
"Id":155,
"Score":164,
"ViewCount":25822,
"Title":"Publicly Available Datasets",
"OwnerUserId":227
}
]
Here is a SQL script to import the json file using OPENROWSET and Bulk import. We also pass in the path to the folder where the json file is. It is put in the same folder as the .sql file script.
Sql-script import_json_file_openrowset.sql:
DECLARE @JSONFILE VARCHAR(MAX);
SELECT @JSONFILE = BulkColumn
FROM OPENROWSET (BULK '$(FullScriptDir)\top-posts.json', SINGLE_CLOB) AS j;
PRINT 'JsonFile contents: ' + @JSONFILE
IF (ISJSON(@JSONFILE)=1) PRINT 'It is valid Json';
The .bat file here passes the current folder as a variable to the sql script
runsqlscript.bat
@set FullScriptDir=%CD%
sqlcmd -S .\SQLEXPRESS -i import_json_file_openrowset.sql
This outputs:
sqlcmd -S .\SQLEXPRESS -i import_json_file_openrowset.sql
JsonFile contents: [
{
"Id":6107,
"Score":176,
"ViewCount":155988,
"Title":"Toreboss: What are deconvolutional layers?",
"OwnerUserId":8820
},
{
"Id":155,
"Score":164,
"ViewCount":25822,
"Title":"Publicly Available Datasets",
"OwnerUserId":227
}
]
It is valid Json
With the variable JSONFILE you can do whatever with it such as inserting it to a column in a new row of a table for example.
