如何在Server 2016中向XML添加编码XML声明prolog？

Question

这是我的疑问：

SELECT *
FROM [dbo].[ENTETE]
FOR XML PATH('ENTETE'), TYPE

其结果是：

<ENTETE>
     <CIB_DECLARANT>10057</CIB_DECLARANT>
     <Type_Fichier>DECLARATION_TCN</Type_Fichier>
     <DATE_DECLARATION>03122019</DATE_DECLARATION> 
</ENTETE>

我想添加这样的XML编码：

<?xml version="1.0" encoding="utf-8" standalone="no"?>
<TCN xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <ENTETE>
       <CIB_DECLARANT>10057</CIB_DECLARANT>
       <Type_Fichier>DECLARATION_TCN</Type_Fichier>
       <DATE_DECLARATION>03122019</DATE_DECLARATION> 
    </ENTETE>

Answer 1

Server内部以UTF-16的形式保存XML数据类型。正因为如此，它删除了任何XML。

这就是为什么如果需要XML声明prolog，则需要使用NVARCHAR()数据类型。

请看下面的内容。

SQL

-- DDL and sample data population, start
DECLARE @ENTETE TABLE (CIB_DECLARANT INT, Type_Fischier VARCHAR(30), date_declaration DATE);
INSERT INTO @ENTETE (CIB_DECLARANT, Type_Fischier,  date_declaration) 
VALUES (10057, 'DECLARATION_TCN', '2019-12-03');
-- DDL and sample data population, end

-- just to see
SELECT
(
    SELECT *
    FROM @ENTETE
    FOR XML PATH('ENTETE'), TYPE
).query('<TCN  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
{
    for $x in /ENTETE
    return $x
}

</TCN>');

DECLARE @prolog NVARCHAR(100) = N'<?xml version="1.0" encoding="utf-8" standalone="no"?>'
   , @xml NVARCHAR(MAX);

-- real deal
SET @xml = @prolog + TRY_CAST((SELECT
(
    SELECT *
    FROM @ENTETE
    FOR XML PATH('ENTETE'), TYPE
).query('<TCN  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
{
    for $x in /ENTETE
    return $x
}

</TCN>')) AS NVARCHAR(MAX));

-- final XML with the prolog as NVARCHAR() data type
SELECT @xml;

输出(只为人眼缩进)

<?xml version="1.0" encoding="utf-8" standalone="no"?>
<TCN xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <ENTETE>
      <CIB_DECLARANT>10057</CIB_DECLARANT>
      <Type_Fischier>DECLARATION_TCN</Type_Fischier>
      <date_declaration>2019-12-03</date_declaration>
   </ENTETE>
</TCN>