SqlServer2005

《SqlServer2005》由会员分享，可在线阅读，更多相关《SqlServer2005（54页珍藏版）》请在装配图网上搜索。

1、SQL 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 of its capabilities. Incre

2、asingly 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 one area that has seen l

3、ess 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, etc. This is generally f

4、ine 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 support for and close inte

5、gration 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 safe multi-user access to

6、 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 application that allows you t

7、o 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 XQuery and combining XQue

8、ry 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 persist your XML documents wit

9、hin 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 new features in SQL Server

10、 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 present; and automatic s

11、hredding 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, which allows stored proce

12、dures 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 years, its become increasin

13、gly 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 that represents a DataSe

14、t 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 on the client. To specify

15、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 the data stored in the X

16、ML 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 recommendation, see http:/www.w3.org/T

17、R/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 repository that you can use to

18、 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 . schema content .You can a

19、dd 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.The name you assign to t

20、he 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: .Then you link your XML

21、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 install in SQL Server 2005

22、. 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 table, as a parameter or v

23、ariable 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-formed). Typed and Un-typed xm

24、l 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 xml(MyNewSchemaCol)Now the

25、 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 the XML document into it

26、s 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 store your XML documents with

27、out 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 original content of the

28、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 2 shows SQL Server Manage

29、ment 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 of running this query ar

30、e 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 Selecting on an xml Colu

31、mn 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) VALUE

32、S (1, xml-document-string)INSERT INTO MyTable(MyKey, MyXml) VALUES (1, CAST(xml-document-string) AS xml)INSERT INTO MyTable(MyKey, MyXml) VALUES (1, CONVERT(xml, xml-document-string)When extracting an XML document from an xml column you can convert it to a char, varchar, ncharor nvarchartype if you

33、want to specify the character format:SELECT CONVERT(nvarchar(500), MyXmlDoc, utf-8) FROM MyTable WHERE . etc. .For a more detailed discussion of how you can store and manipulate XML Schemas and use the xml data type to store XML documents in SQL Server 2005, check out the book ADO.NET and System.Xml

34、 v 2.0: The Beta Version at Querying and Manipulating XML in SQL Server 2005The previous section of this article has described the techniques for creating tables that contain an xml column, and how you can insert and extract XML documents from such a table. However, these assume that you actually wa

35、nt to access the complete document. As youll see later, this often isnt the best way to work with XML - especially if you only need to access or update specific parts of the XML document. SQL Server 2005 supports two new XML manipulation techniques that can provide better performance and reduce the

36、application-level code you have to write. These new techniques are XQuery and the support for XML-DML in the xml data type, and well briefly look at both of them next. XQuery Support in SQL Server 2005For a long time, XML and relational data have been considered to be two different (yet complimentar

37、y) approaches to working with data. However, as you saw earlier, the XML Infoset model and the use of XML Schemas has narrowed that divide so that developers increasingly need to work with their data in both formats, and be able to switch between them easily. The least intuitive aspect of this is th

38、at the developer needs to master the techniques for querying XML documents, and often this involves transforming them into the required format. Until quite recently, this meant either performing complex processing of the XML as a character string, or using an XSL-T style sheet. XSL-T is a very power

39、ful technology, but is not particularly intuitive or easy to use.In response to this, the W3C has committed to producing a recommendation for a new query language called XML Query, or XQuery as it is more usually known. XQuery combines a syntax that is familiar to developers used to working with rel

40、ational data with the power of the XPath language that is used to select individual sections or collections of elements from an XML document.We dont have room here to provide a complete tutorial for XQuery, but there are plenty of places you can go to read more about it. These include the W3C Web si

41、te (http:/www.w3.org/XML/Query), which contains a section named Other Pointers providing links to many other useful resources. XQuery allows you to select individual values from an XML document simply by specifying an XPath that identifies the elements or attributes you want, for example:doc(myxmlfi

42、le.xml)/root/productid=304/nameThis code calls the built-in XQuery doc function to query a document named myxmlfile.xml, and return the name element that is a child of the product element where that product element contains an attribute named id that has the value 304. The result would be something

43、like:Large Flange CompressorAlternatively, you can use an XQuery FLWOR statement to build up an XML document that contains values from the source document. FLWOR is an acronym that describes the sections of the XQuery statement you can use - the basic syntax is:for $variable in sequence-or-contextle

44、t $variable := sequence-or-contextwhere expressionorder by expressionreturn literal-and-nodesFor example, you can create an XML document that has a root element named myproductlist, and contains a list of product descriptions ordered by product name where the value of the id attribute is greater tha

45、n 10, using this kind of syntax: for $p in doc(products.xml)/product let $d := $p/description where data($p/id) 10 order by $p/name0 return $dThe result would be of the form: Reverse flange compressor for big things Extrapolating geek extractor for Windows XP Cross-compiling actuarial extender with

46、flapsSQL Server 2005 supports a subset of the XQuery language, so you can use these kinds of queries to extract data from XML documents stored in both typed and un-typed xml columns. However, note that SQL Server 2005 does not support the let statement - in almost all cases you can create an equival

47、ent query without requiring this. XQuery also implements a large selection of built-in functions, including the data function used in the example above (this function extracts the contents of an element - without it you get back the complete element including the opening and closing element tags). S

48、QL Server 2005 implements the most common functions, and in general you will find that you can accomplish all the tasks you need with the subset that are available. Note that SQL Server 2005 does not implement the doc function, as the XML input comes from a column in the database rather than from a

49、disk file. Youll see in the next section how we access an XML document within an XQuery.XML Methods and XML-DML Support in SQL Server 2005The second new technology supported by SQL Server 2005 is the XML Data Manipulation Language (XML-DML). The new xml data type in SQL Server 2005 is, of course, an

50、 object in its own right (a class that implements the required behaviour and persistence for an XML document). And, like any other object, it exposes methods that you can use to manipulate the data it contains. These methods include: The query method, which returns a fragment of un-typed XML The val

51、ue method, which returns a single value from the XML and exposes it as a standard (non-xml) SQL data type The exist method, which can be used to test whether a specific node/value exists in the XML data The modify method, which executes an XML Data Modification Language (XML-DML) statement The nodes

52、 function, which returns a single-column rowset of nodes from the XML The query, value and exist methods are useful if you just need to access an XML document to get specific values from it or check if it contains a specific value, without having to extract the whole document. The modify method is u

53、seful when you want to update the XML content by executing an XML-DML statement. Again, you can do this without having to extract the whole document.Using the query MethodAs an example, this is a SQL statement that queries the XML in an un-typed column named MyXml in a table named MyTable, and retur

54、ns the matching name element:SELECT MyXml.query(/root/productid=304/name) FROM MyTable The result would be something like:Large Flange CompressorAs you can see, there is no need to use the XQuery doc function. To get just the value of the matching element, you can use the data function:SELECT MyXml.

55、query(data(/root/productid=304/name) FROM MyTable In this case, the result would be just:Large Flange CompressorIf the xml column you are querying against is a typed column (in other words, there is a schema registered for this column), you must specify the namespace for the schema in your query. Th

56、e easiest way is to assign the namespace to a prefix, and use this prefix with each element in the query. For example:SELECT MyXml.query( declare namespace s=http:/myns/mydemoschema; /s:root/s:products:id=304/s:name) FROM MyTable You can also use the query method to execute a FLWOR XQuery. Based on

57、the example we looked at earlier, we can build an XML document that contains a list of product names from a table containing a column named ProductXMLusing this query:SELECT ProductXML.query( for $p in /product where data($p/id) 10 order by $p/name1 return $p/description)FROM ProductList WHERE Produ

58、ctGroup = 3This assumes that each row in the ProductList table is an XML document containing a list of products in a specific category (ProductGroup is the column containing the category number). It will returns an XML document such as: Reverse flange compressor for big things Extrapolating geek ext

59、ractor for Windows XP Cross-compiling actuarial extender with flapsUsing the value MethodThe value method works much like the query method, except that it takes a second parameter that is the name of one of the SQL Server built-in data types. The value is returned as an instance of that type. The ot

60、her point to note is that the XPath you specify must return a single node, so you should specify the node index (when using the query method, the XPath can select more than one node and will return an XML fragment or list of these matching nodes). For example:SELECT MyXml.value(/root/productid=304/n

61、ame)1, nvarchar(30)FROM MyTable Remember that you must declare the namespace of the document if the source column is a typed xml column. Using the exist MethodThe exist method takes an XPath expression that selects a single node within the XML document, and returns either True (bit value 1) if the n

62、ode exists or False (bit value 0) if it does not. If the source column is a typed xml column (in which case you must declare the namespace in your query), and the element contains null, the method returns NULL instead. So the XQuery:SELECT MyXml.exist(/root/productid=304)1 FROM MyTable will return True if there is a product with the id value 304 (a product element with the attribute id=