SQL Server XML Type field additions and deletions

1. Defining the table structure

Create a new database table Communicateitem in MSSM and define one of the fields Itemcontentxml as XML type

2. Edit the table data and add a row to find that the XML type cannot be entered by the designer.

You need to write SQL updates or inserts.

DECLARE  @xml xmlset @xml = ' <MyHobby>  <MyCode>1</MyCode>  <MyName> Mountain climbing </myname ></MyHobby><MyHobby>  <MyCode>2</MyCode>  <MyName> swimming </MyName>< /myhobby><myhobby>  <MyCode>3</MyCode>  <MyName> Gourmet </myname></ Myhobby> ' UPDATE dbo.communicateitem SET [email protected] WHERE communicateitemid=1;

Add a node before the first <MyHobby> node

UPDATE dbo.communicateitem SET itemcontentxml.modify (' Insert <MyHobby><MyCode>0</MyCode></ Myhobby> as first into (/) ') WHERE communicateitemid=1;

Add <MyName> First </MyName> after <MyCode>0</MyCode>

UPDATE dbo. Communicateitem SET itemcontentxml.modify (' Insert  <MyName> first </MyName> as last into (/myhobby) [1] ') WHERE communicateitemid=1;

Delete the node you just added <MyHobby><MyCode>0</MyCode><MyName> first </MyName></MyHobby>

UPDATE SET Itemcontentxml.modify ('delete  /myhobby[1]')WHERE Communicateitemid=1;

3. Querying XML type node data

  SELECT  * from   communicateitem  ci WHERE ci. Itemcontentxml.exist (' (/myhobby/myname[text () = "First"]) ') =1 and  ci. Itemcontentxml.exist (' (/myhobby/myname[text () = ' mountain climbing ') ') =1

4.c# writing and reading XML types

XML data types were introduced in SQL Server2005 and later in SQL Server, and using XML data types in C # requires specifying the parameter type SqlDbType and the parameter value type needs to be SQLXML, as shown in the following example:

Suppose there is a table A,a table with two fields: ID type Int,data type Xml, I want to insert a row of records into a table in C #:

Static voidInserta (intAidstringcontentxml) {//connstring is a connection string that requires additional definition    using(SqlConnection conn =NewSqlConnection (connstring)) {Conn.        Open (); stringsql ="INSERT into [A] ([id],[content]) VALUES (@id, @content)"; using(SqlCommand comm =NewSqlCommand (SQL, conn)) {            using(XmlTextReader rdr =NewXmlTextReader (Contentxml, XmlNodeType.Document,NULL) {SqlXml SqlXml=NewSqlXml (RDR); SqlParameter Parmid=NewSqlParameter ("@id", aid); SqlParameter parmcontent=NewSqlParameter ("@content", Sqldbtype.xml, sqlXml.Value.Length); Parmcontent.value=sqlXml; Comm.                Parameters.Add (Parmid); Comm.                Parameters.Add (parmcontent); Comm.            ExecuteNonQuery (); }} conn.    Close (); }}

When inserting data, you need to use the SQLXML data type as the parameter value, but the C # data type when you read the XML type data is string. The following example:

stringGetContent (intID) {    stringsql ="SELECT [Content] from [A] WHERE [ID] ="+ID; using(SqlConnection conn =NewSqlConnection (connstring)) {Conn.        Open (); using(SqlCommand comm =NewSqlCommand (Sql,conn)) {            stringXML = (string) Comm.            ExecuteScalar (); returnXML; }    }}

It is important to note that when inserting data, the parameter value type of the XML field is not considered a string, and the encoding error exception is reported directly with string.

Reference Documentation:

　　0.xquery/xpath application

Http://www.cnblogs.com/fuhongwei041/archive/2008/09/23/1297420.html

　1.SQL Server Operations XML series

　　　Http://www.cnblogs.com/leep2007/category/392443.html

2.XML and DataTable parameters for the where in and like implementations of SQL Server parameterized queries

http://www.cnblogs.com/lzrabbit/archive/2012/04/29/2475427.html

3. Flexible use of SQL SERVER for XML PATH

Http://www.cnblogs.com/doubleliang/archive/2011/07/06/2098775.html

4.cross Apply & Outer Apply

http://blog.csdn.net/htl258/article/details/4537421

Queries of 5.SQL XML types

Http://www.cnblogs.com/stevenshi/archive/2009/11/26/1611458.html

6.18 Getting Started with small instances SQL Server XML query

Http://www.cnblogs.com/huyong/archive/2012/01/18/2685599.html

7.SQL Server2008 XML Delete and change

http://blog.csdn.net/szstephenzhou/article/details/8270116

8.SQL Server Operations on XML fields

http://blog.csdn.net/w174504744/article/details/8200329

9.SQL Server XML Application Instance

http://blog.csdn.net/liangck/article/details/3335158

SQL Server XML Type field additions and deletions