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

由于数据库对xml数据直接处理有很多优势，05也对这方面加强了功能。下面是一些实例代码，大家可以参考下。

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

0
0
0
0
0
”;
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”

0
0

”;
declare @idHandle as int ;
EXEC sp_xml_preparedocument @idHandle OUTPUT, @s
insert into Xmlta(Name,Nowtime)
se本文来源gao.dai.ma.com搞@代*码(网$lect * 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”

0
0

”;
— 则插入的数据为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
/*
—空的处理



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,
”
1
2
3
4
5
”)
–dataset生成数据
insert into xmlt values(2,
”<?xml version=”1.0″ encoding=”gb2312″ ?>

11900-1-11
21900-1-22
31900-1-33
”)
–读取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

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)

喜欢 (0)赏

[搞代码]

分享 (0)