–用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)