/ * - Save XML to store table / query storage as standard XML file - * // * - call example - SQL with WinOWS authentication EXEC P_SAVEXML @ SQL = 'Area Information', @ fname = 'C: / Regional Information .xml' - Use the specified user exec p_savexml @ SQL = 'area data', @ fname = 'C: / region 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] GOcreate proc p_savexml @ sql VARCHAR (8000), - Table / View / SQL Query Statement @fname VARCHAR (1000) = 'c: /tmp.xml', - Save XML File Name @Userid Varchar (50) = ', - User name, if the NT authentication method is, '' @password varchar (50) = '- password asdeclare @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 () ' ; Data Source = ' @@ servernameelse set @ constr =' Provider = SQLOLEDB.1; Persist Security Info = True; ' ' User Source = ' @@ servernameexec @ err = sp_oacreate' adodb.recordset ', @ obj outif @err <> 0 goto lberrexec @ err = sp_oamethod @ Obj, 'open', null, @ SQL, @ constrif @err <> 0 goto lberrset @ sql = 'del' @ fnameexec master..xp_cmdshell @ sql, no_outputexec @ err = sp_oamethod @ obj, 'save', null, @ fname, 1if @err <> 0 goto lberrexec @ Err = sp_oadestroy @objreturnlberr: EXEC SP_OAGETERRORINFO 0, @ src out, @ desc out select cast (@
