由于引号，SQL to XML 无法创建正确的 XMLNAMESPACE(我认为)

Question

我有以下查询:

WITH XMLNAMESPACES ('CommonImport StudentRecordCount="1" 
xsi:schemaLocation="http://collegeboard.org/CommonImport CommonImport.xsd" 
xmlns="http://collegeboard.org/CommonImport" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"' AS CommonImport)
SELECT B.award_year_token AS [StudentID/AwardYearToken]
  ,A.student_ssn AS [StudentID/SSN]
  ,A.last_name AS [StudentName/LastName]
  ,A.first_name AS [StudentName/FirstName]
  ,A.alternate_id AS [StudentName/AlternateID]
  ,'2807' AS [CustomStrings/CustomString/FieldID]
  ,C.processed_status AS [CustomStrings/CustomString/Value]
  ,'2506' AS [CustomDates/CustomDate/FieldID]
  ,CAST (C.date_processed AS DATE) AS [CustomDates/CustomDate/Value]
FROM [dbo].[student] A INNER JOIN [stu_award_year] B ON A.[student_token] = B.[student_token]
LEFT OUTER JOIN [dbo].[isir_convert_data] C ON A.[student_ssn] = C.[ssn] AND B.award_year_token = C.award_year_token
--LEFT OUTER JOIN [user_string] E ON B.[stu_award_year_token] = E.[stu_award_year_token]
--WHERE B.AWARD_YEAR_TOKEN = 2018  --For 18-19 year.
WHERE B.AWARD_YEAR_TOKEN = 2017  --For 17-18 year.
  AND C.processed_status ='B'
  AND C.date_processed = (SELECT MAX (X.date_processed)
               FROM isir_convert_data X 
               WHERE C.ssn = X.ssn)
FOR XML PATH('Student'), ROOT('CommonImport')

由于引号处理不当，输出无法使用。它看起来像下面这样:

<CommonImport xmlns:CommonImport="CommonImport StudentRecordCount=&quot;1&quot; xsi:schemaLocation=&quot;http://collegeboard.org/CommonImport CommonImport.xsd&quot; xmlns=&quot;http://collegeboard.org/CommonImport&quot; xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema-instance&quot;">

我是通过 SQL Server 生成的。您能否就如何正确创建 XML 标签提供任何建议？如果我没有正确使用 XMLNAMESPACE 函数，请告诉我。谢谢你的考虑。

Answer 1

你必须区分

命名空间的声明和

命名空间的使用

在我看来， StudentRecordCount应该是 <CommonImport> 中的一个属性节点，同 schemaLocation .第二个属性位于 xmlns:xsi 内-命名空间。

你没有说明预期的输出，但我的魔法 Crystal 球告诉我，你可能需要这个:

WITH XMLNAMESPACES (DEFAULT 'http://collegeboard.org/CommonImport'
                  ,'http://www.w3.org/2001/XMLSchema-instance' AS xsi)
SELECT 1 AS [@StudentRecordCount]
      ,'http://collegeboard.org/CommonImport CommonImport.xsd' AS [@xsi:schemaLocation]
      ,'SomeOtherData' AS [Student/SomeElement]
FOR XML PATH('CommonImport');

结果

<CommonImport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
              xmlns="http://collegeboard.org/CommonImport" 
              StudentRecordCount="1" 
              xsi:schemaLocation="http://collegeboard.org/CommonImport CommonImport.xsd">
  <Student>
    <SomeElement>SomeOtherData</SomeElement>
  </Student>
</CommonImport>

如果这还不够，请阅读 how to create a MCVE并提供样本数据和预期输出。

更新 1

这大致是您需要的，但 namespace 是重复的。这是一个已知且烦人的问题。没有错，结果完全没问题，但臃肿。

WITH XMLNAMESPACES (DEFAULT 'http://collegeboard.org/CommonImport'
                  ,'http://www.w3.org/2001/XMLSchema-instance' AS xsi)
,cte AS
(
    SELECT object_id,name FROM sys.objects
)
SELECT COUNT(*) AS [@RecordCount]
      ,'http://collegeboard.org/CommonImport CommonImport.xsd' AS [@xsi:schemaLocation] 
      ,(
        SELECT *
        FROM cte
        FOR XML PATH('Object'),TYPE
       )
FROM cte
FOR XML PATH('CommonImport'); 

更新 2

一个丑陋的解决方法

WITH cte AS
(
    SELECT object_id,name FROM sys.objects
)
SELECT 
CAST(REPLACE(REPLACE(REPLACE(CAST(
(
    SELECT COUNT(*) AS [@RecordCount]
          ,'http://collegeboard.org/CommonImport CommonImport.xsd' AS [@xsi_schemaLocation] --<-- "xsi:" is replaced with "xsi_"
          ,'http://collegeboard.org/CommonImport' AS [@_xmlns_] --<-- "xmlns" is not allowed
          ,'http://www.w3.org/2001/XMLSchema-instance' AS [@_xmlns_xsi] --<-- Same with "xmlns:xsi"
          ,(
            SELECT *
            FROM cte
            FOR XML PATH('Object'),TYPE
           )
    FROM cte
    FOR XML PATH('CommonImport'),TYPE) AS nvarchar(MAX)),'xsi_','xsi:'),'_xmlns_',' xmlns'),'xmlnsxsi','xmlns:xsi') AS XML);

或者，您可以创建完全没有 namespace 的整个内容，并在末尾添加带有字符串方法的 namespace 声明。