December 11, 2016 at 11:21 pm
I am using sql server 2012.I have been assigned a task where one of my column(JsonText) of table(Sample) contain json data.I want to parse that data and insert into columns of another table(Test).I searched on net 'openjson'is supported in sql server 2016.How to do in sql server2012.
Table1 : Sample
Id JsonText Active
JsonText
webaddress?{'data':'{"PId": "XXXX","Status": "YES","Name":"XXX","Address":"XXXX","MobileNumber":"xxx"}'}
I am intrested only 'PID,Address,MobileNumber' columns not all.
Table Test structure like this
Id,PID,Address,MobileNumber
December 12, 2016 at 1:23 am
pinky_sam05 (12/11/2016)
I am using sql server 2012.I have been assigned a task where one of my column(JsonText) of table(Sample) contain json data.I want to parse that data and insert into columns of another table(Test).I searched on net 'openjson'is supported in sql server 2016.How to do in sql server2012.Table1 : Sample
Id JsonText Active
JsonText
webaddress?{'data':'{"PId": "XXXX","Status": "YES","Name":"XXX","Address":"XXXX","MobileNumber":"xxx"}'}
I am intrested only 'PID,Address,MobileNumber' columns not all.
Table Test structure like this
Id,PID,Address,MobileNumber
SELECT
PId = SUBSTRING(src.JsonText, P.sPos, P.ePos - P.sPos)
, [Address] = SUBSTRING(src.JsonText, A.sPos, A.ePos - A.sPos)
, MobileNumber = SUBSTRING(src.JsonText, M.sPos, M.ePos - M.sPos)
FROM #Sample AS src
CROSS APPLY (SELECT PATINDEX('%"PId"%', src.JsonText)+8, CHARINDEX('"', src.JsonText, PATINDEX('%"PId"%', src.JsonText)+8)) P(sPos, ePos)
CROSS APPLY (SELECT PATINDEX('%"Address"%', src.JsonText)+11, CHARINDEX('"', src.JsonText, PATINDEX('%"Address"%', src.JsonText)+11)) A(sPos, ePos)
CROSS APPLY (SELECT PATINDEX('%"MobileNumber"%', src.JsonText)+16, CHARINDEX('"', src.JsonText, PATINDEX('%"MobileNumber"%', src.JsonText)+16)) M(sPos, ePos)
December 12, 2016 at 1:54 am
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply