Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- create table [#json] ([mydata] nvarchar(max));
- insert into [#json] values
- ('["Fingers"]')
- ,('["Fingers","Right-"]')
- ,('["Arm","Fingers","Left-"]');
- ;with [data] as
- (
- select [mydata] = replace(replace(replace([mydata], '[', SPACE(0)), ']', space(0)), '"', space(0)) from [#json]
- )
- ,[split] as
- (
- select
- [mydata] = [s].[str]
- from
- [data] as [d]
- cross apply
- (
- select
- [str] = [x].[c].[value]('(./text())[1]', 'nvarchar(4000)')
- from
- (
- select [x] = convert(xml, '<i>' + replace([d].[mydata], ',', '</i><i>') + '</i>').[query]('.')
- ) as [a]
- cross apply
- [x].[nodes]('i') as [x]([c])
- ) as [s]
- )
- select
- [mydata]
- from
- [split];
- drop table [#json];
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement