Home > Developer > SQL Server
This article explores the built-in XML support in SQL Server 2005. Describes how this support integrates with client programming supported by the. NET framework V2.0 and native code, such as OLE DB and SQLXML.
First, Introduction
Extensible Markup Language (XML) is widely used as a platform-independent representation of data. It is useful for exchanging information within loosely coupled and disparate systems, as well as various enterprise to Enterprise (business-to-business) applications and workflows. Data exchange has become one of the main driving forces of XML technology.
XML is increasingly used in enterprise applications, and it is primarily used to model semi structured and unstructured data. Document management is one such application. Documents such as e-mail are semi-structured in nature. If the document is stored as XML in the database server, you can develop powerful applications to retrieve documents based on the contents of the document, to query parts of the content (for example, to find the part where the title contains the word "background"), and to query the document aggregation. Such a scenario would be feasible if there was an application that could generate and use XML. For example, Microsoft Office 2003 systems allow users to build Word, Excel, Visio, and Infopath documents as XML tags.
| • | Storing XML data in a relational database can bring benefits to data management and query processing. SQL Server provides powerful ability to query and modify relational data, and has been extended to query and modify XML data. This makes it possible to take advantage of investments made in previous releases, just as in cost-based optimization and data storage areas. For example, indexing techniques in relational databases are already well known and have been extended to index XML data so that you can optimize queries using cost-based decisions. |
| • | XML data can interoperate with existing relational data and SQL applications, so that XML can be introduced into systems that require data modeling without destroying existing applications. The database server also provides administrative capabilities to manage XML data (such as backup, recovery, and Replication). |
| • | These features promote the need for native XML support in SQL Server 2005, which solves the growing problem of XML usage. XML support in SQL Server 2005 will bring benefits to enterprise application development. |
| • | The following sections outline the XML support in SQL Server 2000 and 2005, describe some of the scenarios that drive XML usage, and discuss the server-side and client XML feature sets in detail. |
This section provides a brief overview of XML support in SQL Server 2000 and the subsequent release of the SQLXML client programming Platform Web Edition, providing rich support for mapping relational data to XML data or mapping XML data to relational data.
On the server, the XML data can be generated from the table and query the results by using the FOR XML clause in the SELECT statement. This is ideal for data interchange and WEB service applications. The inverse function for XML is a relational rowset generator function named OpenXML, which extracts the value from the XML data and places it in the column of the rowset by asking for the value of the XPath 1.0 expression. The application uses OpenXML to "shred" incoming XML data and store it in a table, or for queries in the T-SQL language.
SQL Server 2000 support for client programming is called SQLXML. At the heart of this technique is the XML view, which is a two-way map between XML schemas and relational tables. SQL Server 2000 supports only the mapping of XDR schemas, although it adds support for XSD in subsequent WEB editions. The XML view allows you to query using a subset of XPath 1.0, where you can use a mapping to convert a path expression to an SQL query in the underlying table and package the results of the query into an XML result.
SQLXML also supports the creation of XML templates, which allows you to create XML documents with dynamic portions. In an XML document, you can embed an XPath 1.0 expression above a FOR XML query and/or a mapped query. When you execute an XML template, you can replace the query block with the results of the query. In this way, you can create XML documents with some static content and some data-driven dynamic content.
In SQL Server 2000, there are two primary ways to access the SQLXML feature:
| • | Sqlxmloledb Provider. Sqlxmloledb Provider is an OLE DB provider that exposes Microsoft SQLXML functionality through ADO. |
| • | HTTP access. The SQLXML feature in SQL Server 2000 can also be accessed through HTTP using the SQLXML ISAPI filter. By using our configuration tools, you can create a Web site to retrieve incoming requests, which execute XML templates, XML views, and XPath 1.0 statements over HTTP. |
The server and client programming platforms provide rich support for generating and using XML data based on mapping between tables and XML data. This can handle structured XML data fairly well. In SQLXML, the query language is a subset of XPath 1.0 and has some limitations. For example, the descendant-or-self axis (//) is not supported. As a result, there are certain limitations when developing some solutions. For example, the order of XML documents is not saved, which is critical for applications such as document management. In addition, recursive XML schemas are not supported. Despite these limitations, the client SQLXML and server XML features are widely used in application development. SQL Server 2005 addresses many of these limitations, extends the relational XML Exchange functionality, and provides native XML support.
This section provides a brief overview of the new XML support added in SQL Server 2005, supplemented by support from the. NET framework V2.0 and native Client data access such as OLE DB.
The XML data model has features that make it difficult to map to relational data models, if not entirely impossible. XML data has a hierarchical structure that can be recursive; a relational database provides weak support for hierarchical data (modeled as a foreign key relationship). Document order is an intrinsic attribute of an XML instance and must be saved in the query results. This contrasts with relational data, which is unordered and must be forced to sort by an additional sort column. It is laborious to regroup the results at query time, because the actual XML schema decomposes the XML data into a large number of tables.
SQL Server 2005 introduces a native data type called XML. Users can create tables that have one or more XML-type columns in addition to the relational column, and also allow variables and parameters. To better support XML model features such as document order and recursive structure, XML values are stored as large binary objects (BLOBs) in an internal format.
SQL Server 2005 provides a collection of XML schemas that can be used as a means of managing the XML schema of the database in the form of metadata. XML data types can be associated with XML schema collections to impose schema restrictions on XML instances. When XML data is associated with an XML schema collection, it is called typed XML, otherwise, it is called untyped XML. In a framework, both typed and untyped XML can be accommodated, the XML data model is persisted, and the query processing is enforced using XML semantics. The underlying relational infrastructure is widely used for this purpose. It supports interoperability between relational and XML data, which opens the way for more extensive adoption of XML functionality.
You can use a T-SQL SELECT statement to retrieve an XML instance. There are five built-in methods for querying and modifying XML instances in XML data types.
The method of XML data type accepts XQuery, which is a newly emerged standard language for the new system (currently in the Last Call (final request) state) and includes the navigation language XPath 2.0. You can also use a language to modify XML data, such as adding or deleting subtrees and updating scalar values. Along with a large set of functions, embedded XQuery and data modification languages provide rich support for processing XML data.
The XQuery type system is consistent with the XML schema type of the consortium. Most SQL types are compatible with the XQuery type system (for example, decimals). A few types (for example, xs:duration) are stored in an internal format and can be compatible with the XQuery type system by appropriate interpretation.
The compile phase examines the correctness of the static types of XQuery expressions and data modification statements, and uses XML schemas for type inference in the case of typed XML. If an expression fails at run time because of a type security conflict, a static type error is generated.
Query execution processes each XML instance at run time, and query execution becomes very time-consuming if the XML value is large or needs to be evaluated in many rows of the table. Therefore, a mechanism for indexing XML columns is provided to speed up queries.
B + trees are widely used to establish index of relational data. The primary XML index of an XML column creates a B + Tree index on all tags, values, and Paths of XML instances in the column. In this way, queries in the XML data can be evaluated effectively, and the XML results are ungrouped from the B + tree while preserving the document order and document structure.
You can create a secondary XML index in an XML column to speed up common queries of different categories: the path index (for a query-based query), the property index (for the case of the package), and the value index (for a value-based query).
XML columns, variables, and parameters can be selectively typed according to the collection of XML schemas, which may be related to another collection (for example, by using) or unrelated. Each typed XML instance specifies the target namespace from the XML schema collection it follows. The database engine verifies the validity of an instance based on an XML schema when data is allocated and modified.
XML schema information is used for storage and query optimization. Typed XML instances contain typed values in the inner binary representation, as in the XML index. In this way, typed XML data can be effectively processed.
Users can store relational data and XML data in the same database. In short, the database engine knows how to follow the XML data model in addition to knowing how to follow the relational data model. After upgrading to SQL Server 2005, relational data and SQL applications can still function correctly. On the server, you can move the XML data residing in files and text or image columns into columns of XML data types. By using a method of XML data types, you can establish and query and modify an XML column's index.
Databases use existing relational infrastructure and engine components, such as storage engines and query processors, for XML processing. For example, an XML index can create a B + tree, and you can view the query plan in the Showplan output. Data management functions, such as backup/restore and replication, are available for XML data by integrating into the relational framework. Similarly, new data management capabilities, such as database mirroring and snapshot isolation, can handle XML data types to provide a seamless user experience.
Structured data should be stored in tables and in the close series. XML data types are a relatively appropriate choice for semi-structured and tagged data that uses XML when the application needs to perform fine-grained queries and data modifications.
The existing for XML functionality has been enhanced in several ways. It can handle instances of XML data types and other new types of SQL, such as [N]varchar (max).
The XML data type instance generated by the new Type directive can be assigned to an XML column, variable, or parameter, or it can be queried using a method of an XML data type. This allows you to nest SELECT ... The for XML TYPE statement.
The path mode allows the user to specify the path in the XML tree where the column values appear, and is easier to write than for the FOR XML EXPLICIT when used with the nested nesting.
The XSINIL directive is used with ELEMENTS to map NULL to an element with a property xsi:nil= "true". In addition, the new root directive allows you to specify the root node in all for XML schemas. The new XmlSchema instruction generates an XSD inline schema.
Enhancements to OpenXML include accepting XML data types in sp_preparedocument and columns that generate XML and new SQL types in a rowset.
The client can access the XML data in the server in several ways. Native SQL client access that uses ODBC and OLE DB to pass XML data types as Unicode strings. OLE DB also provides ISequentialStream access to the XML data types of convective Unicode data.
Managed access passes the XML data as a new class named SqlXml through the ado.net in the. NET Framework V2.0. It supports a method named Createreader (), which returns an XmlReader instance to read the returned XML. Similarly, a dataset can load an instance of an XML data type into a middle-tier column, and the middle tier can be edited as an XML document and saved to SQL Server again. Both support issuing SQL queries to the server to retrieve XML columns that operate in the middle-tier.
In SQL Server 2005, you can query, retrieve, and modify XML data by using SOAP access directly to the HTTP endpoint.
Both native and managed client technologies provide a new interface for retrieving the XML schema collection of typed XML columns.
In addition to using Ado.net to retrieve tables and store results in a relational dataset, you can also use the Xquerycommand class to load relational data directly into an XML document on the middle tier. This class provides a middle-tier XQuery processor that can embed T-SQL statements as part of an XQuery language. In this way, you can query the local XML file, or retrieve the data from SQL Server (either directly from the table or through a database store program), construct the data in XML format, and then load it into the middle-tier XML document. This greatly simplifies queries in SQL Server, creates query results in specific XML formats, and makes them flow to business partners without having to load data into datasets to perform data transformations.
Second, the promotion of XML storage scenarios
XML data is becoming more common. It can represent customer data, and XML schemas with or without descriptive data are available. XML data and XML schemas need to be managed together. The actual application's XML Schemas are often complex, so mapping such XML schemas to tables and columns is a complex task. Long-time maintenance of such mappings is a hassle when XML schema changes or new schemas are added to the system. Typically, XML data is stored in a text column of a file system or database server. Text columns have data-management benefits (for example, replication and backup/recovery), but do not provide query support for any data-based XML structure. With native XML support, application development with XML becomes faster.
Some applications, such as user interface software, allow users to choose from a set of fixed properties. Other applications allow users to define properties that are of interest to them. If such custom attributes are stored in XML format, they can be managed well. An application can support a property other than a scalar property:
| • | Multivalued properties in objects, such as multiple phone numbers, can be supported. |
| • | Complex properties can be supported, for example, the author attribute of a document may be the contact information for that author. |
Object properties can be stored in columns of XML data types and indexed to improve the efficiency of query processing.
XML allows you to exchange data between applications in a platform-independent way. You can use XML tags to model such data as messages. Instead of constantly splitting and generating XML messages, it is wise to store messages in XML format. This is exactly what the data stream needs. The XML message that arrives at the workflow stage carries the current state. Each message needs to be processed, the processing progress recorded in the XML content (such as state change), and then the XML data is forwarded to the next stage of workflow processing. Messages can be of different types, perhaps even semi-structured, and have different XML schemas associated with them, so mapping them to a table is not always easy.
Xml-based standards for different vertical areas, such as financial data and geospatial data, are emerging. These standards describe the structure of the data based on the instance data that can be queried and updated. Typically, the actual data is in binary form, and the XML data provides metadata information about them.
As a simple example, to transfer an input parameter table to a stored program or function, the application first converts the data to XML and then passes it as an XML data type parameter. Regenerates a rowset from an XML parameter within a stored procedure or function.
Suppose you have a call center that uses an XML document to maintain patient records and conversations. When a patient initiates a call, the call center wants to revert to the previous conversation to set the scene for the incoming call. This can benefit the application by querying the XML tag implementation. In addition, you can easily retrieve the details of previous conversations and record current conversations.
Documents such as e-mail are essentially semi-structured. Documents with XML tags are becoming more and more easy to create, for example, using Office 2003. These XML documents can be stored in XML columns and indexed for querying and updating. As a result, developers can do more by leveraging native XML support.
Third, server-side XML processing in SQL Server 2005
SQL Server 2005 support includes providing a database in which you can store relational data and XML data.
You can use the normal CREATE TABLE statement to create a table with an XML column. You can then create an index of the XML in a special way.
The SQL Server SQL-2003 XML data type implements the ISO-standard XML data type. As a result, it can store not only well-formed XML 1.0 documents, but also so-called XML content fragments (with text nodes and any number of top-level elements). The XML data type is not required to be bound to an XML schema when the data is checked for a well-formed format, but malformed data is rejected.
Untyped XML can be used when the schema is not known to be apriori and therefore results in a map-based solution that is not possible to implement. If schemas are known, but mapping to relational data models is complex and difficult to maintain, or there are multiple schemas that are later bound to data based on external requirements, you can also use untyped XML.
Example: Table non-typed XML columns
The following statement creates a table named "Docs" with the integer primary key "PK" and the Untyped XML column "Xcol":
You can also create a table that contains multiple XML columns or close series, with a primary key, or without a primary key.
If there is an XML schema in the XML schema collection that describes the XML data, you can associate the XML schema collection with the XML column that produces the typed XML. You can use XML schemas to validate the validity of data, perform more accurate type checking than untyped XML when compiling queries and data modification statements, and optimize storage and query processing.
typed XML columns, parameters, and variables can store XML documents or content, and can be specified as an option when declared (either document or content, default to content). In addition, you must provide a collection of XML schemas. Specifies the DOCUMENT if each XML instance has exactly one top-level element, otherwise the CONTENT is used. The query compiler uses the DOCUMENT tag in type checking to infer singleton top-level elements.
Example: Typed XML columns in a table
XML columns, variables, and parameters can be bound to an XML schema collection (see the "XML Schema Processing" section later in this article for more detailed information and examples). Suppose mycollection represents such a collection. The following statement creates a table Xmlcatalog with an XML column document that is typed using mycollection. Typed XML columns are also specified to accept XML fragments, not just XML documents.
CREATE TABLE XmlCatalog ( ID INT PRIMARY KEY, Document XML(CONTENT myCollection))
Constraining columns of XML data types
| • | In addition to typing an XML column, you can use relationship (column or row) constraints in columns of typed and untyped XML data types. Most SQL constraints can also be applied to XML columns, and the only notable exception is primary and foreign key constraints, because instances of XML data types are incompatible. Therefore, you can specify that the XML column can be empty or not nullable, provide a default value, and define a CHECK constraint in the column. For example, an untyped XML column can have a CHECK constraint to verify that the stored XML instance conforms to the XML schema. |
Use constraints under the following conditions:
| • | Business rules cannot be represented in XML schemas. For example, a florist's delivery address must be within 50 miles of its business location, which can be written as a constraint in an XML column. This constraint can include methods of XML data types. |
| • | Constraints involve other XML or non-XML columns in the table. An example of this is to match the ID of the Customer (/customer/@CustId) that exists in the XML instance to the value in the integer CustomerID column. |
Example: constraining XML columns
To determine that <book> <author> <last-name> is different from <author> 's <first-name>, you can specify the following CHECK constraints:
CREATE TABLE Docs (PK INT PRIMARY KEY, Xcol XML not nullconstraint ck_name CHECK xcol.exist ('/book/author[first-name = LA St-name] = 0))
SQL Server 2005 stores the XML data as Unicode (UTF-16). The result of XML data retrieved from the server is also UTF-16 encoded. If you want to adopt a different encoding method, you need to make the necessary transformations after retrieving the data, either by forcing the type conversion or by performing the conversion in the middle tier. For example, the XML data can be coerced to the varchar type on the server, in which case the database engine serializes the XML through the encoding determined by the sort of the varchar type.
You can provide XML values for XML columns, parameters, or variables in a variety of ways.
| • | A character or binary SQL type that is implicitly converted to an XML data type. |
| • | As the contents of the file. |
| • | Output as XML publishing mechanism for XML (with type directives that generate instances of XML data types) |
Provides a well-formed check of the values provided and allows the storage of XML documents and XML fragments. If the data is not checked by a well-formed check, it is rejected and an appropriate error message is issued.
For typed XML, you need to check that the supplied value conforms to the XML schema of the XML schema collection that has been registered to the typed XML column. If the XML instance does not pass this validation, it is rejected. In addition, the document tag in typed XML restricts the accepted value to an XML document only when the content allows XML documents and content to be supplied.
Example: inserting data into a untyped XML column
The following statement inserts a new row in table docs with a value of 1 inserted in the PK column of the integer and an instance inserted in the XML column. The data (provided as a string) is implicitly converted to an XML data type and checked for good formatting during the insert process.
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>39.99</price> </book>')
Example: Inserting data from a file into an untyped XML column
The INSERT statement shown below uses OPENROWSET to read the contents of the file C:empxmlfile.xml as a BLOB. Inserts a new row in the table docs, value 10 as the primary key, and the BLOB as the XML column Xcol. The format-good check appears when the contents of the file are assigned to an XML column.
INSERT INTO docs SELECT 10, xCol FROM (SELECT * FROM OPENROWSET (BULK 'C: empxmlfile.xml', SINGLE_BLOB) AS xCol) AS R(xCol)
Example: inserting data into a typed XML column
A typed XML column requires an XML instance data to specify the target namespace for the XML schema to which it is typed (the namespace can be empty). In the following example, this is done through the namespace declaration Xmlns=http://mydvd.
INSERT XmlCatalog VALUES(2, '<?xml version="1.0"?> <dvdstore xmlns="http://myDVD"> <dvd genre="Comedy" releasedate="2003"> <title>My Big Fat Greek Wedding</title> <price>19.99</price> </dvd> </dvdstore>')
Example: Storing XML data that is generated with a FOR XML with the TYPE directive
The enhanced for XML with the type directive can produce results such as instances of XML data types. The resulting XML can be assigned to an XML column, variable, or parameter. In the following statement, the XML instance that is generated using the FOR XML type is assigned to the variable @xVar of the XML data type. You can use methods of XML data types to query variables.
DECLARE @xVar XML
SET @xVar = (SELECT * from docs for XML Auto,type)
Storage representation
An instance of an XML data type is stored in an internal binary representation, which is fluidization and optimized to allow for more efficient parsing. The tag is mapped to an integer value, and the mapped value is stored in the internal representation. This also produces some compression of the data.
For untyped XML, the node value is stored as a Unicode (UTF-16) string, so a run-time type conversion is required to perform the operation. For example, to find the value of predicate/book/price > 9.99, you must convert the price value of the book to a decimal. For typed XML, the encoding type of the value is the type specified in the XML schema. This makes parsing of the data more efficient and does not have to be run-time conversion.
The binary form of storage is limited to 2 GB per XML instance, which can accommodate most of the XML data. In addition, the depth of the XML hierarchy is also limited to 128 layers.
The contents of the information set of the XML data are preserved. However, it cannot be an identical copy of the text XML because the following information is not preserved: insignificant whitespace, order of attributes, namespace prefixes, and XML declarations.
Data Modeling Considerations
Typically, a combination of relational data types and columns of XML data types is more appropriate for data modeling. Some of the values in the XML data can be stored in a closed series, while the remaining values or all of the XML values are stored in an XML column. This can result in better performance and locking characteristics.
For singleton values (that is, a single value property), the values in the XML data can be promoted to computed columns in the same table. A multivalued attribute requires a separate property sheet, which must be populated and maintained using triggers. Queries need to be written directly against the property sheet.
For locking and updating attributes, the granularity of XML data stored in XML columns is critical. SQL Server uses the same locking mechanism for XML and non-XML data. If the granularity is large, in the case of multiple users, locking a large XML instance for updating can cause a decrease in throughput. On the other hand, strict decomposition will lose the encapsulation of the object and increase the cost of the regroup.
Querying and modifying XML data
Querying an XML instance stored in an XML column requires parsing the binary XML data in the column. Parsing binary XML is much faster than parsing XML data in the form of text. XML indexes avoid reparse, which is discussed in the section "Building indexes for XML data."
Methods in XML data types
If you are interested, you can retrieve all of the XML values, or you can retrieve some XML instances. This can be accomplished using four methods of XML data types: Query (), value (), exist (), and nodes (), which accept XQuery expressions as arguments. The fifth method, modify (), allows you to modify XML data and accept XML data modification statements as input.
The query () method is used to extract parts of an XML instance. The XQuery expression is evaluated as a list of XML nodes. The subtree that is rooted in each of these nodes is returned in document order. The result type is untyped XML.
The value () method extracts a scalar value from an XML instance. It returns the value of the node for which the XQuery expression is evaluated. The value is converted to the T-SQL type specified by the second parameter of the value () method.
The exist () method is used to check for the existence of an XML instance. Returns 1 if an XQuery expression is evaluated as a Non-empty node list, otherwise, returns 0.
The nodes () method produces an instance of a specific XML data type, each of which sets its context to a different node for the value of the XQuery expression. Specific XML data types support query (), value (), nodes (), and exist () methods, and can be used for count (*) aggregation and NULL checking. All other uses will generate errors.
The Modify () method allows you to modify portions of an XML instance, such as adding or removing subtrees, or updating scalar values (such as replacing a book's Price from 9.99 to 39.99).
Example: Using the query () method
Consider the query in the XML column xcol of the following table docs, which extracts the element with ID 123 anywhere under the element. The query also retrieves values from the integer primary key column. The query () method in the SELECT list is evaluated for each row in the table that generates the sequence of elements, and the elements and their subtrees are retrieved in document order. For each XML instance, if there is no element with ID 123 or there is no element under it, the result is not returned, that is, the return value of the query () method is NULL.
SELECT PK, Xcol.query ('/doc[@id = 123]//section ')
From docs
The NULL return value can be filtered out in an external SELECT statement. Alternatively, you can use the exist () method, as shown in the next example.
Example: Using the exist () method
Consider the following query, which includes the query () and exist () methods in the XML column xcol of table docs. The exist () method asks for the value of the path expression/doc[@id = 123] To check for the existence of a top-level element with a property called an ID, with a value of 123. For each such row, the query () method in the SELECT clause is evaluated; In this example, the query () method produces an element sequence anywhere under the element. Any rows that return 0 from the exist () method will be ignored.
SELECT xCol.query('/doc[@id = 123]//section')
FROM docs
WHERE xCol.exist ('/doc[@id = 123]') = 1
Example: Using the value () method
The following query uses the value () method to extract the title of the third part of the document in the form of a Unicode string. The nvarchar (max) of the resulting SQL type is specified as the second parameter of the value () method. The XQuery function data () extracts a scalar value from a node.
SELECT xcol.value (' data (/doc//section[@num = 3]/heading) [1]) ', ' nvarchar (max) ') from docs
Many of the XML comes from Office documents that are stored in a file system, Web service, or configuration file. In fact, the data generated in XML or as a virtual XML document is constantly increasing. In order to deal with these more and more data, a powerful query language XQuery emerged. The rationale for selecting XQuery in the XQuery Language specification (located in Http://www.w3.org/TR/xquery) is described as:
| • | A query language that skillfully uses XML constructs that can represent queries across a variety of data, regardless of whether the data is physically stored in XML or is considered XML through middleware. The specification describes a query language called XQuery, which is designed to be widely used in many types of XML data sources. |
| • | XQuery is designed to meet the requirements of the Consortium XML Query Workgroup XML Query 1.0 requirements and use cases in XML query cases. It is a language designed to make queries simple and understandable. It is also fairly flexible and can query a wide range of XML information sources, including databases and documents. |
| • | XQuery can also be summed up as follows: The XQuery language is to XML just as the SQL language is to relational databases. |
An Xquery subset embedded in T-SQL (located in http://www.w3.org/TR/xquery/) is a language that supports querying XML data types. This language is being developed by the worldwide Web Consortium (currently in the final request state), and all major database vendors, including Microsoft, are involved. Our implementation is consistent with the draft XQuery, released in November 2003.
XQuery includes XPath 2.0 as the navigation language. The XQuery implementation of SQL Server 2005 provides constructs for traversing nodes (for), node checking (where), return values (returns), and sorting (order by). It also provides an element construct that is used to reconstruct data in the query process.
SQL Server 2005 also provides a language construct for data modification (DML) for XML data types (see the "Data modification" section below for more information). The following example shows how to use XQuery with XML data types.
Example: Using the rich language constructs in XQuery
The following query shows how to use several XQuery language constructs together. It returns the caption in the range of area number 3 and higher from the document with ID 123 and wraps it in the new tag.
SELECT pk, xCol.query('
for $s in /doc[@id = 123]//section
where $s/@num >= 3
return <topic>{data($s/heading)}</topic>')FROM docs
"For" traverses all <section> elements under the <doc> element with ID 123, and binds each such <section> element to the variable $s. "Where" ensures that the area number (the @num property of the <section> element) is 3 or higher. The query returns the values in the area
Query compilation and execution
SQL statements are parsed by the SQL parser. When it encounters an XQuery expression, it jumps to the XQuery compiler and then compiles an XQuery expression. This results in a query tree that is grafted into the query tree of the entire query.
The entire query tree is optimized for queries and produces a physical query plan based on a cost-based assessment. The Showplan output shows most of the relational operators and some new operators (for example, UDX for XML processing).
Query execution, like the rest of the relationship framework, is oriented to tuples. The value of the WHERE clause is evaluated on each row of the table docs, which requires parsing the XML BLOB at run time in order to produce the value of the XML data type method. If the condition is satisfied, the row is locked and the value of the SELECT clause is evaluated in the row. The results are generated as XML data types (for the query () method) and converted to the specified target type (for the value () method).
If the row does not satisfy the condition in the WHERE clause, it is ignored and the execution is transferred to the next line.
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
