Store a table/query as a standard XML file

/*--Save As XML
Store a table/query as a standard XML file
--Jiangjian 2003.12 (please keep this information for reference)--*/

/*--Call Example
--using SQL to authenticate with Winows
exec p_savexml @sql = ' Area information ', @fname = ' c:/area information. xml '

--With the specified user
exec p_savexml @sql = ' Area information ', @fname = ' c:/area information. xml ', @userid = ' sa '
--*/
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ P_savexml] and OBJECTPROPERTY (ID, N ' isprocedure ') = 1)
drop procedure [dbo]. [P_savexml]
Go

Create proc P_savexml
@sql varchar (8000),--The table/view/SQL query statement to be saved
@fname varchar (1000) = ' C:/tmp.xml ',--saved XML file name
@userid varchar (50) = ', user name, if NT authentication method, then '
@password varchar (50) = '--Password
As
declare @err int, @src varchar (255), @desc varchar (255)
declare @obj int, @constr varchar (1000)
If IsNull (@userid, ') = '
Set @constr = ' provider=sqloledb.1;integrated security=sspi; Persist Security info=false;initial catalog= '
+db_name () + ';D ata source= ' +@ @servername
Else
Set @constr = ' Provider=SQLOLEDB.1; Persist security info=true; '
+ ' User id= ' + @userid + '; Password= ' +isnull (@password, ')
+ '; Initial catalog= ' +db_name () + ';D ata source= ' +@ @servername

exec @err =sp_oacreate ' Adodb.recordset ', @obj out
If @err <>0 goto Lberr

EXEC @err =sp_oamethod @obj, ' open ', null, @sql, @constr
If @err <>0 goto Lberr

Set @sql = ' del ' + @fname
EXEC master.. xp_cmdshell @sql, No_output
EXEC @err =sp_oamethod @obj, ' save ', NULL, @fname, 1
If @err <>0 goto Lberr

EXEC @err =sp_oadestroy @obj
Return

Lberr:
EXEC sp_OAGetErrorInfo 0, @src out, @desc out
Select CAST (@err as varbinary (4)) as error number
, @src as error source, @desc as error description
Go