SQLSERVER 2005中使用sql語句對xml文件和其數(shù)據(jù)的進(jìn)行操作(很全面)
--將XML樹作為varchar參數(shù)傳入用
--insert xx select xxx from openxml() 的語法插入數(shù)據(jù)
-----------------------------------導(dǎo)入,導(dǎo)出xml--------------------------
--1導(dǎo)入實例
--單個表
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
-----------------------讀入第二個表數(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ù)據(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é)點,請使用
SELECT xCol.query(''/Xmltables/Xmltable[@Name="1"]'') from xmlt where ID =1
--讀取Name=1 的節(jié)點值,請使用
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
--讀取所有節(jié)點Name
SELECT nref.value(''@Name'', ''varchar(max)'') LastName
FROM xmlt CROSS APPLY xCol.nodes(''/Xmltables/Xmltable'') AS R(nref) where ID=1
--讀取所有節(jié)點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)
相關(guān)文章
SQL2005 學(xué)習(xí)筆記 公用表表達(dá)式(CTE)
公用表表達(dá)式是Sql Server2005新增加的一個非常好用的功能。2009-07-07SQL Server Management Studio Express管理器 沒有導(dǎo)入導(dǎo)出數(shù)據(jù)的向?qū)У慕鉀Q方法
我的SQL2005 Microsoft SQL Server Management Studio Express管理器里,右鍵單擊一個數(shù)據(jù)庫,指向“任務(wù)”,再單擊“導(dǎo)入數(shù)據(jù)”或“導(dǎo)出數(shù)據(jù)”中沒有這個選項,要想實現(xiàn)導(dǎo)入/導(dǎo)出數(shù)據(jù),要怎么辦呢?2011-04-04SQLServer 數(shù)據(jù)集合的交、并、差集運算
SQLServer2005通過intersect,union,except和三個關(guān)鍵字對應(yīng)交、并、差三種集合運算。2009-09-09如何在SQL Server 2005數(shù)據(jù)庫中導(dǎo)入SQL Server 2008的數(shù)據(jù)
在SQL Server 2008中導(dǎo)入SQL Server 2005很方便,高版本是可以向低版本兼容的,那么我們?nèi)绾卧赟QL Server 2005數(shù)據(jù)庫中導(dǎo)入SQL Server 2008的數(shù)據(jù)呢?下面我們來探討下:2014-06-06如何在SQL SERVER 2005存儲過程中,使用循環(huán)語句
本篇文章是對如何在SQL SERVER 2005存儲過程中,使用循環(huán)語句進(jìn)行了詳細(xì)的分析介紹,需要的朋友參考下2013-06-06關(guān)于sqlserver 2005 使用臨時表的問題( Invalid object name #temptb)
最近在利用 SSRS 2005 做報表的時候,調(diào)用帶有臨時表的數(shù)據(jù)源時,系統(tǒng)會報錯,并無法進(jìn)入向?qū)У南乱徊?提示There is an error in the query. Invalid object name #temptb2012-07-07SQL Server 2005 Express 安裝失敗解決辦法
本人重裝vs2005后,sql sever 2005 express卻一直安裝不上,造成寫好的網(wǎng)頁無法運行。多次卸載重裝無果2009-03-03