asked on

Save to XML Stored Procedure (500 points)

I need to create a stored procedure that will execute another stored proc and write the results to disk as a XML file (auto).

How would I go about doing this?

xxg4813

Hi,

BCP and FOR XML Clause
The bcp command-line utility allows copying data between SQL Server 2000 and an external file. To this utility, we can pass the queryout parameter and a SQL query, to bulk copy data returned by a query into a file.

---------------------
Use Northwind
GO

CREATE PROCEDURE sproc_GetShippers
AS
SELECT *
FROM Shippers
FOR XML AUTO, ELEMENTS

bcp "EXEC Northwind..sproc_GetShippers" queryout c:\temp\bcpOut.xml -S. -Usa -P -c -r -t

-----------------------

Once you run the above command on the DOS command prompt, open c:\temp\bcpOut.xml in either notepad or Internet Explorer, and you'll see that it is missing the root node, and hence is not a well-formed XML. Here is a solution:

Start notepad and create a text file named c:\temp\header.txt:

<ShippersInfo>

Create another text file named c:\temp\footer.txt:

</ShippersInfo>

Now run the bcp command first, and then run the following command:

C:\temp>Copy header.txt+bcpOut.xml+footer.txt result.xml /B

Good luck!

makman111

ASKER

I am getting an error...

C:\>bcp "EXEC Northwind..sproc_GetShippers" queryout c:\temp\bcpOut.xml -S. -Usa
-P -c -r -t
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file

makman111

ASKER

Okay... Got the previous error figured out, but here is a new one.

C:\>bcp "EXEC Northwind..sproc_GetShippers" queryout c:\temp\bcpOut.xml -S. -Usa
-P -c -r -t

Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Warning: Server data (347
bytes) exceeds host-file field length (0 bytes) for field (1). Use prefix leng
th, termination string, or a larger host-file field size. Truncation cannot occ
ur for BCP output files.

1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 16

makman111

ASKER

ASKER CERTIFIED SOLUTION

xxg4813

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

emalagar

Hi makman,
Here is an alternative solution that uses a combination of xp_cmdshell and osql utilities with FOR XML.

The following query can be ran from query analyzer to get all the product information of the northwind database in XML format.

-- BEGIN SQL
EXEC master.dbo.xp_cmdshell 'osql -S YOURSERVERNAME -U YOURDBLOGINNAME -P YOURPASSWORD -d Northwind -Q "SELECT * FROM PRODUCTS FOR XML AUTO" > c:\result.xml'
-- END SQL

specify: YOURSERVERNAME , YOURDBLOGINNAME , YOURPASSWORD

Also note that the result is redirected to a file c:\result.xml in the DATABASE SERVER!!!!! not the client's drive.

makman111

ASKER

Moderator, can you split the points equally?

emalagar

its ok mak - give it all to xxg4813 - just as long as you have the answer you need.

Have a good day