Physical XML File

I have a stored proc that writes the output of a Select Statement to a table with an xml data type (see attached file).  Everything worked fine on the Development box but when I pushed to Production, the DBA has xp_cmdshell locked down and he will not unlock it just for this process.  

Is there any other way to automate the  creation a physical XML file?  I need a file to ftp to a customer. StoredProc1.txt
sherbug1015Asked:
Who is Participating?
 
ZberteocConnect With a Mentor Commented:
I am not familiar with SSIS either and I will never be and that is because I will not use it. However of course is possible to to that.

But a much simpler solution would be to use a batch file to execute the bcp command.

You place the bcp command as it is built by the stored procedure in a text file with extention .bat and simply execue it from windows, or you can execute the bcp command itself.

In bcp you have to qualify the objects you use in queries with the database name they belog to because the bcp command doesn't have a parameter/switch for the database name only for the server name. In the exemple below I added DATABASE_NAME to the table name.
bcp " SELECT '<?xml version="1.0" encoding="UTF-8" ?>'+CONVERT(VARCHAR(MAX),xml_data) FROM DATABASE_NAME.tblXMLLog WHERE xml_id =(Select Max(xml_id) from DATABASE_NAME.dbo.tblXMLLog)"  queryout LocationData.xml -w -T -SBERTEATOTH-XP

Open in new window

0
 
ZberteocCommented:
You will have to execute that bcp command from outside the SQL server. You could use a DTS or SSIS or a simple batch file that you can execute. You will have to find a way to synchronize the outside process with the inside process to grab the content and export it when needed.
0
 
ZberteocCommented:
You could also use the SQLCMD utility in windows to execute what query you want and redirect the result to a file but it still the same solution and that is execute the export from outside the SQL server

There is no way to export data from SQL server itself when the xp_cmdshell is disabled.
0
 
sherbug1015Author Commented:
When I use bcp from within the stored procedure, I am selecting the xml_data field from the table.  Can I still do this with SSIS.

If I execute the attached stored proc (minus the bcp call) from an SSIS Execute SQL Task, then I will have an XML record in the table.  

Can I then use an Execute Process Task from SSIS and basically do the same thing as what the stored procedure does?

Can you possibly give me an example of how to do this from SSIS.   I am not that familiar with SSIS.  

0
All Courses

From novice to tech pro — start learning today.