Double Quotes are being ignored by SQL Server when passed to a string variable

Question

I wrote a Stored Procedure in SQL Server 2016 that generates XML File

When creating XML file I need to specify encoding explicitly. That is why I added that explicit encoding to the result of the XML content.

SELECT @SQLStr = 
'SELECT N''<?xml version="1.0" encoding="UTF-8"?>'' 
            + (SELECT CAST((SELECT [Id], [FirstName], [LastName], [Address] 
                            FROM InputTemp.dbo.XMLTest AS Body 
                            WHERE Id = '''  + str(@Id) + ''' 
                            FOR XML AUTO, ELEMENTS) AS NVARCHAR(MAX)
                          )
              )'

The issue is that double quotes " that are required for the encoding are being ignored when passed to the variable. That is why when I open that generated XML file in a browser it appears totally blank. But when I open in a text editor everything is displayed correctly except there is no double quotes around 1.0 and UTF-8 (eg. <?xml version=1.0 encoding=UTF-8?> ). Once I add the double quotes I then can see the XML file content in the browser properly.

SET QUOTED_IDENTIFIER ON

This option did not help.

But when I run this statement separately double quotes appear.

SELECT N'<?xml version="1.0" encoding="UTF-8"?>' 
         + (SELECT CAST((SELECT [Id], [FirstName], [LastName], [Address] 
                         FROM InputTemp.dbo.XMLTest AS Body 
                         WHERE Id = '''  + str(@Id) + ''' 
                         FOR XML AUTO, ELEMENTS) AS NVARCHAR(MAX)
                       )
           )

Answer 1

This answer is to substitute " to ''''. Though it will print only single quotes. For browser it's sufficient to open the XML file with " double quotes.

Note that a database compatibility level has to be 2016.

SELECT @SQLStr = 
'SELECT N''<?xml version=''''1.0'''' encoding=''''UTF-8''''?>'' + (SELECT CAST((SELECT [Id], [FirstName], [LastName], [Address] FROM InputTemp.dbo.XMLTest AS Body WHERE Id = '''  + str(@Id) + ''' FOR XML AUTO, ELEMENTS) AS NVARCHAR(MAX)))'