SQL 2005对xml文件与xml数据的操作

核心提示：---同时读入多表数据create table Xmlta(Name nvarchar(20),Nowtime datetime)create table Xmltb(Name nvarchar(20),Nowtime datetime)declare @s as nvarchar(4000);set @s =N

-----------------------同时读入多表数据----------------

create table Xmlta(Name nvarchar(20),Nowtime datetime)
create table Xmltb(Name nvarchar(20),Nowtime datetime)
declare @s as nvarchar(4000);
set @s =N'
<Xmltables>
　　　 <Xmlta Name="1" Nowtime="1900-2-1">0</Xmlta>
　　　 <Xmltb Name="2" Nowtime="1900-2-1">0</Xmltb>
</Xmltables>
';
--<Xmlta ></Xmlta> 则插入的数据为null
declare @idHandle as int ;
EXEC sp_xml_preparedocument @idHandle OUTPUT, @s

--表a

insert into Xmlta(Name,Nowtime)
select * from openxml(@idHandle,N'/Xmltables/Xmlta')
with dbo.Xmlta

--表b

insert into Xmltb(Name,Nowtime)
select * from openxml(@idHandle,N'/Xmltables/Xmltb')
with dbo.Xmltb
EXEC sp_xml_removedocument @idHandle
select * from Xmlta
select * from Xmltb
drop table Xmlta,Xmltb--生成xml文件单表

DECLARE @xVar XML
SET　　 @xVar = (SELECT * FROM Xmltable FOR XML AUTO,TYPE)
select @xVar

--1读取xml文件插入表中

DECLARE @hdoc int
DECLARE @doc xml
select @doc=BulkColumn　from　(SELECT *
FROM OPENROWSET(BULK 'E:xml.xml',SINGLE_BLOB) a)b
EXEC sp_xml_preparedocument @hdoc OUTPUT,@doc
SELECT * into #temp
FROM OPENXML (@hdoc,N'/root/dbo.xmltable')
with　(name nvarchar(20),Intro nvarchar(20))
exec sp_xml_removedocument @hdoc

--2读取xml文件插入表中

SELECT * into #temp FROM OPENROWSET(
BULK 'E:xml.xml',SINGLE_BLOB) AS x
DECLARE @hdoc int
DECLARE @doc xml
select @doc=BulkColumn　from #temp
EXEC sp_xml_preparedocument @hdoc OUTPUT,@doc
SELECT * into #temp2
FROM OPENXML (@hdoc,N'/root/dbo.xmltable')
with　(name nvarchar(20),Intro nvarchar(20))
exec sp_xml_removedocument @hdoc
/*