Advertisement
Guest User

Untitled

a guest
Apr 28th, 2018
648
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 0.71 KB | None | 0 0
  1. create table [#json] ([mydata] nvarchar(max));
  2. insert into [#json] values
  3.  ('["Fingers"]')
  4. ,('["Fingers","Right-"]')
  5. ,('["Arm","Fingers","Left-"]');
  6.  
  7.  
  8. ;with [data] as
  9. (
  10.     select [mydata] = replace(replace(replace([mydata], '[', SPACE(0)), ']', space(0)), '"', space(0)) from [#json]
  11. )
  12. ,[split] as
  13. (
  14.     select
  15.         [mydata] = [s].[str]
  16.     from
  17.         [data] as [d]
  18.     cross apply
  19.         (      
  20.             select
  21.                 [str] = [x].[c].[value]('(./text())[1]', 'nvarchar(4000)')
  22.             from
  23.                 (
  24.                     select [x] = convert(xml, '<i>' + replace([d].[mydata], ',', '</i><i>') + '</i>').[query]('.')
  25.                 ) as [a]
  26.             cross apply
  27.                 [x].[nodes]('i') as [x]([c])
  28.         ) as [s]
  29. )
  30. select
  31.     [mydata]
  32. from
  33.     [split];
  34.  
  35.  
  36. drop table [#json];
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement