On the processing of SQLServer2005 learning notes XML _mssql2005

With regard to XML, it is difficult not to understand the functions provided by SQL Server, but to understand the XML itself, a seemingly simple file format. This article is just a preliminary discussion.
See SQL Server online Help for details:
Theme
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 code as follows:

SELECT
Doc.value (' (/root/row[1]/id/text ()) [1] ', ' int ') RootRowID1,--the value of the first row ID, and converted to int type
Doc.value (' (/root/row[2]/id/text ()) [1] ', ' int ') RootRowID2,--the value of the second row ID, and converted to int type
Doc.value (' (/root/row[1]/name/text ()) [1] ', ' varchar ') RootRowNAME1,--the value of the first line of NAME, and converted to the varchar type
Doc.value (' (/root/row[1]/name/@SEX) [1] ', ' varchar ') rootrowname1sex,--the value of the SEX property in the first row of NAME and convert to varchar type
Doc.query ('/root ') ROOT, all XML content under--root, type XML
Doc.query ('/root/row[1] ') RootRow1,--root the first line of all XML content, type XML
Doc.query ('/root/row[2] ') RootRow2--root the second line of all XML content, type XML
From Visioxml
WHERE 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--there is a SEX property
--and doc.exist ('/root/row[1]/name[not (@SEX)] ') =1 in the first line of name--SE is not present in the first line of name X Property
--and doc.exist ('/root/row[1][not (id/*)] ') =1--The first line does not have an ID field
--and doc.exist ('/root/row[1][(id/*)] ') =1- The first line has an ID field