I need to execute a SQL statement that returns XML (for XML AUTO, ELEMENTS) then I need to INSERT the XML into a field in a Different Database an Table from within the SAME stored proc.

The first database is an online transactional database.

The second database is used by a proprietary system to manage file transfers from our National Headquarters to our 4000 offices.

The XML generated will be stored on location as a file. Information is the file is used to drive Desktop Software behavior.

So how do I do this

MyStoredProc
(
)
As
INSERT INTO SQLDB2 (myDestinationField) FROM (SELECT * FROM JOINS in DB1 FOR XML AUTO, ELEMENTS..)

Since I am new to SQL2000 a good code sample would be appreciated.

First off your destination db must exist on the same server as the source db or you must use linked server(this is a pain) and the user that executes the stored procedure must have permissions on both dbs.

second, when I try to run this code it fails
    INSERT INTO NorthWind.dbo.Categories (CategoryName, Description)
    SELECT Title, Body
    FROM Article
    FOR XML AUTO
with the following error: The FOR XML clause is not allowed in a INSERT statement.

this will work (but its not what you want.)
    INSERT INTO NorthWind.dbo.Categories (CategoryName, Description)
    SELECT Title, Body
    FROM Article


I would recommend using a webservice or exe to handle the get of xml from the first db then insert the result to the second db as a text column?

most of my experience with FOR XML is for generating xml for xslt transformation to html, not for updates but I do recall reading about it some time ago Wrox does have a good book on MSSQL2000 XML http://www.amazon.com/exec/obidos/tg...glance&s=books

You could - if your xml is always going to be less than 8000 characters build an xml string manually and then insert this varchar.

    Declare @Title varchar(150)
    Declare @Author varchar(150)
    Declare @xml varchar(8000) -- max varchar

    -- get data from source db
    SELECT @Title = ISNULL(Title, 'no data'),
        @Author = ISNULL(Author, 'no data')
    FROM Article
    WHERE ArticleID = 3

    -- build xml
    SET @xml = '<Root>' +
        '<Title>' + @Title + '</Title>' +
        '<Author>' + @Author + '</Author>' +
        '</Root>'

    -- insert to destination db
    INSERT INTO Northwind.dbo.Categories(CategoryName, Description)
    VALUES('Test', @xml)

this has serious limitations and I would not recommend it.