?
Solved

Save to XML Stored Procedure (500 points)

Posted on 2003-03-11
8
Medium Priority
?
1,236 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 

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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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

770 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