SQL Server Summary of SQL statements for XML data types

--Create XMLTableCreate TableXMLTable (Idint  IDENTITY(1,1)Primary Key, xmlcol XML); Go   ------------------------------------------------------------------------------------Insert XML Data SingleInsert  into [XML].[dbo].[XMLTable]           ([Xmlcol])            Select *  from OpenRowset(BULK 'G:\Document\XMLDocument\x3.xml', Single_clob) asx------------------------------------------------------------------------------------Insert XML Data SingleDECLARE  @s varchar( -)SET @s = '<Cust><Fname>Andrew</Fname><Lname>Fuller</Lname></Cust>' INSERT  into [XML].[dbo].[XMLTable]           ([Id],[Xmlcol])VALUES(3,cast(@s  asXML))GO------------------------------------------------------------------------------------querying xmltable Data SheetsSelect *  fromXMLTable------------------------------------------------------------------------------------Loop Insert 1 million dataDeclare @i intDeclare @r varchar( $)Set @i=1 while @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) asxSet @i=@i+1End------------------------------------------------------------------------------------Loop Insert DataDeclare @x intDeclare @y intDeclare @count intSet @x = 0 while @x <  -begin     Set @y = 0      while @y <  -     begin         Set @count = 0            while @count <  -             begin             Declare @path nvarchar( $)             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'; EXECsp_executesql@path             Set  @count = @count + 1              End     Set @y = @y + 1     EndSet @x = @x + 1End------------------------------------------------------------------------------------XML Primary IndexCreate PrimaryXmlIndexIpxml_xmltable_xmlcol onXMLTable (Xmlcol); --XML Path Secondary indexCreateXmlIndexIxml_xmltable_xmlcol_path onXMLTable (xmlcol) using XMLIndexIpxml_xmltable_xmlcol forPath--XML attribute Secondary indexCreateXmlIndexIxml_xmltable_xmlcol_property onXMLTable (xmlcol) using XMLIndexIpxml_xmltable_xmlcol for Property--XML Content Secondary indexCreateXmlIndexIxml_xmltable_xmlcol_value onXMLTable (xmlcol) using XMLIndexIpxml_xmltable_xmlcol forvalue------------------------------------------------------------------------------------Query StatementsSelect TOP  +Xmlcol.query ('(/authorinfo/personinfo) [1]') asXm fromXMLTableSelect *  fromXmlTablewhereXmlcol.value ('(/authorinfo/personinfo/firstname) [1]','nvarchar (+)')='Victoria Spring'SelectXmlcol.query ('(/dd/a[@id >2]) [1]') asXm fromXMLTable

SQL Server Summary of SQL statements for XML data types