January 10, 2017 at 3:37 pm
When trying to run below code, I'm getting special characters(with '&') in the output. Can someone please suggest me how to handle this? Please use below test data and code to reproduce the issue.
CREATE TABLE lubr_req (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 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
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
January 11, 2017 at 6:25 am
I'm still stuck with the issue. Any help is greatly appreciated.
My goal is to create a JSON format from the output.
January 11, 2017 at 10:09 am
It's because you're converting your XML to VARCHAR(MAX), just leave it as XML and you shouldn't have these problems. The FOR XML directive treats text that looks like XML differently from actual XML.
DECLARE @chardoc CHAR(10) = '<path />', @xmldoc XML = '<path />'
SELECT @chardoc, @xmldoc
FOR XML PATH(''), TYPE
If you're doing this to include NULL values, look at using the ELEMENTS XSINIL directive.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 11, 2017 at 11:45 am
Please correct me if I'm wrong. Do I need to use RAW mode when using ELEMENTS XSINIL? In this case a new subelement is created. I might not need a subelement.
My goal is to create a JSON format output from the data. To achieve this, I'm trying to convert to XML and then to JSON. Please suggest if you there is a better approach.
Expected JSON format output I'm trying to get from my code
[{"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}]}]}
]
January 11, 2017 at 3:55 pm
vpolasa (1/11/2017)
Please correct me if I'm wrong. Do I need to use RAW mode when using ELEMENTS XSINIL? In this case a new subelement is created. I might not need a subelement.
This is something that is quite easily tested and you learn a lot more by trying things for yourself. I suggest you do so.
My goal is to create a JSON format output from the data. To achieve this, I'm trying to convert to XML and then to JSON. Please suggest if you there is a better approach.
I'm aware what your goal is, but you have another thread for that part of your goal. I have not contributed to that thread, because I have never worked with JSON, so I have no suggestions.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply