代码之家 › 专栏 › 技术社区 › Chris Simmons

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

namespaces xml sql-server sql

Chris Simmons · 技术社区 · 6 年前

我有以下疑问:

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函数,请告诉我。感谢您的考虑。

1 回复 | 直到 6 年前

Gottfried Lesigang 6 年前

您必须区分

命名空间的声明和
命名空间的使用

在我看来 StudentRecordCount 应该是 <CommonImport> 节点,与相同 schemaLocation 。第二个属性是生活在 xmlns:xsi -命名空间。

您没有说明预期的输出,但我的魔法水晶球告诉我,您可能需要:

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

这大致就是您所需要的,但名称空间是重复的。这是一个众所周知的恼人问题。没有错,结果很好,但太夸张了。

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

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