I want to extract entire rows from multiple tables in XML format, bcp.. queryout the results to a file, and place the extracted data on another server. The question is how to place the data from multiple tables within a single XML root.
Using the following gives me all the XML data I want but no root.
DECLARE @a XML
DECLARE @b-2 XML
SET @a =(query Table1)
SET @b-2 =(query Table2)
SELECT @a
UNION ALL
SELECT @b-2
Using the following gives me the root I want, but also a carriage return after 2034 XML characters (who knows why).
DECLARE @a XML
DECLARE @b-2 XML
SET @a =(query Table1)
SET @b-2 =(query Table2)
SELECT @a AS [node()]
UNION ALL
SELECT @b-2 AS [node()]
FOR XML PATH(''), ROOT('rootname')
Any ideas on how to get a root node and a clean XML string when extracting whole rows from multiple tables?