SQL Server 2016如何为生成的XML添加XML声明头？

架构老林

2026-7-14

在SQL Server 2016中为FOR XML生成的XML添加声明头

我尝试在SQL Server 2016里，给FOR XML生成的XML内容顶部加上<?xml version="1.0" encoding="UTF-8"?>声明头。

原始SQL代码

SELECT
  [@university] = '159',
  (
    SELECT 
ISNULL(t1.applicationId,'') AS "applicationId",
ISNULL(t1.studentId,'') AS "studentId",
ISNULL(t1.globalId,'') AS "globalId",
ISNULL(t1.LastName,'') AS "LastName",
ISNULL(t1.FirstName,'') AS "FirstName",
ISNULL(t1.preferredFirstName,'') AS "preferredFirstName",
ISNULL(t1.middleName,'') AS "middleName",
ISNULL(t1.suffix,'') AS "suffix",
ISNULL(t1.maidenName,'') AS "maidenName",
ISNULL(t1.addressLine1,'') AS "addressLine1",
ISNULL(t1.addressLine2,'') AS "addressLine2",
ISNULL(t1.addressLine3,'') AS "addressLine3",
ISNULL(t1.city,'') AS "city",
ISNULL(t1.state,'') AS "state",
ISNULL(t1.postalCode,'') AS "postalCode",
ISNULL(t1.countryCode,'') AS "countryCode",
ISNULL(t1.homePhone,'') AS "homePhone",
ISNULL(t1.workPhone,'') AS "workPhone",
ISNULL(t1.cellPhone,'') AS "cellPhone",
ISNULL(t1.email1,'') AS "email1",
ISNULL(t1.email2,'') AS "email2",
ISNULL(t1.institutionEmail,'') AS "institutionEmail",
ISNULL(t1.primaryCitizenship,'') AS "primaryCitizenship",
ISNULL(t1.employer,'') AS "employer",
ISNULL(t1.partnershipCode,'') AS "partnershipCode",
ISNULL(t1.originalExpectedStartDate,'') AS "originalExpectedStartDate",
ISNULL(t1.currentExpectedStartDate,'') AS "currentExpectedStartDate",
ISNULL(t1.actualStartDate,'') AS "actualStartDate",
ISNULL(t1.firstEnrollmentDate,'') AS "firstEnrollmentDate",
ISNULL(t1.applicationStatusCode,'') AS "applicationStatusCode",
ISNULL(t1.applicationStatusDesc,'') AS "applicationStatusDesc",
ISNULL(t1.statusReasonCode,'') AS "statusReasonCode",
ISNULL(statusReasonDesc,'') AS "statusReasonDesc",
ISNULL(t1.appliedDate,'') AS "appliedDate",
ISNULL(t1.applicationFeeStatus,'') AS "applicationFeeStatus",
ISNULL(t1.applicationFeePaidDate,'') AS "applicationFeePaidDate",
ISNULL(t1.applicationPackEvalStatus,'') AS "applicationPackEvalStatus",
ISNULL(t1.applicationPackEvalDate,'') AS "applicationPackEvalDate",
ISNULL(t1.institutionAdmittedDate,'') AS "institutionAdmittedDate",
ISNULL(t1.termAdmitted,'') AS "termAdmitted",
ISNULL(t1.programAdmittedDate,'') AS "programAdmittedDate",
ISNULL(t1.programName,'') AS "programName",
ISNULL(t1.programCode,'') AS "programCode",
ISNULL(t1.programReviewStatusCode,'') AS "programReviewStatusCode",
ISNULL(t1.programReviewStatusDesc,'') AS "programReviewStatusDesc",
ISNULL(t1.programEligibilityStatusCode,'') AS "programEligibilityStatusCode",
ISNULL(t1.programEligibilityStatusDesc,'') AS "programEligibilityStatusDesc",
ISNULL(t1.termEnrolled,'') AS "termEnrolled",
ISNULL(t1.termActivated,'') AS "termActivated",
ISNULL(t1.transferCreditApplied,'') AS "transferCreditApplied",
ISNULL(t1.transferGPA,'') AS "transferGPA",
(
select
ISNULL(t2.holdTypeCode,'') AS "holdTypeCode",
ISNULL(t2.holdTypeDesc,'') AS "holdTypeDesc",
ISNULL(t2.holdDate,'') AS "holdDate",
ISNULL(t2.holdStatusCode,'') AS "holdStatusCode",
ISNULL(t2.holdStatusDesc,'') AS "holdStatusDesc"
from [SAS_IRDM].[dbo].[AP_GRADUATE] as t2
where t2.applicationId=t1.applicationId
FOR XML PATH('hold'),TYPE
)
AS enrollmentHolds
    FROM [AP_GRADUATE] as t1 
    FOR XML PATH('applicant'), TYPE
  )
FOR XML PATH('applicants');

修改后的错误代码

SELECT CONVERT(XML, '<?xml version="1.0" encoding="UTF-8"?>'),
  (
  SELECT
  [@university] = '159',
  (
    SELECT 
ISNULL(t1.applicationId,'') AS "applicationId",
ISNULL(t1.studentId,'') AS "studentId",
ISNULL(t1.globalId,'') AS "globalId",
ISNULL(t1.LastName,'') AS "LastName",
ISNULL(t1.FirstName,'') AS "FirstName",
ISNULL(t1.preferredFirstName,'') AS "preferredFirstName",
ISNULL(t1.middleName,'') AS "middleName",
ISNULL(t1.suffix,'') AS "suffix",
ISNULL(t1.maidenName,'') AS "maidenName",
ISNULL(t1.addressLine1,'') AS "addressLine1",
ISNULL(t1.addressLine2,'') AS "addressLine2",
ISNULL(t1.addressLine3,'') AS "addressLine3",
ISNULL(t1.city,'') AS "city",
ISNULL(t1.state,'') AS "state",
ISNULL(t1.postalCode,'') AS "postalCode",
ISNULL(t1.countryCode,'') AS "countryCode",
ISNULL(t1.homePhone,'') AS "homePhone",
ISNULL(t1.workPhone,'') AS "workPhone",
ISNULL(t1.cellPhone,'') AS "cellPhone",
ISNULL(t1.email1,'') AS "email1",
ISNULL(t1.email2,'') AS "email2",
ISNULL(t1.institutionEmail,'') AS "institutionEmail",
ISNULL(t1.primaryCitizenship,'') AS "primaryCitizenship",
ISNULL(t1.employer,'') AS "employer",
ISNULL(t1.partnershipCode,'') AS "partnershipCode",
ISNULL(t1.originalExpectedStartDate,'') AS "originalExpectedStartDate",
ISNULL(t1.currentExpectedStartDate,'') AS "currentExpectedStartDate",
ISNULL(t1.actualStartDate,'') AS "actualStartDate",
ISNULL(t1.firstEnrollmentDate,'') AS "firstEnrollmentDate",
ISNULL(t1.applicationStatusCode,'') AS "applicationStatusCode",
ISNULL(t1.applicationStatusDesc,'') AS "applicationStatusDesc",
ISNULL(t1.statusReasonCode,'') AS "statusReasonCode",
ISNULL(statusReasonDesc,'') AS "statusReasonDesc",
ISNULL(t1.appliedDate,'') AS "appliedDate",
ISNULL(t1.applicationFeeStatus,'') AS "applicationFeeStatus",
ISNULL(t1.applicationFeePaidDate,'') AS "applicationFeePaidDate",
ISNULL(t1.applicationPackEvalStatus,'') AS "applicationPackEvalStatus",
ISNULL(t1.applicationPackEvalDate,'') AS "applicationPackEvalDate",
ISNULL(t1.institutionAdmittedDate,'') AS "institutionAdmittedDate",
ISNULL(t1.termAdmitted,'') AS "termAdmitted",
ISNULL(t1.programAdmittedDate,'') AS "programAdmittedDate",
ISNULL(t1.programName,'') AS "programName",
ISNULL(t1.programCode,'') AS "programCode",
ISNULL(t1.programReviewStatusCode,'') AS "programReviewStatusCode",
ISNULL(t1.programReviewStatusDesc,'') AS "programReviewStatusDesc",
ISNULL(t1.programEligibilityStatusCode,'') AS "programEligibilityStatusCode",
ISNULL(t1.programEligibilityStatusDesc,'') AS "programEligibilityStatusDesc",
ISNULL(t1.termEnrolled,'') AS "termEnrolled",
ISNULL(t1.termActivated,'') AS "termActivated",
ISNULL(t1.transferCreditApplied,'') AS "transferCreditApplied",
ISNULL(t1.transferGPA,'') AS "transferGPA",
(
select
ISNULL(t2.holdTypeCode,'') AS "holdTypeCode",
ISNULL(t2.holdTypeDesc,'') AS "holdTypeDesc",
ISNULL(t2.holdDate,'') AS "holdDate",
ISNULL(t2.holdStatusCode,'') AS "holdStatusCode",
ISNULL(t2.holdStatusDesc,'') AS "holdStatusDesc"
from [SAS_IRDM].[dbo].[AP_GRADUATE] as t2
where t2.applicationId=t1.applicationId
FOR XML PATH('hold'),TYPE
)
AS enrollmentHolds
    FROM [AP_GRADUATE] as t1 
    FOR XML PATH('applicant'), TYPE
  )
FOR XML PATH('applicants')
)
FOR XML PATH('')

修改后出现转义问题，比如<applicants>变成了<applicants university="159"><applicant><applicationId>，无法得到正确的XML格式。

解决方案

要解决这个问题，核心是不要把XML声明当作XML节点拼接，而是将整个XML内容转换为字符串后再与声明头拼接。SQL Server的XML类型本身不存储声明头，只有输出为字符串时才会显示。

方法1：字符串拼接法（推荐）

将FOR XML生成的XML通过.value('.', 'NVARCHAR(MAX)')转换为字符串，再和声明头拼接：

SELECT '<?xml version="1.0" encoding="UTF-8"?>' + 
(
  SELECT
    [@university] = '159',
    (
      SELECT 
        ISNULL(t1.applicationId,'') AS "applicationId",
        ISNULL(t1.studentId,'') AS "studentId",
        ISNULL(t1.globalId,'') AS "globalId",
        ISNULL(t1.LastName,'') AS "LastName",
        ISNULL(t1.FirstName,'') AS "FirstName",
        ISNULL(t1.preferredFirstName,'') AS "preferredFirstName",
        ISNULL(t1.middleName,'') AS "middleName",
        ISNULL(t1.suffix,'') AS "suffix",
        ISNULL(t1.maidenName,'') AS "maidenName",
        ISNULL(t1.addressLine1,'') AS "addressLine1",
        ISNULL(t1.addressLine2,'') AS "addressLine2",
        ISNULL(t1.addressLine3,'') AS "addressLine3",
        ISNULL(t1.city,'') AS "city",
        ISNULL(t1.state,'') AS "state",
        ISNULL(t1.postalCode,'') AS "postalCode",
        ISNULL(t1.countryCode,'') AS "countryCode",
        ISNULL(t1.homePhone,'') AS "homePhone",
        ISNULL(t1.workPhone,'') AS "workPhone",
        ISNULL(t1.cellPhone,'') AS "cellPhone",
        ISNULL(t1.email1,'') AS "email1",
        ISNULL(t1.email2,'') AS "email2",
        ISNULL(t1.institutionEmail,'') AS "institutionEmail",
        ISNULL(t1.primaryCitizenship,'') AS "primaryCitizenship",
        ISNULL(t1.employer,'') AS "employer",
        ISNULL(t1.partnershipCode,'') AS "partnershipCode",
        ISNULL(t1.originalExpectedStartDate,'') AS "originalExpectedStartDate",
        ISNULL(t1.currentExpectedStartDate,'') AS "currentExpectedStartDate",
        ISNULL(t1.actualStartDate,'') AS "actualStartDate",
        ISNULL(t1.firstEnrollmentDate,'') AS "firstEnrollmentDate",
        ISNULL(t1.applicationStatusCode,'') AS "applicationStatusCode",
        ISNULL(t1.applicationStatusDesc,'') AS "applicationStatusDesc",
        ISNULL(t1.statusReasonCode,'') AS "statusReasonCode",
        ISNULL(statusReasonDesc,'') AS "statusReasonDesc",
        ISNULL(t1.appliedDate,'') AS "appliedDate",
        ISNULL(t1.applicationFeeStatus,'') AS "applicationFeeStatus",
        ISNULL(t1.applicationFeePaidDate,'') AS "applicationFeePaidDate",
        ISNULL(t1.applicationPackEvalStatus,'') AS "applicationPackEvalStatus",
        ISNULL(t1.applicationPackEvalDate,'') AS "applicationPackEvalDate",
        ISNULL(t1.institutionAdmittedDate,'') AS "institutionAdmittedDate",
        ISNULL(t1.termAdmitted,'') AS "termAdmitted",
        ISNULL(t1.programAdmittedDate,'') AS "programAdmittedDate",
        ISNULL(t1.programName,'') AS "programName",
        ISNULL(t1.programCode,'') AS "programCode",
        ISNULL(t1.programReviewStatusCode,'') AS "programReviewStatusCode",
        ISNULL(t1.programReviewStatusDesc,'') AS "programReviewStatusDesc",
        ISNULL(t1.programEligibilityStatusCode,'') AS "programEligibilityStatusCode",
        ISNULL(t1.programEligibilityStatusDesc,'') AS "programEligibilityStatusDesc",
        ISNULL(t1.termEnrolled,'') AS "termEnrolled",
        ISNULL(t1.termActivated,'') AS "termActivated",
        ISNULL(t1.transferCreditApplied,'') AS "transferCreditApplied",
        ISNULL(t1.transferGPA,'') AS "transferGPA",
        (
          select
            ISNULL(t2.holdTypeCode,'') AS "holdTypeCode",
            ISNULL(t2.holdTypeDesc,'') AS "holdTypeDesc",
            ISNULL(t2.holdDate,'') AS "holdDate",
            ISNULL(t2.holdStatusCode,'') AS "holdStatusCode",
            ISNULL(t2.holdStatusDesc,'') AS "holdStatusDesc"
          from [SAS_IRDM].[dbo].[AP_GRADUATE] as t2
          where t2.applicationId=t1.applicationId
          FOR XML PATH('hold'),TYPE
        ) AS enrollmentHolds
      FROM [AP_GRADUATE] as t1 
      FOR XML PATH('applicant'), TYPE
    )
  FOR XML PATH('applicants'), TYPE
).value('.', 'NVARCHAR(MAX)') AS ResultXML;

方法2：先存储XML再转换拼接

先将生成的XML存入变量，再转换为字符串拼接：

DECLARE @xmlContent XML;

SET @xmlContent = (
  SELECT
    [@university] = '159',
    (
      SELECT 
        ISNULL(t1.applicationId,'') AS "applicationId",
        -- 其余字段与原始代码一致，省略重复部分
        (
          select
            ISNULL(t2.holdTypeCode,'') AS "holdTypeCode",
            ISNULL(t2.holdTypeDesc,'') AS "holdTypeDesc",
            ISNULL(t2.holdDate,'') AS "holdDate",
            ISNULL(t2.holdStatusCode,'') AS "holdStatusCode",
            ISNULL(t2.holdStatusDesc,'') AS "holdStatusDesc"
          from [SAS_IRDM].[dbo].[AP_GRADUATE] as t2
          where t2.applicationId=t1.applicationId
          FOR XML PATH('hold'),TYPE
        ) AS enrollmentHolds
      FROM [AP_GRADUATE] as t1 
      FOR XML PATH('applicant'), TYPE
    )
  FOR XML PATH('applicants')
);

SELECT '<?xml version="1.0" encoding="UTF-8"?>' + CAST(@xmlContent AS NVARCHAR(MAX)) AS ResultXML;

关键说明

你的错误方案中，将XML声明当作XML节点拼接，SQL Server会自动转义XML内容，导致标签被转换为实体字符（如<变成<）。
SQL Server的XML数据类型不会存储XML声明头，只有当输出为字符串时，拼接的声明头才会保留并显示。

内容的提问来源于stack exchange，提问作者SASPYTHON

SQL Server 2016如何为生成的XML添加XML声明头？

原始SQL代码 #

修改后的错误代码 #

方法1：字符串拼接法（推荐） #

方法2：先存储XML再转换拼接 #

关键说明 #

原始SQL代码

修改后的错误代码

方法1：字符串拼接法（推荐）

方法2：先存储XML再转换拼接

关键说明