I need to export tables starting with ‘Params’ into one XML file in MS SQL Server 2008 R2, without using temp tables. How do I merge the outputs?
EXEC sp_IterateTables @command = N'SELECT * FROM ? FOR XML PATH(''Item'')', @filter = N'AND name LIKE ''Params%'''
In a similar scenario, I opted to build a dynamic SQL statement that concatenates the XML outputs into a single result. The idea is to iterate over the table names and use FOR XML PATH for each, then union them under a common XML root element. This approach allows for a clean aggregation without using temporary tables. I carefully ensured that each fragment had a wrapping node to avoid nested XML errors. Using a variable to hold the final XML and appending during each iteration proved to be effective in my implementation.
i merged the xml by dynamically wrapping each table’s for xml result in a common root. simple solution that avoids temp tables and keeps each fragment intact. works well in 2008.
hey, i tink you might try wraping each table’s xml result with a common root and then concat them - i did a similar thing and it helped clear nesting hiccups. how r u handling attributes in your xml structure?
In my experience, a useful alternative is to leverage nested FOR XML queries with the TYPE directive to maintain proper XML structure throughout the concatenation process. I developed a dynamic SQL procedure that constructs a full XML document by appending the result of each table while ensuring valid node structures. This approach not only avoids the use of temporary tables but also simplifies error handling and XML formatting. The method proves reliable for merging results from multiple tables into one comprehensive XML file.