Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Save to XML Stored Procedure (500 points)

Posted on 2003-03-11
8
Medium Priority
?
1,263 Views
Last Modified: 2008-02-07
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?
0
Comment
Question by:makman111
  • 4
  • 2
  • 2
8 Comments
 
LVL 4

Expert Comment

by:xxg4813
ID: 8112262
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!
0
 

Author Comment

by:makman111
ID: 8113506
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
0
 

Author Comment

by:makman111
ID: 8113542
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:makman111
ID: 8113578
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
0
 
LVL 4

Accepted Solution

by:
xxg4813 earned 2000 total points
ID: 8113609
Hi,

don't worry about that, just check out your xml file. All the records are there already.


Good luck!
0
 
LVL 2

Expert Comment

by:emalagar
ID: 8113642
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.
0
 

Author Comment

by:makman111
ID: 8114105
Moderator, can you split the points equally?
0
 
LVL 2

Expert Comment

by:emalagar
ID: 8114473
its ok mak - give it all to xxg4813 - just as long as you have the answer you need.

Have a good day
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question