How to concatenate multiple SQL "SELECT AS XML PATH " query results INTO one XML file-CodePudding

I have multiple SELECT ... AS XML PATH queries in one MS SQL program, but I need the result of program to be in one single xml file.

For example, how to concatenate xml results of multiple select...as xml into one result xml file in one SQL program? Problem is, each of these queries is created using CURSOR so I can't use UNION ALL.

This cursor fetches table names from INFORMATION_SCHEMA.TABLES and each select is from different table. Not to complicate but each select is created dynamically as it's first created as VARCHAR then executed using exec(@sql) so

fetch c_table_name from cursor_c
@sql=     ' select
           id as ''value/@id'',
           name as ''value''
           from _TABLENAME_
           where somevalue=cursorValue
           for xml path('''')'


set @sql = replace(sql, '_TABLENAME_, c_table_name
exec(@sql)

each such select xml result for example:

   <value id=1>aaaa</value>
   <value id=2>bbb</value>

some next table in cursor could give another result, for example:

   <value id=11>ccc</value>
   <value id=21>ddd</value>

But the result would be separate xml files and I want just one like this:

<value id=1>aaaa</value>
<value id=2>bbb</value>
<value id=11>ccc</value>
<value id=21>ddd</value>
....

Is that possible? I tried putting the result into xml variable and then add another result into same variable, this doesn't work. Nothing I tried works. Problem is that each select is dependent on cursor. It has to be like that, selects are not this simple this is just example. Is it even possible to have multiple SELECT... FOR XML PATH statements and somehow manage them to generate one single XML result by concatenating them all?

CodePudding user response：

Instead of using a cursor and executing your statements one by one, you could just build one SQL statement using STRING_AGG() to create your unions and then execute that:

DECLARE @sql NVARCHAR(MAX) = '';
SELECT  @sql = STRING_AGG(CONCAT('SELECT id AS [value/@id], name AS [value] FROM ', 
                            QUOTENAME(TABLE_SCHEMA), '.', QUOTENAME(TABLE_NAME), 
                            ' FOR XML PATH('''')'), ' UNION ALL ')
FROM    INFORMATION_SCHEMA.TABLES 
WHERE   ....

PRINT @sql;
--EXECUTE sp_executesql @sql  -- Check first with print, then uncomment to execute

With that being said, if you have so many tables that are closely related enough to make sense to union together, but numerous enough that it requires dynamic SQL to reference them all, then this is something of a code smell for me. If all the tables really are related and make sense to be unioned, then perhaps a view is on order, or perhaps they should actually be one table?

CodePudding user response：

Maybe you can create a temporary table before the cursor, inside the cursor insert each select to this table and after the cursor is closed do one select from this table and then drop it.