Inserting content of SQL query in XML using FOR XML PATH

I wrote a stored procedure that returns nvarchar variable that contains some generated SQL query, and second procedure, that generates XML using FOR XML PATH. I want to modify my procedure that generates XML and add content of my generated query from first procedure into generated XML.

Part of my procedure that generates XML:

SELECT @SQLStr = 'SELECT';

    DECLARE @tmp varchar(100), @tmpkod varchar(max);
    DECLARE c CURSOR LOCAL READ_ONLY FOR
    SELECT tableName, tableCode FROM @TableNames 
    OPEN c
    FETCH NEXT FROM c INTO @tmp, @tmpkod;
    WHILE @@FETCH_STATUS = 0 
    BEGIN

    SELECT @i = @i - 1;
    SELECT @SQLStr = @SQLStr + '(SELECT TOP 10 * FROM ' + @tmp + ' FOR XML PATH(''row''), TYPE) AS ' + @tmp + ',
    '
    EXEC GenerujSelectazXML @tmp, @tmpcode output;

    SELECT @SQLStr = @SQLStr + '(SELECT ' + @tmpCode + ' FOR XML PATH (''row''), TYPE) AS ' + @tmp + '_TEST'
    SELECT @tmpcode

    IF (@i <> 0) SELECT @SQLStr = @SQLStr + ',
    '
    ELSE SELECT  @SQLStr = @SQLStr + '
    '
    FETCH NEXT FROM c INTO @tmp, @tmpkod;
    END
    CLOSE c; DEALLOCATE c;

    SELECT @SQLStr = @SQLStr + 'FOR XML PATH(''''), ROOT(''root'')';

    EXEC (@SQLStr)

I cannot simply put content of a query into XML, because it contains some special characters like " < ", " > ", and they are introducing/ending xml tags. So i thought that putting query command into XML comment will solve my problem.

I tried this:

    SELECT @SQLStr = '<!--' + @tmpCode + '-->';

and it didn't help, I got error:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '<'.
Msg 137, Level 15, State 1, Line 4
Must declare the scalar variable "@xml".
Msg 137, Level 15, State 2, Line 216
Must declare the scalar variable "@xml".
Msg 156, Level 15, State 1, Line 217
Incorrect syntax near t he keyword 'FOR'.
Msg 137, Level 15, State 1, Line 219
Must declare the scalar variable "@xml".
Msg 137, Level 15, State 2, Line 416
Must declare the scalar variable "@xml".
Msg 156, Level 15, State 1, Line 417
Incorrect syntax near the keyword 'FOR'.
Msg 137, Level 15, State 1, Line 419
Must declare the scalar variable "@xml".
Msg 137, Level 15, State 2, Line 540
Must declare the scalar variable "@xml".

I also tried this:

SELECT @SQLStr = '<![CDATA[' + @tmpCode + N']]>';

it didn't help, either.

Error message that I got:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '<'.
Msg 103, Level 15, State 4, Line 3
The identifier that starts with CDATA[DECLARE @xml xml SELECT TOP 1 @xml = x FROM iksemel ORDER BY id INSERT INTO ARTYKUL_TEST(ID_ARTYKULU,ID_MAGAZYNU' is too long. Maximum length is 128.
Msg 105, Level 15, State 1, Line 541
Unclosed quotation mark after the character string ')'.

Please help

Solution

Could you provide an example of the xml you're trying to generate? Based on the code you posted, I'm assuming it looks something like this.

<root>
  <Instructors>
    <row>
      <InstructorName>Graff</InstructorName>
    </row>
  </Instructors>
 <Instructors_TEST>
   <row>0</row>
 </Instructors_TEST>
</root>

You'll need to modify your generated SQL to be more like this:

SELECT (
  SELECT * FROM Instructors FOR XML PATH('row'), TYPE
) AS Instructors,
(
  SELECT 0 FROM Instructors FOR XML PATH('row'), TYPE
) AS Instructors_TEST
FOR XML PATH(''), ROOT('root')

The problem is not with the value (query) you're trying to store in the xml. Do a SELECT on the @tmpCode, paste it into the editor, and check whether its a valid string. It's not.

In your code, you'd get this:

SELECT @tmpCode = '''SELECT value(''ID_ARTYKULU[1]'')'''
--'SELECT value('ID_ARTYKULU[1]')'

But you want to use this:

SELECT @tmpCode = '''SELECT value(''''ID_ARTYKULU[1]'''')'''
--'SELECT value(''ID_ARTYKULU[1]'')'

A ghetto fix would be to replace your @tmpCode section with this:

SELECT @tmpCode = N'''' + REPLACE('SELECT value(''ID_ARTYKULU[1]'')', N'''', N'''''') + N''''

Since you're using dynamic SQL, you need to be very careful when escaping quotes.