SQL Server 2005对XML的支持

SQL Server 2000对XML的支持

    SQL Server 2000就已经有了对XML的支持，这种支持主要体现在FOR XML子句和SQLXML上。然而，这些支持并不充分，因为在SQL Server 2000里，归根结底是用关系型的数据表来模拟XML数据，由于关系型数据先天上的缺点，让它模拟表现力和扩展性更强的XML数据必然模拟得不伦不类，要实现具有一定格式的XML代码，往往需要写很复杂的SQL语句。此外，由于SQL Server 2000发布得比较早，它对XPath的支持也不全面，更不要说现在的XQuery了。因此，在真实项目中，一旦遇到比较复杂的XML，往往很少会使用FOR XML子句，而是直接将XML代码保存在数据表的text类型字段中。然而，text字段是不会对XML代码是否完整以及是否符合某个Schema的格式而进行验证的。于是，采用得比较多的办法是用对XML支持比较强的语言在客户端对XML进行操作，然后更新到数据库中。但是，对保存在text字段中的XML代码进行查询的效率就很难保证了。

XML数据类型

    在SQL Server 2005中，对XML的支持得到了很好的加强。最关键的一点就是，微软在SQL Server 2005中提供了一个新的数据类型，即XML数据类型，从此告别了用关系型数据来模拟XML数据的历史。因此，以后XML数据可以直接保存到数据表的一个字段中了。

    XML数据类型是一用专门用于保存和操作XML的数据类型。它是以BLOB的二进制形式保存的，一个XML类型字段可以保存2GB的XML代码，层次可达128层。此外，XML数据类型还有检验XML数据完整性的功能，比以前用text类型保存XML数据方便得多。

    如下代码就创建了一个带有XML类型字段xCol的数据表docs，并且为该字段添加了一个“书籍作者的姓和名不能相等”的用自定义函数实现的约束，最后还插入了一条记录。

CREATE FUNCTION udf_Check_Names (@xmlData XML)
RETURNS INT AS
BEGIN
RETURN (SELECT @xmlData.exist('/book/author[first-name = last-name]'))
END
GO

CREATE TABLE docs (pk INT PRIMARY KEY,
xCol XML NOT NULL CONSTRAINT CK_name CHECK (dbo.udf_Check_Names(xCol) = 0))
GO

INSERT INTO docs VALUES (1,
'<book genre="security" publicationdate="2002" ISBN="0-7356-1588-2">
<title>Writing Secure Code</title>
<author>
<first-name>Michael</first-name>
<last-name>Howard</last-name>
</author>
<author>
<first-name>David</first-name>
<last-name>leBlanc</last-name>
</author>
<price>33.99</price>
</book>')

XML字段与Schema Collection的绑定

    在SQL Server 2005中，XML类型的字段是可以和XML Schema绑定的。Schema被放在一个叫Schema Collection的集合里，一个Collection可以放多个Schema，而XML字段是直接和Collection绑定的，XML字段的值只要和Collection中的任何一个Schema匹配就可以了。

    与Collection绑定的XML字段被称为Typed XML类型的字段，而没有与Collection绑定的XML字段则被称为Untyped XML类型的字段。对于Typed XML来说，绑定方式还可以有DOCUMENT和CONTENT两种。如果是DOCUMENT，表示字段内的数据必须是一个完成的XML文档，最顶层元素也只能有一个；而如果是CONTENT，则表示字段内的数据只需要是一个片断即可，而最顶层元素也可以有多个。

    如下代码创建了一个名为myCollection的Schema Collection，然后创建了一个新的数据表XmlCatalog，把它的document字段和myCollection匹配。这样一来，插入到document字段的XML数据就必须与myCollection中的Schema相匹配，否则SQL Server 2005将会拒绝插入。

CREATE XML SCHEMA COLLECTION myCollection AS
'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns="http://myBooks"
elementFormDefault="qualified"
targetNamespace="http://myBooks">
<xsd:element name="bookstore" type="bookstoreType" />
<xsd:complexType name="bookstoreType">
<xsd:sequence maxOccurs="unbounded">
<xsd:element name="book" type="bookType" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="bookType">
<xsd:sequence>
<xsd:element name="title" type="xsd:string" />
<xsd:element name="author" type="authorName" />
<xsd:element name="price" type="xsd:decimal" />
</xsd:sequence>
<xsd:attribute name="genre" type="xsd:string" />
<xsd:attribute name="publicationdate" type="xsd:string" />
<xsd:attribute name="ISBN" type="xsd:string" />
</xsd:complexType>
<xsd:complexType name="authorName">
<xsd:sequence>
<xsd:element name="first-name" type="xsd:string" />
<xsd:element name="last-name" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:schema>'

CREATE TABLE XmlCatalog (
ID INT PRIMARY KEY,
Document XML(CONTENT myCollection))

查询和更新

    XML类型提供了五个用于查找和更新的方法，分别是query，exist，value，nodes和modify方法。调用这五个方法时必须全部小写（很奇怪，SQL Server 2005在别的地方都不区分大小写），而且全部都使用XQuery语句来定位，定位的XQuery语句将以字符串的形式作为方法的第一个参数。

    query方法用于从XML实例中提取XML片断。如：

SELECT pk, xCol.query('/doc[@id = 123]//section')
FROM docs

    exist方法用于确定 XML 实例中是否存在某一元素，如果存在则返回1，否则返回0。如：

SELECT xCol.query('/doc[@id = 123]//section')
FROM docs
WHERE xCol.exist ('/doc[@id = 123]') = 1

    value方法用于从XML实例中提取一个值。由于value方法只能返回一个值，因此它执行的XQuery语句必须要能确定到一个值上，则否就会报错。此外，value方法的第二个参数将确定返回值的类型。如：

SELECT xCol.value('(/doc//section[@num = 3]/title)[1]', 'nvarchar(max)')
FROM docs

    nodes方法用于从XML实例片断产生一个新的XML实例。nodes方法和query方法的区别在于，query方法返回的XML片断是作为字符串返回的，而nodes返回的XML片断则是XML类型的，然后程序还可以对这个返回的XML类型进行进一步的操作。nodes的使用比较复杂，往往要与CROSS APPLY连用。如：

SELECT nref.query('.') LastName
FROM docs CROSS APPLY xCol.nodes('//first-name') AS R(nref)

    modify方法顾名思义就是用于更新了。如：

UPDATE docs SET xCol.modify(
'insert
<section num="2">
<title>Background</title>
</section>
after (/doc//section[@num=1])[1]')
where xCol.exist('/doc') = 1
select * from docs

XML索引

    相比起关系型数据，XML有着各种各样的优点，但有个最大的缺陷就是它的效率。因为关系型数据文件中，数据的字段名只需出现一次即可，而XML数据文件中，元素名将返复出现，这必须会影响到查询的效率。为了进可能的提高XML的查询效率，SQL Server 2005为XML类型提供了索引功能。

    XML索引分为主索引和二级索引。在对XML类型的字段创建主索引时，SQL Server 2005并不是对XML数据本身进行索引，而是对XML数据的元素名、值、属性和路径进行索引。如：

CREATE PRIMARY XML INDEX idx_xCol on docs (xCol)

    在为XML字段创建了主索引后，还可以分别为XML的值、属性和路径创建二级索引，以提高对这些内容查询的效率。如下三句代码分别创建了这三种索引：

CREATE XML INDEX idx_xCol_Value on docs (xCol)
USING XML INDEX idx_xCol FOR VALUE

CREATE XML INDEX idx_xCol_Property on docs (xCol)
USING XML INDEX idx_xCol FOR PROPERTY

CREATE XML INDEX idx_xCol_Path on docs (xCol)
USING XML INDEX idx_xCol FOR PATH

FOR XML子句和XML数据类型

    在SQL Server 2005中，FOR XML子句也有了对XML数据类型的支持。一般情况下，FOR XML子句返回的XML数据也和query方法一样，是以字符串的形式返回的。但是，如果在FOR XML子句后加上TYPE选项的话，就会返回XML类型的XML数据了，这时就可以对返回的XML数据进行操作。如：

SELECT (SELECT * FROM Customers FOR XML AUTO, TYPE).query(
'<doc>{
for $c in /Customers
return
<Person name="{data($c/@ContactName)}"/>
}</doc>')

.NET Framework 2.0和XML数据类型

    XML数据类型是SQL Server 2005最新提出的数据类型，因此在.NET Framework 1.1中是不支持的，但在.NET Framework 2.0中就有了支持。System.Data.SqlTypes命名空间中有一个叫SqlXml的类专门对应于SQL Server 2005中的XML数据类型。通过SqlXml类就可以操作SQL Server 2005的XML数据类型。如：

using System;
using System.Xml;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;

class xmldtADONETAccess
{
    public static void ReadXmlDataType()
    {
        // in-proc connection to server
        SqlConnection conn = new SqlConnection("server=localhost;database=XMLTest;Integrated Security=SSPI");
        conn.Open();

        // prepare query to select xml data
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = "SELECT xCol.query('//section') FROM docs WHERE xCol.exist ('/doc[@id = 123]') = 1";

        // execute query and retrieve incoming data
        SqlDataReader r = cmd.ExecuteReader();
        r.Read();

        // access XML data type field in rowset
        SqlXml xml = r.GetSqlXml(0);
        new XmlTextWriter(Console.Out).WriteNode(xml.CreateReader(), true);
        conn.Close();
    }
    public static void WriteXmlDataType()
    {
        // connection to server
        SqlConnection conn = new SqlConnection("server=localhost;database=XMLTest;Integrated Security=SSPI");
        conn.Open();

        // update XML column at the server
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = "UPDATE docs SET xCol=@x WHERE pk=1";

        // set value of XML parameter
        SqlParameter p = cmd.Parameters.Add("@x", SqlDbType.Xml);
        p.Value = new SqlXml(new XmlTextReader("<hello/>",
        XmlNodeType.Document, null));

        // execute update and close connection
        cmd.ExecuteNonQuery();
        conn.Close();
    }
}