create table jtable(JsonOutPut nvarchar(max));
go
insert into jtable(JsonOutPut)
values('[{"FID":1,"Fname":"cake","Fcount":5,"FDate":"2020-02-13","Fregion":"UK"},{"FID":2,"Fname":"pie","Fcount":15,"FDate":"2020-02-15","Fregion":"UK"}]'),
('[{"FID":100,"Fname":"cake100","Fcount":1005,"FDate":"2020-02-13","Fregion":"UK100"},{"FID":1002,"Fname":"pie100","Fcount":10015,"FDate":"2020-02-15","Fregion":"UK100"}]');
go
create view jview
as
select oj.FID, oj.Fname, oj.Fcount, oj.FDate, oj.Fregion
from jtable
cross apply openjson(JsonOutPut)
with
(
FID int '$.FID',
Fname varchar(100) '$.Fname',
Fcount int '$.Fcount',
FDate date '$.FDate',
Fregion varchar(20) '$.Fregion'
) as oj
go
select *
from jview
go
drop view if exists jview
drop table if exists jtable
go
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…