Store a table/query as a standard XML file

xml| Standard


/*--saved as XML


stores tables/queries as standard XML files


--*/





/*--Call Example


-The case of winows authentication with SQL


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