作法請參考此篇
如下為原本的問題內容
//-----------------------------------------------------------
以下先定義一個變數紀錄Json字串
set @json = N'{
"Book":{
"IssueDate":"02-15-2019"
, "Detail":{
"Type":"Any Type"
, "Author":{
"Name":"Annie"
, "Sex":"Female"
}
}
, "Chapter":[
{
"Section":"1.1"
, "Title":"Hello world."
}
,
{
"Section":"1.2"
, "Title":"Be happy."
}
]
, "Sponsor":["A","B","C"]
}}'
然後,希望產生的結果表可以類似如下:
topKey Key Value
Book IssueDate 02-15-2019
Book Detail { "Type":"Any Type", "Author":{ "Name":"Annie" , "Sex":"Female"}
Book Chapter [{ "Section":"1.1", "Title":"Hello world." }, { "Section":"1.2", "Title":"Be happy." }]
Book Sponsor ["A","B","C"]
Detail Type Any Type
Detail Author { "Name":"Annie" ,"Sex":"Female"}
Author Name Annie
Author Sex Female
Chapter Section 1.1
Chapter Title Hello world
Chapter Section 1.2
Chapter Title Be happy.
我發現當欄位“Value”是Json字串時,我需要繼續解析它,直到他已經為Value。
所以我先創建了一個function,將@Json傳入這個function,並當符合條件時持續呼叫自己,組成一個Table,return到呼叫端。
create function ParseJson(@json nvarchar(max))
returns @tempTable table ([key] nvarchar(max),[value] nvarchar(max))
as
begin
insert @tempTable
select
x.[key]
,x.[value]
from
openjson(@json) x
cross apply ParseJson(x.[value]) y
where ISJSON(x.[value])=1
end
go
但我目前呼叫他,會return空值,但目前想不出該如何做調整較好...
select * from ParseJson(@json)
希望尋求邦友的協助,先謝謝各位了。