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

核心提示： --表binsert into Xmltb(Name,Nowtime)select * from openxml(@idHandle,N'/Xmltables/Xmltb')with dbo.XmltbEXEC sp_xml_removedocument @idHandle

--表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
/*

---空的处理

<dbo.xmltable name="1" Intro="" />
<dbo.xmltable name="2" />
<dbo.xmltable name="3" Intro="c" />
1　
2　 NULL
3　 c
*/
drop table xmlt