将表导成XML，到底应如何作？请大家指点

[zx197093]

我的问题很简单，就是在SQL2000中如何将一个表的内容导成XML文件，存在C：\中，请大家告诉我详细的步骤！尤其是如何存盘。谢谢

[伍子V5]

EXEC master..xp_cmdshell 'bcp "SELECT * FROM tablename for xml auto" queryout "c:\docu.xml" -c -Sservername -Usa -Ppassword'

[zjcxc]

--调用ADO存储XML

declare @err int,@src varchar(255),@desc varchar(255)
declare @obj int,@str varchar(1000)
set @str='Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog='
+db_name()+';Data Source='+@@servername

--上面是用ADO连接SQL的字符串,根据你的情况做调整
/*--ADO连接SQL的字符串模板
@str='Provider=SQLOLEDB.1;Persist Security Info=True;User ID=用户名;Password=密码;Initial Catalog=数据库名;Data Source=SQL服务器名'
--*/

exec @err=sp_oacreate 'adodb.recordset',@obj out
if @err<>0 goto lberr

exec @err=sp_oamethod @obj,'open',null,'要存储成XML文件的表名或查询',@str
if @err<>0 goto lberr

exec @err=sp_oamethod @obj,'save',null,'c:\a.xml',1 --保存到c:\a.xml中
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 错误号
,@src as 错误源,@desc as 错误描述

[zjcxc]

注意，如果:c:\a.xml文件已经存在，则保存不会成功，这果，可以在保存前进行删除。

exec master..xp_cmdshell 'del c:\a.xml'
exec @err=sp_oamethod @obj,'save',null,'c:\a.xml',1

[txlicenhe]

1:局长的试了一下没成功，生成了文件，但资料好象不对劲
2：联机帮助
Exporting to a File
The following example shows how to export object instance data for two repository objects. You do not need to bracket an export within a transaction. To release the objects after the export concludes, set the objects to nothing.

dim oExp as new Export
dim oMyObj1 as RepositoryObject
dim oMyObj2 as RepositoryObject
dim oRep as new Repository
dim oRoot as RepositoryObject

set oRoot=oRep.Open "SERVER=MyServer;DATABASE=MyDB;UID=sa;PWD=MyPassword;"
set oMyObj1=oRep.Object(objid1)
set oMyObj2=oRep.Object(objid2)
oExp.add oMyObj1
oExp.add oMyObj2
oExp.Export "c:\temp\myXmlFile.xml", INDENTATION
Set oMyObj1=Nothing
Set oMyObj2=Nothing
Set oRoot=Nothing
Set oRep=Nothing
Set oExp=Nothing