0

I have a Json string like below I am trying to insert it as row values into the database. so example here there would be two rows inserted into the database with 4 columns The columns being status, filename, mainpic, fullurl I understand the basic idea of what I am looking to do but I need to know how to parse this down By the way my JSON will always be formated this way so no need to worry about how it is formatted 

{"status":"success",
 "filename":"c3336eab-133d-4e78-ba9a-135152e0cddf.jpg",
 "mainpic":"true","fullurl":"../userimages/c3336eab-133d-4e78-ba9a-135152e0cddf.jpg"},
{"status":"success",
 "filename":"6dffc7f0-17e2-40c5-ab6d-c380697ae4d8.jpg",
 "mainpic":"true","fullurl":"../userimages/6dffc7f0-17e2-40c5-ab6d-c380697ae4d8.jpg"}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
scripter78
  • 1,117
  • 3
  • 22
  • 50
  • TSQL is probably not the right tool for the job. What is your development framework? You'll want to do this in code. – Bennor McCarthy Feb 26 '12 at 20:41
  • possible duplicate of [Parse JSON in TSQL](http://stackoverflow.com/questions/2867501/parse-json-in-tsql) – David Hall Feb 26 '12 at 20:42
  • Well here is the thing, If I do this in my code (C#) it would cause for multiple inserts instead of just one. I have since decided I would just store the entire JSon object as a string in field in the database and just go the route where anytime I need to make a change I will pull the entire JSON string make the changes that are needed and then update the database with the new string – scripter78 Feb 27 '12 at 00:01

2 Answers2

0

Using a SQLCLR implementation is an option.

http://jqsql.com/ source available https://github.com/ercananlama/JQSQL

Modify your input slightly and declare it as a json array, pass an empty string as second argument to select root.

EXEC jqsql.totable '[
{"status":"success",
 "filename":"c3336eab-133d-4e78-ba9a-135152e0cddf.jpg",
 "mainpic":"true",
 "fullurl":"../userimages/c3336eab-133d-4e78-ba9a-135152e0cddf.jpg"},
{"status":"success",
 "filename":"6dffc7f0-17e2-40c5-ab6d-c380697ae4d8.jpg",
 "mainpic":"true",
 "fullurl":"../userimages/6dffc7f0-17e2-40c5-ab6d-c380697ae4d8.jpg"}
 ]', ''

returns a result set like this

status  filename                                 mainpic fullurl
------- ---------------------------------------- ------- ------------------------------------------------------
success c3336eab-133d-4e78-ba9a-135152e0cddf.jpg 1       ../userimages/c3336eab-133d-4e78-ba9a-135152e0cddf.jpg
success 6dffc7f0-17e2-40c5-ab6d-c380697ae4d8.jpg 1       ../userimages/6dffc7f0-17e2-40c5-ab6d-c380697ae4d8.jpg
JJS
  • 6,431
  • 1
  • 54
  • 70
0

As a starting point you could take a look at this comprehensive T-SQL JSON parsing function...

http://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/

But as your JSON is quite simple, it might be worth making a cut down version for your needs.

barrylloyd
  • 1,599
  • 1
  • 11
  • 18
  • Actually that was the first thing I did find and you're right that is a bit excessive for what i was needing. plus it wasn't giving the name value pairs as column name and column value. like I was needing in this case – scripter78 Feb 27 '12 at 14:16