Suggestions on exporting XML file from SQL to file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Suggestions on exporting XML file from SQL to file

Author

Topic

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-01-28 : 14:36:33

Ok, I've done some research and have ruled out I do not want to use the BCP option of exporting an XML file to the file system of a remote computer. Mainly due to the necessity of the xp_cmdshell procedure and the level of individuals that will need access to this.

So, with that in mind, does anyone have any suggestions for pushing data from SQL Server to a file in an XML format other than the sp_OA procedures?

To provide insight, I have a query similar to this:

SELECT
    <column1>,
    <column2>,
    <column3>,
    <column4>,
    <column5>
FROM <database>.<schema>.<table1> AS T1
    LEFT JOIN <database>.<schema>.<table2> AS T2
        ON T1.<field> = T2.<field>
FOR XML PATH('DocumentName'), ROOT('NodeName'), ELEMENTS XSINIL, TYPE

The code that actually runs this is in it's own stored procedure then I would use code like so to push this to disk:

xp_cmdshell 'bcp "EXEC <database>.<schema>.<somestoredprocedure>" queryout "\\REMOTESERVER\SHARENAME\FILENAME.XML" -c -S<database\instance> -T'

This actually worked like a charm. The only caveat is the permissions required to execute xp_cmdshell. I'm simply not comfortable with the options of providing access to the level of users that would need to access and execute xp_cmdshell. So I'd like to find another way to export this data from SQL Server to disk.

Any suggestions?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-28 : 14:38:49

well since i'm not familiar with any onter pure T-SQL way of doing that you might want to do this with CLR Stored procedures.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-01-28 : 14:53:38

save a DTS package and call that from a sp

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-01-29 : 10:56:12

quote:
Originally posted by sakets_2000

save a DTS package and call that from a sp

DTS does not exist in 2005 am I correct? It's replaced by SSIS. Honestly, I was looking for a quick and dirty (yet stable) solution. I ended up creating a custom role for executing the OLE procs and using those. I simply granted the necessary users that role. I'm able to stuff the results of the above query into an XML variable and write that to file via the Scripting.FileSystemObject.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources