由于引号，SQL to XML无法创建正确的XMLNAMESPACE(我认为) [英] SQL to XML not able to create proper XMLNAMESPACE due to quotation marks (I think)

问题描述

我有以下查询:

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

由于引号处理不当，因此无法使用该输出.看起来如下:

The output is unusable due to the mishandling of the quotation marks. It looks like the following:

<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函数，请告诉我.谢谢您的考虑.

I am generating this via SQL Server. Can you offer any advice on how to properly create the XML Tag? And if I'm not properly using the XMLNAMESPACE function, please let me know. Thank you for considering.

推荐答案

您必须区分

• 名称空间的声明和
• 名称空间的使用

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

It seems to me, that StudentRecordCount should be an attribute in the <CommonImport> node, same with schemaLocation. The second attribute is living within the xmlns:xsi-namespace.

您没有说明预期的输出，但是我的魔幻水晶球告诉我，您可能需要这样做:

You did not state the expected output, but my magic crystal ball showed me, that you might need this:

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>

如果这还不能解决问题，请阅读如何创建MCVE 并提供示例数据和预期输出.

If this does not help enough, please read about how to create a MCVE and provide sample data and expected output.

-大致上-这是您所需要的，但是名称空间是重复的.这是一个已知的烦人的问题.没错，结果是完全可以的，但会肿.

This is - roughly - what you need, but the namespaces are repeated. This is a known and annoying issue. Not wrong, the result is perfectly okay, but bloated.

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

一个丑陋的解决方法

UPDATE 2

An ugly workaround

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

或者，您可能会创建一个完全没有名称空间的东西，并在末尾添加带有字符串方法的名称空间声明.

Alternatively you might create the whole thing without namespaces at all and add the namespace declaration with string methods at the end.

这篇关于由于引号，SQL to XML无法创建正确的XMLNAMESPACE(我认为)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！