Posted: December 5, 2018 | Author: Jingyang Li | Filed under: Uncategorized |
--Use JSON function
Create table test(Id int ,col varchar(4000))
Insert into test values(1,'
')
;with mycte as (
select col,
concat('[', Stuff(replace(replace(replace(replace(col,':','":"'),',','","'),'>','"}'),'<button ',',{"') ,1,1,''),']') jsonInfo
from test)
,mycte2 as (
select * from mycte
CROSS APPLY OPENJSON (jsonInfo)
with (text varchar(50),value varchar(50))
)
select text as 'button.text',value as 'button.value'
from mycte2
For JSON PATH
drop table test
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9bd29ce3-6b20-44e7-8755-248abbd16665/sql-query-help?forum=transactsql
Jingyang Li 