SQL Server 2012: XML hierarchical data - escape characters

Question

I am trying to create XML format output from the following hierarchical data. In the output, I see escape characters (<, ",> replaced by escape characters).

Below the test data is the create / insert / function and select function.

Executing the select statement will reproduce the output problem.

Can someone please suggest me how to handle escape characters?

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

WITH PrepareTable (XMLString)AS(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 RAW,TYPE,ELEMENTS)SELECT [XMLString]FROM[PrepareTable]

Answer 1

Your problems:

• SelectChild, XML, @ListDirectChildren. XML , ( ) . ?

• XML VARCHAR(MAX) (btw: use always NVARCHAR XML). , , .

• "null", . XML -: . XML : NULL, .

  . , :
  <ListDirectChildren></ListDirectChildren> <ListDirectChildren /> ( ). node text(), NULL, .

  . NULL. XSINIL

empty NULL:

DECLARE @x XML=
'<root>
   <testempty1 />
   <testempty2></testempty2>
 </root>';

SELECT @x.value('(/root/testempty1)[1]','nvarchar(max)') AS testempty1_Content
      ,@x.value('(/root/testempty1/text())[1]','nvarchar(max)') AS testempty1_Text
      ,@x.value('(/root/testempty2)[1]','nvarchar(max)') AS testempty2_Content
      ,@x.value('(/root/testempty2/text())[1]','nvarchar(max)') AS testempty2_Text
      ,@x.value('(/root/NotExistingElement)[1]','nvarchar(max)') AS NotExistingElement_Content
      ,@x.value('(/root/NotExistingElement/text())[1]','nvarchar(max)') AS NotExistingElement_Text

:

                   Content  Text
testempty1         ""       NULL
testempty2         ""       NULL
NotExistingElement NULL     NULL

XSINIL

SELECT NULL AS test FOR XML RAW, ELEMENTS XSINIL

<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <test xsi:nil="true" />
</row>

:

-, , , escape-?

, . , , , , ( XML).