SQL Server's summary of SQL statements for XML Data Types

-- Create xmltablecreate table xmltable (ID int identity (1, 1) primary key, xmlcol XML); go tables insert XML data into a single insert into [XML]. [DBO]. [xmltable] ([xmlcol]) Select * From OpenRowSet (bulk 'G: \ document \ xmldocument \ x3.xml', single_clob) as x forward insert a single XML data record 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 tables query xmltable data table select * From xmltable tables insert 1 million data records cyclically in declare @ I intdeclare @ r varchar (200) set @ I = 1 while @ I <1000000 begininsert into [XML]. [DBO]. [xmltable] ([xmlcol]) -- select * from [XML] Select * From OpenRowSet (bulk 'G: \ nxddata \ xmldata \ 00 \ 00 \ 00 \ 00000000. xml', single_clob) as xset @ I = @ I + 1end users cyclically insert data declare @ x intdeclare @ Y intdeclare @ count intset @ 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 \ 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 primary XML primary index create primary XML index ipxml_xmltable_xmlcol on xmltable (xmlcol ); -- XML Path secondary index create XML index ixml_xmltable_xmlcol_path on xmltable (xmlcol) using XML index ipxml_xmltable_xmlcol for path -- XML Attribute secondary index create XML index ixml_xmltable_xmlcol_property on xmltable (xmlcol) using XML index ipxml_xmltable_xmlcol for property -- create XML index ixml_xmltable_xmlcol_value on xmltable (xmlcol) using XML index ipxml_xmltable_xmlcol for value limit query statement select Top 1000 xmlcol. query ('(/authorinfo/personinfo) [1]') as XM from xmltableselect * From xmltable where xmlcol. value ('(/authorinfo/personinfo/firstname) [1]', 'nvarchar (50) ') = 'yichun' select xmlcol. query ('(/DD/A [@ ID> 2]) [1]') as XM from xmltable