/*** create procedure for get all fields from json * * Mark* * 2014-7-17 17:16:01* * */USE QEO_InsuranceGOIF OBJECT_ID(‘sp_getJSONFields‘) IS NOT NULLDROP PROCEDURE sp_getJSONFieldsGOCREATE PROCEDURE sp_getJSONFields@Json VARCHAR(MAX)ASBEGINSELECT @Json=REPLACE(@Json,‘{‘,‘‘)SELECT @Json=REPLACE(@Json,‘}‘,‘,‘)DECLARE @temp VARCHAR(100)DECLARE @objName VARCHAR(30)DECLARE @objValue VARCHAR(30)DECLARE @fieldSql VARCHAR(MAX)SET @fieldSql=‘select ‘WHILE LEN(@Json)>0BEGIN    SELECT @temp=SUBSTRING(@Json,0,CHARINDEX(‘,‘,@Json,0))    --PRINT @temp     SELECT @Json=RIGHT(@Json,LEN(@Json)-LEN(@temp)-1)    --PRINT @Json        set @objName =left(@temp,CHARINDEX(‘:‘,@temp,0)-1)    set @objValue =right(@temp,len(@temp)-CHARINDEX(‘:‘,@temp,0))        --PRINT  @objName+‘=‘+ @objValue+‘;‘    set @fieldSql=@fieldSql+REPLACE(@objValue,‘"‘,‘‘‘‘)+‘ as ‘+REPLACE(@objName,‘"‘,‘‘)+‘,‘        --PRINT ‘------------------‘    ENDSET @fieldSql=LEFT(@fieldSql,LEN(@fieldSql)-1)--EXEC sp_executesql @fieldSqlEXEC (@fieldSql)ENDGOEXEC sp_getJSONFields @json= ‘{"VIN_Invalid":"1","VIN_ID":"427658","Veh_TypeCode":"CTRK","Year":"2011","Make":"TOYOTA","Veh_Model":"TUNDRA","Body_CD":"PK"}‘