SQL将JSON转成列

 

好久不写东西，这个也没什么技术含量，放上来玩玩，也许有人用的着。

 

/**
* create procedure for get all fields from json 
* 
* Mark
* 
* 2014-7-17 17:16:01
* 
* */



USE QEO_Insurance
GO

IF OBJECT_ID('sp_getJSONFields') IS NOT NULL
DROP PROCEDURE sp_getJSONFields
GO

CREATE PROCEDURE sp_getJSONFields
@Json VARCHAR(MAX)
AS
BEGIN
SELECT @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)>0
BEGIN
    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 '------------------'    
END
SET @fieldSql=LEFT(@fieldSql,LEN(@fieldSql)-1)

--EXEC sp_executesql @fieldSql
EXEC (@fieldSql)
END
GO

EXEC sp_getJSONFields @json= '{"VIN_Invalid":"1","VIN_ID":"427658","Veh_TypeCode":"CTRK","Year":"2011","Make":"TOYOTA","Veh_Model":"TUNDRA","Body_CD":"PK"}'

 

输入：

{"VIN_Invalid":"1","VIN_ID":"427658","Veh_TypeCode":"CTRK","Year":"2011","Make":"TOYOTA","Veh_Model":"TUNDRA","Body_CD":"PK"}

输出：

 

 

2014-07-1719:38:41