SQLSERVER 2005中使用sql语句对xml文件和其数据的进行操作(很全面)

--用sql多条可以将多条数据组成一棵xml树l一次插入
--将xml树作为varchar参数传入用
--insert xx select xxx from openxml() 的语法插入数据
-----------------------------------导入，导出xml--------------------------

--1导入实例
--单个表
create table xmltable(name nvarchar(20),nowtime nvarchar(20))
declare @s as nvarchar(2000);
set @s = n''
<xmltables>
<xmltable name="1" nowtime="1900-1-1">0</xmltable>
<xmltable name="2" nowtime="1900-1-1">0</xmltable>
<xmltable name="3" nowtime="1900-1-1">0</xmltable>
<xmltable name="4" nowtime="1900-1-1">0</xmltable>
<xmltable name="5" nowtime="1900-1-1">0</xmltable>
</xmltables>'';
declare @idhandle as int ;
exec sp_xml_preparedocument @idhandle output, @s
insert into xmltable(name,nowtime)
select * from openxml(@idhandle,n''/xmltables/xmltable'')
with dbo.xmltable
exec sp_xml_removedocument @idhandle
select * from xmltable
-----------------------读入第二个表数据--------------------
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
--表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
------------------------------------xml数据操作------------------
--类型化的xml
create table xmlt(id int primary key, xcol xml not null)
--t-sql生成数据
insert into xmlt values(1,
''<xmltables>
<xmltable name="1" nowtime="1900-1-1">1</xmltable>
<xmltable name="2" nowtime="1900-1-2">2</xmltable>
<xmltable name="3" nowtime="1900-1-3">3</xmltable>
<xmltable name="4" nowtime="1900-1-4">4</xmltable>
<xmltable name="5" nowtime="1900-1-5">5</xmltable>
</xmltables>'')
--dataset生成数据
insert into xmlt values(2,
''<?xml version="1.0" encoding="gb2312" ?>
<xmltables>
<xmltable><name>1</name><nowtime>1900-1-1</nowtime>1</xmltable>
<xmltable><name>2</name><nowtime>1900-1-2</nowtime>2</xmltable>
<xmltable><name>3</name><nowtime>1900-1-3</nowtime>3</xmltable>
</xmltables>'')
--读取name=1 的節點，請使用
select xcol.query(''/xmltables/xmltable[@name="1"]'') from xmlt where id =1
--读取name=1 的節點值，請使用
select xcol.query(''/xmltables/xmltable[@name="1"]/text()'') from xmlt where id =1
--读取name=5 的name 屬性值，請使用
select xcol.query(''data(/xmltables/xmltable[@name])[5]'') from xmlt where id =1
--读取所有节点name
select nref.value(''@name'', ''varchar(max)'') lastname
from xmlt cross apply xcol.nodes(''/xmltables/xmltable'') as r(nref) where id=1
--读取所有节点nowtime
select nref.value(''@nowtime'', ''varchar(max)'') lastname
from xmlt cross apply xcol.nodes(''/xmltables/xmltable'') as r(nref) where id=1
select xcol.query(''data(/xmltables/xmltable[@name=5]/@nowtime)[1]'') from xmlt where id =1
--读取name=1 的name 屬性值
select xcol.value(''data(/xmltables/xmltable//name)[1]'',''nvarchar(max)'') from xmlt where id=2
--读取nowtime=1 的nowtime 屬性值
select xcol.value(''data(/xmltables/xmltable/nowtime)[1]'',''nvarchar(max)'') from xmlt where id=2
--select xcol.value(''data(/xmltables/xmltable[@name])[1]'',''nvarchar(max)'') from xmlt where id=2

------------------------------------------函数使用----------------
--query()、exist()
select pk, xcol.query(''/root/dbo.xmltable/name'') from docs
select xcol.query(''/root/dbo.xmltable/name'') from docs
where xcol.exist (''/root/dbo.xmltable'') = 1
--modify()
update docs set xcol.modify(''
insert
<section num="2">
<heading>background</heading>
</section>
after (/doc/section[@num=1])[1]'')
--value()
select xcol.value(''data((/root/dbo.xmltable//name))[2]'',''nvarchar(max)'') from docs
where pk=3
--nodes()
select nref.value(''@name'', ''varchar(max)'') lastname
from xmlt cross apply xcol.nodes(''/xmltables/xmltable'') as r(nref)
--query()、value()、exist() 和nodes()，modify()
select cast(t.c as xml).query(''/root/dbo.xmltable/name'')
from openrowset(bulk ''e:\xml.xml'',single_blob) t(c)