You must Sign In to post a response.
  • Category: SQL Server

    How to parse json data in sql server 2012?

    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
  • #768544
    You can use this code script to query table from JSON string
    Select 
    max(case when name='Text' then convert(Varchar(50),StringValue) else '' end) as [Text],
    max(case when name='Value' then convert(Varchar(50),StringValue) else '' end) as [Value],
    max(case when name='Default' then convert(bit,StringValue) else 0 end) as [Default]
    From parseJSON
    (
    '[{"Text":"YES","Value":"YES","Default":true},
    {"Text":"NO","Value":"NO","Default":false}]'
    )
    where ValueType = 'string' OR ValueType = 'boolean'
    group by parent_ID

    reference : http://mtkcode.blogspot.in/2014/08/parse-json-string-by-sql-script.html


Sign In to post your comments