我有以下查询:
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="1" xsi:schemaLocation="http://collegeboard.org/CommonImport CommonImport.xsd" xmlns="http://collegeboard.org/CommonImport" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"">
我是通过SQL Server生成的。您可以提供有关如何正确创建XML标签的任何建议吗?如果我没有正确使用XMLNAMESPACE函数,请告诉我。感谢您的考虑。
您必须区分
在我看来,这StudentRecordCount
应该是<CommonImport>
节点中的一个属性,与相同schemaLocation
。第二个属性位于xmlns:xsi
-namespace中。
您没有说明预期的输出,但是我的魔幻水晶球告诉我,您可能需要这样做:
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以及提供示例数据和预期输出的信息。
大致上,这是您所需要的,但是名称空间是重复的。这是一个已知的烦人的问题。没错,结果是完全可以的,但是会肿。
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');
丑陋的解决方法
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);
另外,您可能会创建一个完全没有名称空间的东西,并在最后添加带有字符串方法的名称空间声明。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句