SQL Server 对XML数据类型的SQL语句总结
--创建XMLTablecreate 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 intdeclare @r varchar(200)set @i=1while @i<1000000begininsert into [XML].[dbo].[XMLTable] ([XMLCol])--select * from [xml]select * from openrowset(BULK 'G:\NXDData\xmldata\xmldata\00\00\00\00000000.xml', SINGLE_CLOB) as xset @i=@i+1end------------------------------------------------循环插入数据declare @x intdeclare @y intdeclare @count intset @x = 0while @x < 100begin 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 endset @x = @x + 1end------------------------------------------------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 XMLTableselect * from xmlTable where XMLCol.value('(/authorinfo/personinfo/firstname)[1]','nvarchar(50)') ='维春'select XMLCol.query('(/dd/a[@id>2])[1]') as xm from XMLTable