Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

querry get data in double codes in string Expand / Collapse
Posted Monday, February 10, 2014 11:45 AM



Group: General Forum Members
Last Login: Today @ 11:13 AM
Points: 5,645, Visits: 8,171
mpradeep23 (2/10/2014)

i have table called t1

with column name offers with data as

"entitlementwrapper" : [ {
"Type" : "Factory Warranty",
"Date_Type" : "Ship date",
"Status" : "Active",
"Start_Date" : "2012-12-21",
"End_Date" : "2014-01-19",
"Days_Left" : "116",
"Term" : "13",
"Description" : "Wty: HP HW Replacement Support",
"IsTrusted" : "Y",
"Transaction_ID" : "4644780453"

i want to get data to store in other table

i need queery to load data into this table


So your table is storing the JSON in some form of character column. Got it.

You cannot DIRECTLY do what you want. You cannot write a SELECT statement against JSON data in SQL Server. You need to first use some mechanism to PARSE your JSON into some type of tabular format (I recommend SQL CLR for that personally). You could write TSQL to do the parsing but TSQL is not very good at that (if you pursue it, look up SUBSTRING and CHARINDEX). You can also likely (not certain) do some XML stuff to work the parsing too. I foresee numerous complexities (and thus time involvement) in creating rigorous parsing code here, so I consider this beyond the realm of a forum post for assistance. Perhaps someone else will take up the charge here.

Best of luck!


Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1539897
Posted Thursday, February 13, 2014 8:19 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, November 29, 2016 5:10 PM
Points: 540, Visits: 2,475
TheSQLGuru (2/9/2014)
Jeff Moden (2/9/2014)
TheSQLGuru (2/9/2014)
Please provide several sample sets of data and the corresponding table output you expect (with column data types). That's JSON, yes?

THAT's JSON? Shoot... that's just as bad or worse than attribute based XML. We need a few more "standards" for passing simple data.

I personally feel that JSON is MUCH worse than XML (and I frickin' HATE XML, so that should tell you something), but alas JSON has taken over and we are just going to have to learn to make the best of it.

People who like JSON: CS freshmen pretty printing address books in Java.

People who hate JSON: Everyone else.
Post #1541229
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse