Parse JSON in SQL Server 2016

json
sql

#1

You can now parse JSON in SQL Server 2016. This is useful for UiPath as the transaction information is stored in JSON within the QueueItems table and would be helpful when creating reports from the database.

https://blogs.technet.microsoft.com/dataplatforminsider/2016/01/05/json-in-sql-server-2016-part-1-of-4/


#2

Here is an example of extracting First Name from the SpecificData field using JSON_VALUE

JSON String

{"DynamicProperties":{"Type":"ADD","Employee ID":"","First Name":"Lonnie","Last Name":"Cruz","Email Address":"lonnie.cruz@genfour.net"}}

SELECT Id,
     JSON_VALUE(SpecificData, '$."Dynamic Properties"."First Name"') as Town
FROM test AS t 
WHERE ISJSON(SpecificData) > 0

Should be easy from here on in… :smiley:


#3

Hello! I have a problem with this request.Use this in Execute query activity. Example -
SELECT [Id]
,JSON_VALUE([Data],’."@Number"') AS [Number] ,JSON_VALUE([Data],'."@Name"’) AS [Name]
FROM [section].[subsection]
Error

I try to fix this, it gives an error on the following query element


#4

Have you tried running it in Management Studio - easier to get the query running there by breaking it down and then importing into UiPath. This is a SQL syntax error - I’ll have a look if I get time.

RD