I'm trying to create JSON format output from the below hierarchical data, by first converting the data into XML and then to JSON. In the XML output I'm seeing special characters (<,",> replaced with < " >).
mi_check_pt_rout_key_n is considered as parent key.
Running the below code will help to reproduce the issue in getting escape sequences (< " >) in the XML output.
Can someone please suggest me
- how to address the escape sequences (< " >) in the XML output?
- how to format the output in JSON
CREATE TABLE meas_loc (enty_key bigint,mi_check_pt_rout_key_n bigint,mi_check_pt_pred_key_n bigint,MI_MEAS_LOC_SEQ_N FLOAT);
CREATE TABLE chkp_cond (enty_key bigint,mi_chkpcond_rout_key_n bigint,mi_chkpcond_pred_key_n bigint,MI_CHKPCOND_SEQ_NUM_N FLOAT);
INSERT INTO meas_loc (enty_key,mi_check_pt_rout_key_n,mi_check_pt_pred_key_n,MI_MEAS_LOC_SEQ_N) VALUES (64251803159,64251705940,64251705940,1);
INSERT INTO meas_loc (enty_key,mi_check_pt_rout_key_n,mi_check_pt_pred_key_n,MI_MEAS_LOC_SEQ_N) VALUES (64251802979,64251705940,64251705940,2);
INSERT INTO meas_loc (enty_key,mi_check_pt_rout_key_n,mi_check_pt_pred_key_n,MI_MEAS_LOC_SEQ_N) VALUES (64251802983,64251705940,64251705940,3);
INSERT INTO chkp_cond (enty_key,mi_chkpcond_rout_key_n,mi_chkpcond_pred_key_n,MI_CHKPCOND_SEQ_NUM_N) VALUES (64252166584,64251705940,64251802983,1);
INSERT INTO meas_loc (enty_key,mi_check_pt_rout_key_n,mi_check_pt_pred_key_n,MI_MEAS_LOC_SEQ_N) VALUES (64252166585,64251705940,64252166584,1);
create table lubr_chkp (enty_key bigint, rounte_key bigint, parent_key bigint, enty_seq float, chkp_cond nvarchar(6))
;with t as ( select enty_key,mi_check_pt_rout_key_n rounte_key,mi_check_pt_pred_key_n parent_key,MI_MEAS_LOC_SEQ_N enty_seq, 'true' chkp_cond
from meas_loc
union all
select enty_key,mi_chkpcond_rout_key_n rounte_key,mi_chkpcond_pred_key_n parent_key,MI_CHKPCOND_SEQ_NUM_N enty_seq, 'false' chkp_cond
from chkp_cond
)
insert into lubr_chkp (enty_key , rounte_key , parent_key , enty_seq, chkp_cond )
select enty_key , rounte_key , parent_key , enty_seq, chkp_cond from t
go
drop function SelectChild
go
CREATE function SelectChild(@key as bigint)
returns xml
begin
return (
select
CONVERT(varchar(100), CAST(enty_seq AS float)) as "@SeqNum",
enty_key as "@EntityKey",
chkp_cond as "@IsCheckpoint",
isnull(CONVERT(varchar(max), cast(dbo.SelectChild(enty_key) as xml)),'null') as "@ListDirectChildren"
from lubr_chkp
where parent_key = @key
order by enty_seq
for xml path('entity'), type
)
end
go
select cast(
(SELECT
CONVERT(varchar(100), CAST(enty_seq AS float)) as "@SeqNum"
,enty_key AS "@EntityKey"
,chkp_cond as "@IsCheckpoint"
,isnull(CONVERT(varchar(max), cast(dbo.SelectChild(enty_key) as xml)),'null') as "@ListDirectChildren"
FROM lubr_chkp
WHERE parent_key = 64251705940
order by enty_seq
FOR XML PATH ('entity'), type) as varchar(max))
I'm trying to get JSON format output as below:
[{"SeqNum":1,"EntityKey":64251803159,"IsCheckpoint":true,"ListDirectChildren":null},
{"SeqNum":2,"EntityKey":64251802979,"IsCheckpoint":true,"ListDirectChildren":null},
{"SeqNum":3,"EntityKey":64251802983,"IsCheckpoint":true,"ListDirectChildren":
[{"SeqNum":1,"EntityKey":64252166584,"IsCheckpoint":false,"ListDirectChildren":
[{"SeqNum":1,"EntityKey":64252166585,"IsCheckpoint":false,"ListDirectChildren":null}]}]}
]