Automate XML file build from SQL stored proc

Can anyone help with automating the build of an xml file?  Essentially, I can execute a query (see below) and view desired results in SQL Server Management Studio, then obviously copy and paste to a text file.  I need to somehow replace these last two steps, by either modifying the query to write to a desired XML file or building a DTX package to accomplish the steps (or other?).  

 SELECT [item-no] as "Style",
       (select offer as "SourceCode" from zztemp3 z where z.[item-no] = c.[item-no]  for xml path (''), type) as "SourceCodeList"
FROM   (select distinct [item-no] from zztemp3) c

We are using SQL 2005.  Thanks in advance --Karen
Who is Participating?
Mark WillsConnect With a Mentor Topic AdvisorCommented:
Here you go...

create procedure u_XML_ZZTEMP as
SELECT [item-no] as "Style",
       (select offer as "SourceCode" from zztemp3 z where z.[item-no] = c.[item-no]  for xml path (''), type) as "SourceCodeList"
FROM   (select distinct [item-no] from zztemp3) c

--  then run a bcp export - change MyDB for your database, and assumes Windows / mixed security - otherwise specifiy user and password...

declare @bcp varchar(2000)
set @bcp = 'bcp "exec MyDB..u_xml_zztemp" queryout "c:\zztemp_xml_'+convert(varchar,getdate(),112)+'.xml" -T -c -CACP'
exec xp_cmdshell @bcp
I would create an SSIS package...use a data flow task w/ your stored procedure as your source and a raw data file as your desintation (the xml)
PetEdgeAuthor Commented:
I've tried this but haven't been able to get it to successfully work.

In Control Flow, I've created an Execute SQL task.
Under General tab, Resultset is XML.
Under Result Set tab:
   Result Name        Variable Name
   0                          User::XMLContent

SSIS variables:
Name              Scope             Data Type    Value
XMLContent   Package          String

I am able to execute the above SQL task successfully.  (Note that the Package variable never seems to get populated).

Next, I added a Script task, see below.  When the script runs, I get the below error:

Error: The script threw an exception: The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Pulic Sub Main()
        Dim sw As New IO.StreamWriter("C:\Temp\test.xml")
        Dts.TaskResult = Dts.Results.Success
    End Sub
End Class

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Mark WillsTopic AdvisorCommented:
Just add a new job to sql server agent... work your way through the pop-up, add a new step, and simply paste the three line of the bcp part of the sql.
PetEdgeAuthor Commented:
Hi Mark,

I was just working my way through your solution.  By default, xp_cmdshell is turned off as part of the security configuration.  Once I turned it on in my dev environment, your code worked beautifully and the file was as desired.

Can you speak to security ramifications of turning xp_cmdshell on?


if xp_cmdshell is on, then you're leaving your machine/network open to possible attack if someone was to get into your SQL machine w/ malicious intentions.
Mark WillsTopic AdvisorCommented:
Create a specific user, allow them access to xp_cmdshell... Would be inclined to create them as a Windows user as well - same password. No one logs in as that user, but you can nominate that user when setting up the Job. For all intensive purposes, it is another sysadmin type user, and really only used for batch type jobs where extra permission is required. So long as you control and "own" that user, then others cannot get access. Would be a slightly different story if you were to start creating OSQL or Windows command files where it could be easier to incorporate "unauthorised" sql code.

When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail. That is what you want, so do NOT create a proxy...

Requires CONTROL SERVER permission, so hopefully "mere mortals" are not part of sysadmin, nor should they have CONTROL SERVER permissions...

My esteemed colleague mr chapmandew is probably more up to date with these types of security scenarios...

chapmandewConnect With a Mentor Commented:
Recently, we had a security firm come in and do some analysis on the db servers where I work.  They found one sql server (not one I administer, mind you.  :) ) that had a blank sa pwd.  They were able to log into that server, and with the use of xp_cmdshell create a local machine user, promote that user to a domain user.  Then, do a dump of the password hashes and promote itself to a domain admin.  From there, the world was their oyster and they could do anything they wanted on the system.  Scary stuff.
PetEdgeAuthor Commented:
Mark and chanmandew-- thanks to you both.  
I would like to leave this open until I hear back from my DBA, concerning use of xp_cmdShell.  If this is nixed.. then it's back to working on the SSIS solution.
Mark WillsTopic AdvisorCommented:
Given @chapmandews comment, then you could always turn it on for the purposes of running that Job, then turn it off again. Because that "special" user does have some privileges, then shouldn't be a problem.

If someone can access your SQL server well enough to start using xp_cmdshell, then they have to be able to get in at a privileged level - in which case, you have a bigger security concern than just xp_cmdshell... Such as a "sa" user without a password - heavens forbid !! Not surprising - there are plenty of machines out there with blank SA password, and then there's the batch jobs, web config files, and windows command files that openly display a connection string - that is the security risk.

the commands themselves are usually not the security risk as such (there are plenty), it is more a case of having a properly and tightly controlled security matrix - something that a lot of sites do not always pay attention to when starting out, and then becomes on of those "difficult" tasks all too easy to delay until "tommorrow". That's why you hear a fair bit about things like xp_cmdshell. In reality, it is a fairly privileged function and should be treated as such.

I have no problems using and recommending, but, it is normally under the guise of "control your security matrix".
PetEdgeAuthor Commented:
While my DBA is adverse to opening this up to users, it can be run by a service account so I can move forward this this solution.  Thanks again!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.