November 24, 2008 at 2:13 pm
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?
December 4, 2008 at 11:33 am
After much trial and error i answered by my own question.
The variables need to be VARCHAR(MAX). A third variable, also VARCHAR(MAX), is set to be @a+@b with a ROOT node added to the front and back of the new variable.
DECLARE @a VARCHAR(MAX)
DECLARE @b-2 VARCHAR(MAX)
SET @a =(query Table1)
SET @b-2 =(query Table2)
SET @C = rootname + @a + @b-2 + rootname
The @C variable, though a VARCHAR data type, can be inserted to an XML field in a table, which is what i wanted.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply