SQL Server 2012:分层 XML 数据 - 转义字符 [英] SQL Server 2012: Hierarchical XML data - escape characters

问题描述

我正在尝试从以下分层数据创建 XML 格式输出.在输出中，我看到转义字符(<,",> 替换为转义字符).

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).

下面的测试数据有 create/insert/function 和 select 语句.

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

运行 select 语句将在输出中重现该问题.

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. 您尝试使用 XML 类型的 SelectChild 的输出作为属性 @ListDirectChildren 的内容.你不能在这个地方使用 XML，因此它被作为普通文本处理(和转义).您是否正在尝试创建某种递归子列表?

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?

在您的外部查询中，您将 XML 转换为 VARCHAR(MAX)(顺便说一句:始终使用 NVARCHAR 与 XML 结合).同样，您将强制引擎将此文本视为文本并因此对其进行转义.

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.

您尝试添加字符串null"来表示缺少值.但 XML 的工作方式不同:一种.XML 中完全缺少该元素:查询它会返回 NULL，没关系.

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.对于某些规则，元素必须在那里，但应该为空:
或 (其中意思完全一样).查询节点的text()，得到NULL，也可以.

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.对于某些规则，您希望将元素标记为 NULL.使用 XSINIL

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

试试这个 empty 和 null 的变体:

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

结果:

                   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>

我的建议:

这个问题

有人可以建议我如何处理转义字符吗?

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

有望解决.请开始一个新问题，在其中向示例场景添加更多数据以反映多个子项，放置指向此问题的链接并说明预期输出(XML 应该是什么样子).

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).

这篇关于SQL Server 2012:分层 XML 数据 - 转义字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！