SQLSERVER 2005中使用sql語句對xml文件和其數據的進行操作(很全面)

2020-10-30 19:10:15

字體：大中小

來源：轉載

供稿：網友

--用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 的亚洲香蕉成人av网站在线观看_欧美精品成人91久久久久久久_久久久久久久久久久亚洲_热久久视久久精品18亚洲精品_国产精自产拍久久久久久_亚洲色图国产精品_91精品国产网站_中文字幕欧美日韩精品_国产精品久久久久久亚洲调教_国产精品久久一区_性夜试看影院91社区_97在线观看视频国产_68精品久久久久久欧美_欧美精品在线观看_国产精品一区二区久久精品_欧美老女人bb