November 24, 2016 at 5:41 am
Hi,
How to convert table/tables data to (Embedded/Nested) JSON format in SQL Server 2014? Task is to import data into mongodb which requires data in JSON format.
Thanks in advance.
Regards,
Vijay
November 24, 2016 at 10:41 am
Please give us some sample data and also show how you would like that data to be formatted.
The sample data should be in the form of INSERTs to a temporary table, to enable easy consumption for others.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 24, 2016 at 12:24 pm
vijay.singh 46672 (11/24/2016)
Hi,How to convert table/tables data to (Embedded/Nested) JSON format in SQL Server 2014? Task is to import data into mongodb which requires data in JSON format.
Thanks in advance.
Regards,
Vijay
SQL 2014 does not natively support JSON.
To achieve this, one of my colleagues wrote a SQL CLR proc that takes an XML blob and shreds it into JSON using C#. It then returns the JSON as a VARCHAR(MAX).
Below is some sample data, with XML and JSON representation of the same data
DATA
DECLARE @Parent TABLE (
ParentID INT NOT NULL
, ParentName VARCHAR(50) NOT NULL
);
DECLARE @child TABLE (
ParentID INT NOT NULL
, ChildID INT NOT NULL
, ChildName VARCHAR(50) NOT NULL
);
INSERT INTO @Parent (ParentID, ParentName)
VALUES (1, 'Joe')
, (2, 'Bob');
INSERT INTO @child (ParentID, ChildID, ChildName)
VALUES (1, 1, 'Jack')
, (1, 2, 'Jill')
, (2, 3, 'Bruce')
, (2, 4, 'Betty');
XML code
SELECT
p.ParentID [@ParentID]
, p.ParentName [@ParentName]
, (SELECT c.ChildID [@ChildID]
, c.ChildName [@ChildName]
FROM @child AS c
WHERE c.ParentID = p.ParentID
FOR XML PATH('Child'), TYPE
) AS 'Children'
FROM @Parent AS p
GROUP BY p.ParentID, p.ParentName
FOR XML PATH('Parent'), ROOT('Parents');
XML results
<Parents>
<Parent ParentID="1" ParentName="Joe">
<Children>
<Child ChildID="1" ChildName="Jack" />
<Child ChildID="2" ChildName="Jill" />
</Children>
</Parent>
<Parent ParentID="2" ParentName="Bob">
<Children>
<Child ChildID="3" ChildName="Bruce" />
<Child ChildID="4" ChildName="Betty" />
</Children>
</Parent>
</Parents>
I believe that this would be the expected JSON equivalent
[
{
"Parent": {"ParentID":1, "ParentName":"Joe"}
, "Child": [{"ChildID":1, "ChildName":"Jack"}
, {"ChildID":2, "ChildName":"Jill"}
]
}
, {
"Parent": {"ParentID":2, "ParentName":"Bob"}
, "Child": [{"ChildID":3, "ChildName":"Bruce"}
, {"ChildID":4, "ChildName":"Betty"}
]
}
]
I have managed to recreate the above JSON output using TSQL, but it is ugly and relies on lots of hard-coding and replace statements. If nobody can come up with an elegant solution, I'll considder posting mine here.
November 25, 2016 at 12:26 pm
DesNorton (11/24/2016)
I have managed to recreate the above JSON output using TSQL, but it is ugly and relies on lots of hard-coding and replace statements. If nobody can come up with an elegant solution, I'll considder posting mine here.
OK, so my code was absolutely horrible.
I started trawling the web, and came across https://www.codeproject.com/articles/831487/nested-xml-from-sql-to-json It uses 2 scalar functions, to convert the XML to JSON. However, it does not work out of the box. Further along in the comments, the original author posted a mod that does work. The mod is listed below.
CREATE FUNCTION dbo.qfn_JsonEscape ( @value NVARCHAR(MAX) )
RETURNS NVARCHAR(MAX)
AS
BEGIN
IF ( @value IS NULL )
RETURN 'null';
IF ( TRY_PARSE( @value AS FLOAT) IS NOT NULL )
RETURN @value;
SET @value = REPLACE(@value, '\', '\\');
SET @value = REPLACE(@value, '"', '\"');
RETURN '"'+@value+'"';
END;
GO
CREATE FUNCTION [dbo].[qfn_XmlToJson] ( @XmlData XML )
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @m NVARCHAR(MAX)
SELECT
@m = STUFF(
(SELECT
theline
FROM
( SELECT
',' + ' {'
+ STUFF(
(SELECT
',"' + COALESCE(b.c.value('local-name(.)', 'NVARCHAR(255)'),
'') + '":'
+ CASE WHEN b.c.value('count(*)', 'int') = 0
THEN dbo.[qfn_JsonEscape](b.c.value('text()[1]',
'NVARCHAR(MAX)'))
ELSE dbo.qfn_XmlToJson(b.c.query('*'))
END
FROM
x.a.nodes('*') b ( c )
FOR
XML PATH('')
, TYPE).value('(./text())[1]', 'NVARCHAR(MAX)'), 1, 1,
'') + '}'
FROM
@XmlData.nodes('/*') x ( a )
) JSON ( theLine )
FOR
XML PATH('')
, TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
DECLARE @n INT
SELECT
@n = b.c.value('count(*)', 'int')
FROM
@XmlData.nodes('/') b ( c )
IF ( @n = 0 )
RETURN 'null'
IF ( @n = 1 )
RETURN @m
RETURN '['+@m+']'
END
GO
SAMPLE DATA
DECLARE @Parent TABLE (
ParentID INT NOT NULL
, ParentName VARCHAR(50) NOT NULL
);
DECLARE @child TABLE (
ParentID INT NOT NULL
, ChildID INT NOT NULL
, ChildName VARCHAR(50) NOT NULL
);
DECLARE @Pet TABLE (
ChildID INT NOT NULL
, PetID INT NOT NULL
, PetDescription VARCHAR(50) NOT NULL
);
INSERT INTO @Parent (ParentID, ParentName)
VALUES (1, 'Joe')
, (2, 'Bob');
INSERT INTO @child (ParentID, ChildID, ChildName)
VALUES (1, 1, 'Jack')
, (1, 2, 'Jill')
, (2, 3, 'Bruce')
, (2, 4, 'Betty');
INSERT INTO @Pet (ChildID, PetID, PetDescription)
VALUES (2, 1, 'Puppy')
, (2, 2, 'Kitten')
, (3, 3, 'Pony');
CODE USAGE
Notes:
* The XML has multiple top-level nodes.
* Single Child is returned as a {Single Object}
* Multiple Children are returned a [Collection]
DECLARE @xmlData XML = (
SELECT
p.ParentID AS [ParentID]
, p.ParentName AS [ParentName]
, (SELECT c.ChildID AS [ChildID]
, c.ChildName AS [ChildName]
, (SELECT gc.PetID AS [PetID]
, gc.PetDescription AS [PetDescription]
FROM @Pet AS gc
WHERE gc.ChildID = c.ChildID
FOR XML PATH('Pet'), TYPE
) AS [Pets]
FROM @child AS c
WHERE c.ParentID = p.ParentID
FOR XML PATH('Child'), TYPE
) AS [Children]
FROM @Parent AS p
GROUP BY p.ParentID, p.ParentName
FOR XML PATH('Parent')
);
SELECT
xmlData = @xmlData
, jsonData = dbo.qfn_XmlToJson(@xmlData);
November 25, 2016 at 2:05 pm
With a few nested queries, you could also sue the following code to generate the JSON
SELECT '['
+ STUFF((
SELECT ', {"ParentID":' + CONVERT(VARCHAR(10), p.ParentID) + ', "ParentName":"' + p.ParentName + '"'
+ ( SELECT ', "Child":['
+ STUFF((
SELECT ', {"ChildID":' + CONVERT(VARCHAR(10), c.ChildID) + ', "ChildName":"' + c.ChildName + '"'
+ ( SELECT ', "Pet":['
+ STUFF((
SELECT ', {"PetID":' + CONVERT(VARCHAR(10), pt.PetID) + ', "PetDescription":"' + pt.PetDescription + '"}'
FROM @Pet AS pt
WHERE pt.ChildID = c.ChildID
ORDER BY pt.PetID
FOR XML PATH('')
), 1, 2, '')
+ ']' )
+ '}'
FROM @child AS c
WHERE c.ParentID = p.ParentID
ORDER BY c.ChildID
FOR XML PATH('')
), 1, 2, '')
+ ']' )
+ '}'
FROM @Parent AS p
ORDER BY p.ParentID
FOR XML PATH('')
), 1, 2, '')
+ ']';
The output can be verified online at http://jsonlint.com/
November 30, 2016 at 8:51 am
If you are comfortable scripting, use Powershell to read the json source and stuff it into some staging tables on a SQL database.
February 15, 2019 at 1:24 pm
To anyone who is stuck on old versions of SQL server (prior to 2016 anyway), I made the following changes to the functions, and they work well!
qfn_JsonEscape SET @value = REPLACE(@value, '\', '\\');
SET @value = REPLACE(@value, '"', '\"');
SET @value = REPLACE(@value, CHAR(10), '\n'); -- New Line Feed
SET @value = REPLACE(@value, CHAR(13), '\r'); -- Carriage Return
SET @value = REPLACE(@value, CHAR(09), '\t'); -- Tab
SET @value = REPLACE(@value, CHAR(12), '\r'); -- Form Feed
SET @value = REPLACE(@value, CHAR(08), '\b'); -- Backspace
qfn_XmlToJson
Look for all references to theLine and make sure they are capitalized the same.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply