浅谈 SQL Server 2000 对 XML 的支持

发表于:2007-05-26来源:作者:点击数: 标签:
SQL Server 2000就已经有了对XML的支持,这种支持主要体现在FOR XML子句和SQLXML上。然而,这些支持并不充分,因为在SQL Server 2000里,归根结底是用关系型的数据表来模拟XML数据,由于关系型数据先天上的缺点,让它模拟表现力和扩展性更强的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();
            }
            }

原文转自:http://www.ltesting.net