使用select ...for xml生成的xml文件如何重新导入到数据库中

[ansi]

如我用select top 2 * from admin for xml auto 生成的xml文件

<admin id="4" username="admin" password="admin" levels="1" content="" deleted="0"/>
<admin id="5" username="123" password="123" levels="4" content="" deleted="0"/>

如何导入另一个数据库中?

[pengdali]

DECLARE @idoc int
DECLARE @doc varchar(1000)
set @doc='<root>'+'<admin id="4" username="admin" password="admin" levels="1" content="" deleted="0"/>
<admin id="5" username="123" password="123" levels="4" content="" deleted="0"/>'+'</root>'

--开始：

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT *
FROM OPENXML (@idoc, '/root/admin',1)
WITH ([id] int,
username varchar(20),
[password] varchar(20),
levels int,
content varchar(8000),
deleted int)

--或：

insert 你的表 SELECT *
FROM OPENXML (@idoc, '/root/admin',1)
WITH ([id] int,
username varchar(20),
[password] varchar(20),
levels int,
content varchar(8000),
deleted int)

EXEC sp_xml_removedocument @idoc

[ansi]

username varchar(20),
[password] varchar(20),
levels int,
content varchar(8000),
deleted int)
什么意思?我得表已经建好了 ,而且字段大小都规定好了

[pengdali]

使用openxml的格式是这样的。

insert 你的表 SELECT *
FROM OPENXML (@idoc, '/root/admin',1)
WITH ([id] int,
username varchar(20),
[password] varchar(20),
levels int,
content varchar(8000),
deleted int)

[ansi]

明白了,谢谢:)