/ * - Save XML to store table / query storage as standard XML file - Zou Jian 2003.12 (please keep this information) - * /
/ * - Calling example - Sitting with SQL with WinOWS authentication EXEC P_SAVEXML @ SQL = 'Area Information', @ fname = 'C: / Regional information .xml'
- Use the specified user exec p_savexml @ SQL = 'area information', @ 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] Go
Create Proc p_savexml @ SQL VARCHAR (8000), - Table / View / SQL Query Statement @Fname Varchar (1000) = 'C: /TMP.XML', - Saved XML File Name @Userid Varchar (50 ) = ', - user name, if it is a NT authentication mode, it 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 @10 = 'provider = sqloledb.1; persist security info = true;' 'user source =' @@ servername
Exec @ Err = sp_oacreate 'adodb.recordset', @ Obj outif @err <> 0 goto lberr
Exec @ Err = SP_OAMETHOD @ Obj, 'Open', NULL, @ SQL, @ Constrif @err <> 0 goto lberr
Set @ SQL = 'del' @ fnameexec master..xp_cmdshell @ SQL, NO_OUTPUTEXEC @ ire = sp_oamethod @ Obj, 'Save', Null, @ fname, 1IF @err <> 0 goto lberr
Exec @ err = sp_oadestroy @objreturn
