Search code examples
sql-serverxmlbcp

Use stored procedure so that the output can be saved in a file or as an output parameter

I am using SQL Server 2008. I have a stored procedure that returns XML data. I want to use the stored procedure using `bcp' and return the output to an XML file or call the stored procedure within another stored procedure and store the data as an XML variable.

To save it to a file the stored procedure so not have an OUTPUT parameter and returns the data as XML.

This works

SET @CopyXmlCmd = 'bcp "EXEC CreateXMLFileOfDatabase " queryout "' + @FileAndPath + '" -T -w -S ' + @@SERVERNAME + ' -d ' + DB_NAME()
EXEC master..XP_CMDSHELL @CopyXmlCmd

The stored procedure, CreateXMLFileOfDatabase has no output or input parameters. But I need to also have this stored procedure work for this:

DECLARE @XmlTargetDB XML;
EXEC CreateXMLFileOfDatabase @ResultDB = @XmlTargetDB

The signature for this version of the stored procedure is this:

CREATE PROCEDURE [dbo].[CreateXMLFileOfDatabase] @ResultDB XML OUTPUT

Can I use bcp using a stored procedure that has an output parameter and store the results to a file?

Solution

  • You might try something like this:

    I assume, that the final command in your SP is SELECT TheXml;

    In this case you can declare a fitting table and insert the result:

    DECLARE @tbl TABLE(MyXMLColumn XML);
INSERT INTO @tbl(MyXMLColumn)
EXEC YourProcedure;

    --From this table you can set the value to an XML variable:

    DECLARE @ResultXML XML;
SELECT @ResultXML=MyXMLColumn FROM @tbl;

    UPDATE: Example

    Try this:

    CREATE PROCEDURE dbo.TestOutputXML
AS
BEGIN
    SELECT CAST('<root>test</root>' AS XML) AS Test
END
GO

DECLARE @tbl TABLE(MyResult XML);

INSERT INTO @tbl(MyResult)
EXEC dbo.TestOutputXML;

DECLARE @MyXMLVariable XML=(SELECT TOP 1 MyResult FROM @tbl);

SELECT @MyXMLVariable;
GO

DROP PROCEDURE dbo.TestOutputXML;