SQL Server 2005 and XML instance tutorial

Description
Query () method (XML data type)
This method is used to query the XML instance.
Value () method (XML data type)
This method is used to retrieve the value of the SQL type from an XML instance.
exist () method (XML data type)
This method is used to determine whether the query returns non-null results.
Modify () method (XML data type)
Use this method to specify an XML DML statement to perform an update.
Nodes () method (XML data type)
This method is used to split the XML into multiple lines to propagate parts of the XML document to the rowset.

Less gossip, first create a datasheet that contains the XML type, and then create an XML file that loads the contents of the XML file into the data table on the server side.
Copy code code as follows:
CREATE TABLE Visioxml
(
ID INT,
Doc XML
);
Go

Create a file named Xxx.xml, which reads
/*
<ROOT>
<ROW>
<ID>1</ID>
<name sex= "MALE" >WBQ</NAME>
</ROW>
<ROW>
<ID>2</ID>
<name sex= "FEMALE" >CZH</NAME>
</ROW>
</ROOT>
*/
INSERT into Visioxml (Id,doc)
SELECT 4,* from OPENROWSET (BULK ' e:xxx.xml ', Single_blob) as X;

--The following is the use of value () and query ()
--select * from Visioxml WHERE id=4
Copy code:
SELECT
Doc.value (' (/root /row[1]/id/text ()) [1] ', ' int ') RootRowID1,--the value of the first row ID, and converted to type int
Doc.value (' (/root/row[2]/id/text ()) [1] ', ' int ') RootRowID2,--the value of the second row ID, and converted to type int
Doc.value (' (/root/row[1]/name/text ()) [1] ', ' varchar ') RootRowNAME1,--First row N AME, and is converted to the VARCHAR type
Doc.value (' (/root/row[1]/name/@SEX) [1] ', ' VARCHAR ') rootrowname1sex,--the NAME of the first row of the SEX property Value and converts to the VARCHAR type
doc.query ('/root ') ROOT, all XML content under--root, type XML
Doc.query ('/root/row[1] ') RootRow1,--roo T the first line of all XML content, the type is XML
Doc.query ('/root/row[2] ') RootRow2--root the second row of all the XML content, the type is XML
from Visioxml
whe RE id=4

--The following is the use of the exist () function in two environments
Copy code code as follows:
SELECT
Doc.exist ('/root/row[1]/name[(@SEX cast as xs:string?) = Xs:string ("MALE")] Row1eqstringmale,
Doc.exist ('/root/row[1]/name[(@SEX cast as xs:string?) = ' MALE '] ' Row1eqmale,
Doc.exist ('/root/row[1]/id[(text () [1] cast as xs:float?) = Xs:float (1)] ') ROW1EQFLOAT1,
Doc.exist ('/root/row[2]/id[(text () [1] cast as xs:float?) = 2] ') row1eq1
From Visioxml
WHERE id=4

Copy code code as follows:
SELECT Id,doc
From Visioxml
WHERE id=4
and Doc.exist ('/root/row[1]/name[(@SEX)] ') =1--SEX attribute exists in the first line of NAME
--and doc.exist ('/root/row[1]/name[not (@SEX)] ') =1--No SEX attribute exists in the first line of NAME
--and doc.exist ('/root/row[1][not (id/*)] ') =1--ID field does not exist in the first line
--and doc.exist ('/root/row[1][(id/*)] ') =1--The first row has an ID field