--创建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(''+cast(@x as nvarchar),2) + N'\' + right(''+cast(@y as nvarchar),2) + N'\00' + right(''+cast(@x as nvarchar),2) + right(''+cast(@y as nvarchar),2)+ right(''+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