SQl Xml和C# Xml数据的一点操作总结

SQl Xml和C# Xml数据的一点操作总结
在此申明Xml是InfoSet数据不是字符串，所以在此强烈反对用string拼接xml。数据库可以存放xml类型数据，那么该数据的具体操作又如何了。
1.首先建立一张含有xml数据类型的表
CREATE TABLE [dbo].[TestXml](
 [ID] [bigint] IDENTITY(1,1) NOT NULL,
 [Message] [xml] NULL,
 CONSTRAINT [PK_TestXml] PRIMARY KEY CLUSTERED
(
 [ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
2.创建相应的DataSet
注意默认Message是String类型，为此我们要把它改为XDocument或则XmlDocument，如：
 [global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
            public XDocument Message {
                get {
                    try {
                        string xmlstr=  ((string)(this[this.tableTestXml.MessageColumn]));
                       return  XDocument.Parse(xmlstr);
                    }
                    catch (global::System.InvalidCastException e) {
                        throw new global::System.Data.StrongTypingException("表“TestXml”中列“Message”的值为 DBNull。", e);
                    }
                }
                set {
                    this[this.tableTestXml.MessageColumn] = value;
                }
            }
3.添加记录
  Database Db = DatabaseFactory.CreateDatabase("TestProvider");
            TestDataSet ds = new TestDataSet();
            DBCommandWrapper dbCommand = Db.GetStoredProcCommandWrapper("TextXml_I");
            XNamespace ns = string.Empty;
            XDocument doc = new XDocument(
                new XElement(ns + "BookStore",
                    new XElement(ns + "Book",
                        new XAttribute("PublicationDate", DateTime.Now.ToShortDateString()),
                        new XAttribute("ISBN", "123-456-7890"),
                        new XElement(ns + "Title", "Asp.Net 2.0 Book"),
                       new XElement(ns + "Price", "12"),
                       new XElement(ns + "Author",
                           new XElement(ns + "First-Name", "ma"),
                           new XElement(ns + "Last-Name", "jiang")
                           )
                           )
                           )
                );

            dbCommand.AddInParameter("Message", DbType.Xml, doc.ToString());
            int result = Db.ExecuteNonQuery(dbCommand);
其中TextXml_I是相应的存储过程CREATE PROC [dbo].[TextXml_I]
(
@Message XML
)
AS
INSERT INTO [TestXml]
           ([Message])
     VALUES
           (@Message)

Database、DatabaseFactory、DBCommandWrapper类是我自己开发的一个框架，详细请看http://blog.csdn.net/dz45693/archive/2010/04/22/5514224.aspx
4.查看数据
相应的html如下
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
    <Columns>
    <asp:BoundField HeaderText="ID" DataField="ID" />
    <asp:TemplateField HeaderText="Message">
    <ItemTemplate>
    <asp:Panel runat="server" ID="entryPanel">
        </asp:Panel>
    </ItemTemplate>
    </asp:TemplateField>
    </Columns>
    </asp:GridView>
由于xml呈现的形式是多变的为此我们用样式来控制其呈现样式，创建XSLMessage.xslt样式文件
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl">
  <xsl:template match="/BookStore">

    <table>
      <tr>
        <td>
          Title:<xsl:value-of select="Book/Title"/>
        </td>
      </tr>
      <tr>
        <td>
          Price:<xsl:value-of select="Book/Price"/>
        </td>
      </tr>
      <tr>
        <td>
          Author:<xsl:value-of select="Book/Author/First-Name"/>--<xsl:value-of select="Book/Author/Last-Name"/>
        </td>
      </tr>
      <tr>
        <td>
          ISBN:<xsl:value-of select="Book/@ISBN"/>
        </td>
      </tr>
        <tr>
          <td>
            PublicationDate:<xsl:value-of select="Book/@PublicationDate"/>
          </td>
        </tr>
      </table>
  </xsl:template>
</xsl:stylesheet>
查询代码
 Database Db = DatabaseFactory.CreateDatabase("TestProvider");
            TestDataSet ds = new TestDataSet();
            DBCommandWrapper dbCommand = Db.GetSqlStringCommandWrapper("SELECT * FROM dbo.TestXml");
            Db.LoadDataSet(dbCommand, ds, ds.TestXml.TableName);
            this.GridView1.DataSource = ds;
            this.GridView1.DataBind();
            XslCompiledTransform xslt = new XslCompiledTransform();
            xslt.Load(Server.MapPath("XSLMessage.xslt"));

            for (int i = 0; i < ds.TestXml.Rows.Count; i++)
            {
                GridViewRow row = this.GridView1.Rows[i];
                Panel entryPanel = row.FindControl("entryPanel") as Panel;
                StringWriter sw = new StringWriter();
                xslt.Transform(ds.TestXml[i].Message.GetXmlDocument(), null, sw);
                entryPanel.Controls.Add(new LiteralControl(sw.ToString().Replace("__LINEBREAK__", "<br />")));
            }

注意我在此主张用XDocument，因为它使用很方便；但是很多类还是只支持XmlDocument，为此我们需要一个方法吧XDocument转化为XmDocument，我采用3。5框架的扩展方法方式实现该功能。

 public static class StaticClass
    {

  public static XmlDocument GetXmlDocument(this XDocument doc)
        {
            XmlDocument newdoc = new XmlDocument();
             newdoc.LoadXml(doc.ToString());
             return newdoc;
        }

}
整过查询结果如图：

同样要实现此效果我们还可以依赖xml的查询，请参考http://blog.csdn.net/dz45693/archive/2010/03/30/5434150.aspx