Sql Server xml 类型字段的增删改查

1.定义表结构

在MSSM中新建数据库表CommunicateItem，定义其中一个字段ItemContentXml 为xml类型

2.编辑表数据，新增一行，发现xml类型不能通过设计器录入数据。

需要写sql更新或插入。

DECLARE  @xml XML
SET @xml='
<MyHobby>
  <MyCode>1</MyCode>
  <MyName>爬山</MyName>
</MyHobby>
<MyHobby>
  <MyCode>2</MyCode>
  <MyName>游泳</MyName>
</MyHobby>
<MyHobby>
  <MyCode>3</MyCode>
  <MyName>美食</MyName>
</MyHobby>
'

UPDATE dbo.CommunicateItem SET ItemContentXml=@xml WHERE CommunicateItemId=1;

　在第一个<MyHobby>节点之前添加一个节点

UPDATE dbo.CommunicateItem SET ItemContentXml.modify('insert <MyHobby><MyCode>0</MyCode></MyHobby> as first into(/)')
 WHERE CommunicateItemId=1;

在<MyCode>0</MyCode>之后添加 <MyName>第一个</MyName>

 UPDATE dbo.CommunicateItem SET ItemContentXml.modify('insert  <MyName>第一个</MyName> as last into(/MyHobby)[1]')
 WHERE CommunicateItemId=1;

删除刚添加的节点 <MyHobby><MyCode>0</MyCode><MyName>第一个</MyName></MyHobby>

UPDATE dbo.CommunicateConfig SET ItemContentXml.modify('delete  /MyHobby[1]')
WHERE CommunicateItemId=1;

 

3.查询XML类型节点数据

  SELECT  *   FROM CommunicateItem  ci WHERE ci.ItemContentXml.exist('(/MyHobby/MyName[text()="第一个"])')=1 
 AND  ci.ItemContentXml.exist('(/MyHobby/MyName[text()="爬山"])')=1

4.C#写入和读取Xml类型

 

在sql server2005以及之后的sql server中引入了Xml数据类型，在C#中使用Xml数据类型需要指定参数类型为SqlDbType，参数值类型需要用SqlXml，如下示例：

 

假定有一种表A，A表有两个字段：ID 类型 int，Data 类型 Xml，我要用C#往表中插入一行记录：

 

 

static void InsertA(int aid, string contentXml)
{
//ConnString是连接字符串，需要额外定义
    using (SqlConnection conn = new SqlConnection(ConnString))
    {
        conn.Open();
        string sql = "INSERT INTO [A] ([ID],[Content])VALUES(@id,@content)";
        using (SqlCommand comm = new SqlCommand(sql, conn))
        {
            using (XmlTextReader rdr = new XmlTextReader(contentXml, XmlNodeType.Document, null))
            {
                SqlXml sqlXml = new SqlXml(rdr);
 
                SqlParameter parmID = new SqlParameter("@id", aid);
                SqlParameter parmContent = new SqlParameter("@content", SqlDbType.Xml, sqlXml.Value.Length);
                parmContent.Value = sqlXml;
 
                comm.Parameters.Add(parmID);
                comm.Parameters.Add(parmContent);
                comm.ExecuteNonQuery();
            }
        }
        conn.Close();
    }
}

 

插入数据时需要用SqlXml数据类型作为参数值，但读出Xml类型数据时的C#数据类型是string。如下示例：

string GetContent(int id)
{
    string sql = "SELECT [Content] FROM [A] WHERE [ID] = " + id;
 
    using (SqlConnection conn = new SqlConnection(ConnString))
    {
        conn.Open();
        using (SqlCommand comm = new SqlCommand(sql,conn))
        {
            string xml = (string)comm.ExecuteScalar();
            return xml;
        }
    }
}

需要注意的是，在插入数据时Xml字段的参数值类型不可以为string，直接用string会报编码错误异常。

 

参考文档：

　　0.XQuery/XPath应用 

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

　　1.SQL Server操作XML系列

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

　　2.Sql Server参数化查询之where in和like实现之xml和DataTable传参

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

　　3.灵活运用 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　

　　5.SQL XML类型的查询

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

　　6.18个小实例入门SQLServer XML查询

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

　　7.SQL Server2008 XML增删改查

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

　　8.SQL Server对Xml字段的操作

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

　　9.SQL Server XML应用实例

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