SQL Server 对XML数据类型的SQL语句总结
时间：2021-07-01 10:21:17
帮助过：9人阅读
创建XMLTable
create table XMLTable(Id
int IDENTITY (
1,
1)
primary key, XMLCol xml);
go
----------------------------------------------------------------------------------
--插入XML数据单条
insert into [XML].
[dbo].
[XMLTable]
([XMLCol])
select * from
openrowset(
BULK ‘G:\Document\XMLDocument\x3.xml‘,SINGLE_CLOB)
as x
----------------------------------------------------------------------------------
--插入XML数据单条
DECLARE @s varchar(
100)
SET @s = ‘<Cust><Fname>Andrew</Fname><Lname>Fuller</Lname></Cust>‘
INSERT INTO [XML].
[dbo].
[XMLTable]
([Id],
[XMLCol])
VALUES(
3,
cast(
@s as xml))
GO
----------------------------------------------------------------------------------
--查询XMLTable数据表
select * from XMLTable
----------------------------------------------------------------------------------
--循环插入100万条数据
declare @i int
declare @r varchar(
200)
set @i=1
while @i<1000000
begin
insert into [XML].
[dbo].
[XMLTable]
([XMLCol])
--select * from [xml]
select * from
openrowset(
BULK ‘G:\NXDData\xmldata\xmldata\00\00\00\00000000.xml‘, SINGLE_CLOB)
as x
set @i=@i+1
end
----------------------------------------------------------------------------------
--循环插入数据
declare @x int
declare @y int
declare @count int
set @x = 0
while @x < 100
begin
set @y = 0
while @y < 100
begin
set @count = 0
while @count < 100
begin
declare @path nvarchar(
200)
set @path = N
‘insert into [XML].[dbo].[XML]([XML])select * from openrowset(bulk ‘‘G:\NXDData\xmldata\xmldata\00\‘ + right(
‘0‘+cast(
@x as nvarchar),
2)
+ N
‘\‘ + right(
‘0‘+cast(
@y as nvarchar),
2)
+ N
‘\00‘ + right(
‘0‘+cast(
@x as nvarchar),
2)
+ right(
‘0‘+cast(
@y as nvarchar),
2)
+ right(
‘0‘+cast(
@count as nvarchar),
2)
+ N
‘.xml‘‘,SINGLE_CLOB) as x‘;
EXEC sp_executesql
@path
set @count = @count + 1
end
set @y = @y + 1
end
set @x = @x + 1
end
----------------------------------------------------------------------------------
--XML主索引
create primary xml
index IPXML_XMLTable_XMLCol
on XMLTable(XMLCol);
--XML路径辅助索引
create xml
index IXML_XMLTable_XMLCol_Path
on XMLTable(XMLCol)
using xml index IPXML_XMLTable_XMLCol
for path
--XML属性辅助索引
create xml
index IXML_XMLTable_XMLCol_Property
on XMLTable(XMLCol)
using xml index IPXML_XMLTable_XMLCol
for Property
--XML内容辅助索引
create xml
index IXML_XMLTable_XMLCol_value
on XMLTable(XMLCol)
using xml index IPXML_XMLTable_XMLCol
for value
----------------------------------------------------------------------------------
--查询语句
select TOP 1000 XMLCol.query(
‘(/authorinfo/personinfo)[1]‘)
as xm
from XMLTable
select * from xmlTable
where XMLCol.value(
‘(/authorinfo/personinfo/firstname)[1]‘,
‘nvarchar(50)‘)
=‘维春‘
select XMLCol.query(
‘(/dd/a[@id>2])[1]‘)
as xm
from XMLTable
SQL Server 对XML数据类型的SQL语句总结
标签：varchar color while font single bsp log begin tab
