SQL Server parsing JSON

Outsourced projects, there is a lot of information stored in JSON, whether it is query or modify information is very cumbersome. Find some useful SQL function to parse, and attach the modified example.

Use procedure:

1. Need to create a new custom type table:hierarchy in SQL;

2. Return the custom function:parsejson of Table:hierarchy;

3. Create a new return value of nvarchar (MAX) as required: To_json or to_xml custom function.

The final results are as follows:

--update JSON and Return JSON
DECLARE @MyHierarchy Hierarchy, @xml xml
INSERT into @myHierarchy
SELECT * from Parsejson (' {
"Contact":
{
"Name": "Test",
"Network Name": "TEST",
"Age": 26,
"G": M,
"Phonenumbers":
{
"Mobile": "0000000000000000",
"Phone": "0000000000000000"
}

}
}
‘)
SELECT * FROM @myHierarchy
Update @myHierarchy set stringvalue= ' Bai ' where name= ' name '
SELECT dbo. ToXML (@MyHierarchy)
SELECT @XML =dbo. ToXML (@MyHierarchy)
SELECT @XML

SQL Server parsing JSON