SQL Server XQuery and XML.docx

1、SQL Server XQuery and XMLSQL Server 2005 XQuery and XML-DMLAn Overview of XML Support in SQL Server 2005Ever since the mid 90s, as XML has evolved to become the optimum way to share, transport and persist data, developers have sought efficient ways to store, manipulate and generally take advantage o

2、f its capabilities. Increasingly fast and easy-to-use XML parsers have been developed, transport protocols such as SOAP have been used to implement Web Services around XML, and many applications, tools and programming libraries now include features to import and export their data as XML.However, the

3、 one area that has seen less development, particularly in the Microsoft world, is the implementation of efficient and robust technologies for persisting XML. XML is basically just text, and the most common persistence format is as a disk file in some standard text-based format such as ANSI, Unicode,

4、 etc. This is generally fine for single-user applications, but does prompt some serious questions when used in a server-based and/or multi-user environment.This and the two subsequent articles look at how the latest version of Microsofts enterprise-level database, SQL Server 2005, now offers great s

5、upport for and close integration with XML as a data persistence format. This includes new ways to validate, store and query XML documents that are stored within the database. SQL Server 2005 provides native support for XML that can vastly improve application performance, while supporting robust and

6、safe multi-user access to the data contained within the XML documents.The topics well be covering in this article are: A brief overview of the way that SQL Server 2005 stores XML documents and schemas How SQL Server 2005 provides support for querying and manipulating XML documents A simple test appl

7、ication that allows you to experiment with XQueryIn two subsequent articles, well see some techniques for improving the performance of applications that work with XML documents, as well as some examples of the different ways you can use XQuery: Extracting data from xml columns, using parameter with

8、XQuery and combining XQuery and XSL-T Updating the contents of xml columns, and using XQuery in a managed code stored procedureAn Overview of XML Support in SQL Server 2005SQL Server 2005 adds a raft of new features to support XML data storage and manipulation. These features make it easier to persi

9、st your XML documents within the database, while providing increased performance over the traditional techniques. Well be comparing these traditional techniques, and seeing how you can improve the performance of your applications, in Parts 2 and 3. For the moment, however, well briefly explore the n

10、ew features in SQL Server 2005. These include: A dedicated data type named xml that can be used to store XML documents or fragments of XML The ability to register XML schemas with SQL Server 2005, and store schema information within the database Automatic validation of XML documents when a schema is

11、 present; and automatic shredding of the XML data to support efficient querying and updating of the content An implementation of a subset of the W3C XQuery language and XML-DML to provide this querying and update facility Support for hosting the .NET Common Language Runtime (CLR) within SQL Server,

12、which allows stored procedures that manipulate XML documents to be written in managed codeYoull see how all these features come into play throughout these three articles, and how they open up new techniques for working with XML documents and XML data.XML Schemas and the W3C Infoset ModelIn recent ye

13、ars, its become increasingly obvious that the major uses of XML are as a way of storing both rowset (single table) and hierarchical (multiple-table) data, rather than unstructured information such as newspaper articles. For example, a common use of Web Services in .NET applications is to expose data

14、 that represents a DataSet in a format that allows discovery and transmission across HTTP networks such as the Internet. The DataSet may contain a single table, or multiple tables that are related through primary and foreign keys, and the XML data can be used to completely reconstruct that DataSet o

15、n the client. To specify the data type for an element or an attribute in an XML document you use a schema. This indicates, for example, whether a value such as 42(which is stored as a text string within the XML) represents either a character string or a numeric value. The client can then reconstruct

16、 the data stored in the XML document so that it is accessible as the appropriate data types. This is at the heart of the recent moves towards the XML Information Set (Infoset) model, which effectively considers an XML document as one or more typed rowsets.For details of the W3C Infoset recommendatio

17、n, see http:/www.w3.org/TR/xml-infoset/This means that you must expose an XML Schema (or the relevant schema information) for every XML document or fragment in order to take advantage of the Infoset model and data-typing of the XML content. SQL Server 2005 makes this easy by providing a schema repos

18、itory that you can use to store XML schemas, and it will automatically use the appropriate schema to validate and store XML data. The XML Schema RepositorySchemas are added to a database by executing the CREATEXMLSCHEMACOLLECTION statement, for example: CREATE XML SCHEMA COLLECTION MyNewSchemaCol .

19、schema content .You can add multiple schemas in one go by concatenating them together, use the ALTERXMLSCHEMACOLLECTION statement to add or remove individual schemas in a collection, and remove the collection using the DROPXMLSCHEMACOLLECTION statement. See the SQL Server help files for more details

20、.The name you assign to the collection (MyNewSchemaCol in the code above) is used in the ALTER and DROP statements, and is displayed in SQL Server Management Studio. However, you should include a targetNamespace attribute in the opening element to identify each schema in the collection, for example:

21、 .Then you link your XML documents to the appropriate schema by specifying this namespace: .You can use SQL Server Management Studio to view and manage schemas and schema collections. For example, Figure 1 shows the schema collections in the AdventureWorks sample database that you can download and i

22、nstall in SQL Server 2005. Figure 1 - The schema collections in the AdventureWorks sample databaseThe New xml Native Data TypeSQL Server 2005 supports a new native data type named xml that you use in exactly the same way as any other built-in data type. You can use it to define a column type for a t

23、able, as a parameter or variable in a stored procedure, and anywhere else you would use built-in types such as nvarchar, int, etc. The xml type can store either a complete XML document, or a fragment of XML, as long as it is well-formed (you cannot use an xml type to store XML that is not well-forme

24、d). Typed and Un-typed xml ColumnsWhen you provide a schema for the XML documents you will store, you can create a typedxml column in a table. You specify the name of the schema collection that contains the schema you want to apply to that column, for example: CREATE TABLE MyTable(MyKey int, MyXml x

25、ml(MyNewSchemaCol)Now the content of the XML document you insert into that column will be shredded automatically into its individual data items, and SQL Server will store these internally in the most efficient and compact way possible. When you query the column, SQL Server automatically reconstructs

26、 the XML document into its original form. Note, however, that this will not include things like comments that are not part of the original data content of the document. What you get back is effectively a serialized rowset that represents the data you originally stored there.Its also possible to stor

27、e your XML documents without specifying a schema, in which case you create an un-typedxml column. In this case, the XML is stored as a simple character string, because SQL Server has no way of knowing the data type of each element and attribute. This is less efficient, but does maintain the complete

28、 original content of the XML document (such as comments, etc.). But remember that, even with an un-typed column, the XML you insert must be well-formed.To create an un-typed column, you simply omit the schema collection name when you create the table:CREATE TABLE MyTable(MyKey int, MyXml xml)Figure

29、2 shows SQL Server Management Studio displaying the structure of the Sales.Store table in the AdventureWorks sample database. You can see the xml-typed column named Demographics in the left-hand tree view, and a query that extracts the rows from this table in the right-hand query window. The results

30、 of running this query are shown in the grid below this, and weve super-imposed on this the view of the XML document you get when you click on the contents of one of the columns in the grid. Figure 2 - The xml column in the Sales.Store table, showing one of the XML documents it containsInserting and

31、 Selecting on an xml Column Once you have created your table, you insert an XML document into an xml column in the same way as you would for any other built-in data type. You can insert it as a string value, or use the CASTor CONVERT functions to specifically convert it to an xml type:INSERT INTO MyTable(MyKey, MyXml) VALUES (1, xml-document-string)INSERT INTO MyTable(MyKey, MyXml) VALUES (1, CAST(xml-document-string) AS xml)INSERT INTO MyTable(MyKey, MyXml) VALUES (1, CON