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