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

核心提示： ---读入第二个表数据create table Xmlta(Name nvarchar(20),Nowtime nvarchar(20))declare @s as nvarchar(4000);set @s =N'<Xmltables> <Xmltb Name=

-----------------------读入第二个表数据--------------------

create table Xmlta(Name nvarchar(20),Nowtime nvarchar(20))
declare @s as nvarchar(4000);
set @s =N'
<Xmltables>
　　　 <Xmltb Name="6" Nowtime="1900-2-1">0</Xmltable>
　　　 <Xmlta Name="11" Nowtime="1900-2-1">0</Xmlta>
</Xmltables>
';
declare @idHandle as int ;
EXEC sp_xml_preparedocument @idHandle OUTPUT, @s
insert into Xmlta(Name,Nowtime)
select * from openxml(@idHandle,N'/Xmltables/Xmlta')
with dbo.xmlta
EXEC sp_xml_removedocument @idHandle
select * from Xmlta
drop table Xmlta

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

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