旷世神医,我欲封天耳根小说零,官场小说排行榜

主頁(yè) > 知識(shí)庫(kù) > SQLSERVER 2005中使用sql語(yǔ)句對(duì)xml文件和其數(shù)據(jù)的進(jìn)行操作(很全面)

SQLSERVER 2005中使用sql語(yǔ)句對(duì)xml文件和其數(shù)據(jù)的進(jìn)行操作(很全面)

--用SQL多條可以將多條數(shù)據(jù)組成一棵XML樹(shù)L一次插入
--將XML樹(shù)作為varchar參數(shù)傳入用
--insert xx select xxx from openxml() 的語(yǔ)法插入數(shù)據(jù)
-----------------------------------導(dǎo)入，導(dǎo)出xml--------------------------

--1導(dǎo)入實(shí)例
--單個(gè)表
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
-----------------------讀入第二個(gè)表數(shù)據(jù)--------------------
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
-----------------------同時(shí)讀入多表數(shù)據(jù)----------------
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> 則插入的數(shù)據(jù)為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數(shù)據(jù)操作------------------
--類型化的XML
CREATE TABLE xmlt(ID INT PRIMARY KEY, xCol XML not null)
--T-sql生成數(shù)據(jù)
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生成數(shù)據(jù)
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 的節(jié)點(diǎn)，請(qǐng)使用
SELECT xCol.query(''/Xmltables/Xmltable[@Name="1"]'') from xmlt where ID =1
--讀取Name=1 的節(jié)點(diǎn)值，請(qǐng)使用
SELECT xCol.query(''/Xmltables/Xmltable[@Name="1"]/text()'') from xmlt where ID =1
--讀取Name=5 的Name 屬性值，請(qǐng)使用
SELECT xCol.query(''data(/Xmltables/Xmltable[@Name])[5]'') from xmlt where ID =1
--讀取所有節(jié)點(diǎn)Name
SELECT nref.value(''@Name'', ''varchar(max)'') LastName
FROM xmlt CROSS APPLY xCol.nodes(''/Xmltables/Xmltable'') AS R(nref) where ID=1
--讀取所有節(jié)點(diǎn)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

------------------------------------------函數(shù)使用----------------
--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)

您可能感興趣的文章:

Sql Server 和 Access 操作數(shù)據(jù)庫(kù)結(jié)構(gòu)Sql語(yǔ)句小結(jié)
六條比較有用的MySQL數(shù)據(jù)庫(kù)操作的SQL語(yǔ)句小結(jié)
用SQL語(yǔ)句添加刪除修改字段、一些表與字段的基本操作、數(shù)據(jù)庫(kù)備份等
sql語(yǔ)句之?dāng)?shù)據(jù)操作介紹
SQL Server 數(shù)據(jù)庫(kù)基本操作語(yǔ)句總結(jié)
淺析SQL數(shù)據(jù)操作語(yǔ)句

標(biāo)簽：齊齊哈爾通化阜陽(yáng) 荊州那曲海北臨滄泰州

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《SQLSERVER 2005中使用sql語(yǔ)句對(duì)xml文件和其數(shù)據(jù)的進(jìn)行操作(很全面)》，本文關(guān)鍵詞 SQLSERVER,2005,中,使用,sql,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。