SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


querry get data in double codes in string


querry get data in double codes in string

Author
Message
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32161 Visits: 8672
mpradeep23 (2/10/2014)
Hi

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
1.type
2.start_date
3.end_date

i need queery to load data into this table

thanks,
Pradeep



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!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
sqldriver
sqldriver
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2176 Visits: 2536
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. :-D


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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search