SQL to XML not able to create proper XMLNAMESPACE due to quotation marks (I think)

Question

I have the following query:

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

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.

Answer 1

You must distinguish between

• the declaration of a namespace and
• the usage of a namespace

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

the result

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

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

UPDATE 1

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

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.