SQL Server 2012: Hierarchical XML data - escape characters

I'm trying to create XML format output from the below hierarchical data. In the output I'm seeing escape characters (<,",> replaced with escape characters).

Below test data has create/insert/function and select statement.

Running the select statement will reproduce the issue in the output.

Can someone please suggest me how to address the 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]

1 answer

  • answered 2017-01-11 14:17 Shnugo

    Your problems:

    1. You try to use the output of SelectChild, which is of type XML, as the content of the attribute @ListDirectChildren. You cannot use XML in this place, therefore it is handled (and escaped) as normal text. Are you attempting to create kind of a recursive child list?

    2. In your outer query you cast the XML to VARCHAR(MAX) (btw: use always NVARCHAR in connction with XML). Again you'll force the engine to treat this text as text and therefore escape it.

    3. You try to add the string "null" to express the missing of a value. But XML works differently: a. The element is missing in the XML completely: Querying it will return NULL, that's fine.

      b. For some rules the Element must be there, but should be empty:
      <ListDirectChildren></ListDirectChildren> or <ListDirectChildren /> (which is meaning exactly the same). Query the node's text() and you get NULL, that's fine too.

      c. For some rules you want to mark the element as NULL. Use XSINIL

    Try this for variations of empty and 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
    

    The result:

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

    Try this for XSINIL

    SELECT NULL AS test FOR XML RAW, ELEMENTS XSINIL
    

    to get this

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

    My suggestion:

    This question

    Can someone please suggest me how to address the escape characters?

    is hopefully solved. Please start a new question where you add some more data to your sample scenario to reflect multiple children, place a link to this question and state the expected output (how the XML should look like).