USING XQUERY WITH THE XML DATA TYPE XQuery

USING XQUERY WITH THE XML DATA TYPE XQuery Functions Supported by SQL Server (continued ) Functions on String Values concat contains substring string-length Constructors and Functions on Booleans not Functions on Nodes number id get-local-name-from-QName get-namespace-from-QName Context Functions position last Functions on Sequences empty distinct-nodes distinct-values Aggregate Functions count avg min max sum Constructor Functions: to create instances of any of the XSD types (except QNameand subtypes of xs:duration)

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

XML QUERY LANGUAGES: XQUERY AND XPATH Accessors get

XML QUERY LANGUAGES: XQUERY AND XPATH Accessors get information about a specific node. Examples include fn:node-kind, fn:node-name, and fn:data, which obtain the kind of node, the QName, and the data type. The error function is called whenever a nonstatic error (that is, an error at query execution time) occurs. It can also be invoked from XQuery or XPath applications and is similar to a .NET exception. Constructors are provided for every built-in data type in the XML Schema, Part 2, Datatypes, specification. These are similar to constructors in .NET classes and may be used to create nodes dynamically at runtime. Context functions can get information about the current execution con- text that is, the document, node, and number of items in the current sequence being processed. These are similar to environment variables in a program or the current environment (SETvariables) in a SQL query. Casting functions are used to cast between different data types, similar to casting in a programming language, the CType function in VB.NET, or the CAST function in SQL:1999. A specific casting function is defined for each legal cast among the XML Schema primitive types. SQL Server XQuery Functions and Operators The SQL Server engine s implementation of XQuery provides a rich subset of the XQuery and XPath functions and operators. Since both the SQL Server XQuery engine and the XQuery function specification are still in flux, the set of functions may change in the future or even before the time you read this. The functions that are implemented are listed in the box titled XQuery Functions Supported by SQL Server. XQuery Functions Supported by SQL Server Data Accessor Functions string data Functions on Numeric Values floor ceiling round

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services

USING XQUERY WITH THE XML DATA TYPE defines

USING XQUERY WITH THE XML DATA TYPE defines the data model as being based on the XML Information Set data model, with some extensions. It also describes functions that each XQuery processor should implement internally. Note that although these are described using functional notation, these are meant to be implemented inside the parser and should not be confused with the standard function library. We discussed the data model in the last chapter, along with its implementation in the SQL Server 2005 XMLdata type. The second companion spec is more relevant to XQuery and XPath as query languages. It is called XQuery 1.0 and XPath 2.0 Functions and Operators Version 1.0, and describes a standard function library and a standard set of operators for XQuery engine implementers. This would be similar to a standard function library in SQL:1999. The XQuery engine in SQL Server 2005 implements some of the standard functions, concentrating on the ones that make the most sense for a database or where the functionality is similar to a T-SQL function. The functions and operators are grouped in the spec according to the data type that they operate on or their relationship to other functions and operators. The complete list of groups is as follows: Accessors Error function Trace function Constructors Functions and operators on numerics Functions on strings Functions and operators on boolean values Functions and operators on durations, dates, and times Functions related to QNames Functions and operators for anyURI Functions and operators on base64Binaryand hexBinary Functions and operators on NOTATION Functions and operators on nodes Functions and operators on sequences Context functions Casting Some of these categories require further explanation.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

XML QUERY LANGUAGES: XQUERY AND XPATH The

XML QUERY LANGUAGES: XQUERY AND XPATH The SELECTstatement can also refer the other columns in the xml_tabtable in this case, the the_idcolumn. You could use OUTER APPLY instead of CROSS APPLY. In this case, because all the row values contain a matching node, the result will be the same, but if we added a nameless person as follows: INSERT xml_tab VALUES( ) the CROSS APPLY version would produce five rows, and the OUTER APPLY version would produce six rows with the name as a NULL value. A similar condition doesn t distinguish between CROSS APPLY and OUTER APPLY, however. Say we look for a node in the xml.value portion (l_name) that doesn t exist in any of the noderefs. SELECT the_id, a.value( l_name[1]/text()[1] , varchar(20) ) AS l_name FROM xml_tab CROSS APPLY xml_col.nodes( /people/person/name ) AS result(a) Both CROSS APPLY and OUTER APPLY produce the same number of rows, all with NULLvalues for l_name. Although the syntax for producing the rowset may seem strange at first, this equates to the second parameter in the OpenXML function, which also produces one row for each node selected by the expression in the second parameter. You use each reference node produced by the xml.nodes function as a starting node (context node) for the XMLdata type xml.query, xml.value, or xml.nodes functions. Using the reference node with xml.existis technically possible but of limited value. These functions are used to produce additional columns in the rowset, analogous to the WITH clause in OpenXML. When used in conjunction with the XMLdata type functions, the xml.nodes function is a less memory-intensive version of OpenXML. It uses less memory because data in the XML data type column is already parsed; we get similar results to using OpenXML without the overhead of having a DOM in memory. XQuery Standard Functions and Operators The XQuery standard includes two companion specifications that describe the data model and its extension functions. These standards apply not only to XQuery, but also to XPath 2.0 and indirectly to XSLT 2.0, which uses XPath 2.0 as its query language. The first specification, XQuery 1.0 and XPath 2.0 Data Model, lays the groundwork for both specifications and

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

USING XQUERY WITH THE XML DATA TYPE laurel

USING XQUERY WITH THE XML DATA TYPE laurel hardy ) GO we can use the xml.nodes method to extract a series of rows, one for each person s name. xml_col.nodes( /people/person[1] ) AS result(a) This produces a four-row, one-column rowset of abstract documents (most likely pointers) with each abstract document pointing at a different XML context node. We can then use the xml.valuefunction to extract values. SELECT the_id, a.value( text()[1] , VARCHAR(20) ) AS name FROM xml_tab CROSS APPLY xml_col.nodes( /people/person/name ) AS result(a) This produces a rowset that looks like this. the_id name – – 1 curly 2 larry 3 moe 4 laurel 4 hardy Notice that we could not have produced the same result by using either the xml.value function or the xml.query function alone. The xml.value function would produce an error, because it needs to return a scalar value. The xml.queryfunction would return only four rows, a single row for the laurel and hardy person. There are a few interesting features to note about this syntax. The xml.nodesmethod produces a named resultset (rowset), which must be given an alias enclosed within the keyword result. The xml.valuemethod on the left-hand side of the CROSSAPPLY keyword refers to the aresult column from the xml.nodesmethod. The xml.nodesstatement on the right-hand side of the CROSS APPLYkeyword refers to the xml_colcolumn from the xml_tab table. The xml_tabtable appears on the left-hand side of the CROSSAPPLYkeyword.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

XML QUERY LANGUAGES: XQUERY AND XPATH for $b

XML QUERY LANGUAGES: XQUERY AND XPATH for $b in //person where $b/name= moe return

  • { string($b/name[1] }
  • ) FROM xml_tab GO returns

  • moe
  • moe
  • This query also uses the XQuery string function, which leads us to talking about XQuery standard functions. We ll talk about XQuery standard functions and operators shortly. xml.nodes(string xquery-text) The xml.nodes XML data type function produces a single column rowset from the contents of an XML data type column. This function takes an XQuery expression and produces zero or more rows that contain a single column that is an opaque reference to a special type of XML document. This reference is special because the context node for future XQuery functions is set at the node that matches the XQuery statement in the xml. nodes clause. This document must be used with other XQuery functions, like queryor value, and can even be used as input to another nodesfunction. Because xml.nodesproduces a context node other than the root node, relative XQuery/XPath expressions can be used with the resultant document reference. The xml.nodesfunction must be used in conjunction with the CROSSAPPLYor OUTERAPPLYclause of a SELECTstatement, because the table that contains the XML data type column must be part of the left-hand side of the CROSSAPPLYclause; the rowset cannot be returned directly. Here s a simple example that shows its usage. Starting with the simple XMLdata type table of people: CREATE TABLE xml_tab( the_id INTEGER PRIMARY KEY IDENTITY, xml_col XML) GO INSERT xml_tab VALUES( curly ) INSERT xml_tab VALUES( larry ) INSERT xml_tab VALUES( moe ) INSERT xml_tab values(

    Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services

    USING XQUERY WITH THE XML DATA TYPE where

    USING XQUERY WITH THE XML DATA TYPE where $b/@name = moe return ($b) ) AS persons FROM xml_tab XPath predicate SELECT the_id, xml_col.query( /people/person[@name= moe ] ) AS persons FROM xml_tab Note that for the columns in which moe did not occur, the result is an empty sequence rather than an empty string or a NULLvalue. the_id persons 1 2 3 4 You can also return literal content interspersed with query data by using the standard curly brace notation. The following query returns a series of results surrounded by XHTML list tags (

  • ). Note that this uses an element normal form version of the XML in the table, rather than the attribute normal form we ve been using thus far. CREATE TABLE xml_tab( the_id INTEGER PRIMARY KEY IDENTITY, xml_col XML) GO INSERT xml_tab VALUES( curly ) INSERT xml_tab VALUES( larry ) INSERT xml_tab VALUES( moe ) INSERT xml_tab VALUES( curly larry moe moe ) GO literal result element and constructor with curly braces SELECT xml_col.query(

    Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

  • XML QUERY LANGUAGES: XQUERY AND XPATH The query

    XML QUERY LANGUAGES: XQUERY AND XPATH The query does not have to return a single node or a well-formed document, however. If the following row is added to the table, the results are different. INSERT xml_tab VALUES( ) GO SELECT xml_col.query( /people/person ) AS persons FROM xml_tab These are the results. persons < person name= curly /> Note that the result from the last row is a multinode sequence, and though it is not a well-formed XML document, it does adhere to the XQuery 1.0 data model. In addition to simple XQuery statements consisting of a simple XPath expression, you can use the FLWOR expressions that we saw at the beginning of this chapter. (Note that at this time, SQL Server s implementation of XQuery does not support the let FLWOR operator). This query uses FLWOR expressions and produces the same result as the previous query. SELECT xml_col.query( for $b in /people/person return ($b) ) FROM xml_tab You can subset the results with an XQuery predicate and the WHERE clause, as well as with an XPath predicate. For example, these two queries return the same results. XQuery WHERE clause SELECT the_id, xml_col.query( for $b in /people/person

    Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Cheap Web Hosting services

    USING XQUERY WITH THE XML DATA TYPE The

    USING XQUERY WITH THE XML DATA TYPE The xml.valuefunction is good for mixing data extracted from the XML data type in the same query with columns returned from SQL Server, which, by definition contain a single value. In addition, it can also be used as a predicate in a SQL WHERE clause or a JOIN clause, as in the following query. SELECT the_id FROM xml_tab WHERE xml_col.value( /people[1]/person[1]/@name , VARCHAR(50) ) = curly xml.query(string xquery-text) Although all the functions of the XML data type do invoke the internal XQuery engine, the function that actually executes a complete XQuery query on an instance of the XMLdata type is xml.query. This function takes the XQuery text, which can be as simple or complex as you like, as an input string and returns an instance of the XMLdata type. The result is always an XQuery sequence as the specification indicates, except when your XQuery instance is NULL. If the input instance is NULL, the result is NULL. The resulting instance of the XMLdata type can be returned as a SQL variable, used as input to an INSERT statement that expects an XML type, or returned as an output parameter from a stored procedure. XQuery usually gets its input from the XQuery functions doc() or collection() in dealing with documents from the file system or input from a stream. XQuery can also use a user-defined context document. In SQL Server 2005, though, remember that the xml.query function refers to a specific column (or instance of the XML data type), and input will come from that column or instance s data; this is the context document. The simplest XQuery query might be one that consists only of an XPath 2.0 expression. This would return a sequence containing zero or more nodes or scalar values. Given our previous document, a simple XQuery query like this: SELECT xml_col.query( /people/person ) AS persons FROM xml_tab would return a sequence containing a single node for each row. persons < person name= curly />

    Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

    XML QUERY LANGUAGES: XQUERY AND XPATH The xml.value

    XML QUERY LANGUAGES: XQUERY AND XPATH The xml.value function must return a single scalar value, or else an error is thrown. You wouldn t need to have xml.value to return a single XMLdata type, because that s what xml.querydoes. Using our overly simple document from the xml.existsection: CREATE TABLE xml_tab( the_id INTEGER PRIMARY KEY IDENTITY, xml_col XML) GO INSERT xml_tab VALUES( ) INSERT xml_tab VALUES( ) INSERT xml_tab VALUES( ) GO the following SQL query using xml.value: SELECT the_id, xml_col.value( /people[1]/person[1]/@name , varchar(50) ) AS name FROM xml_tab would produce a single row containing the_idand namecolumns for each row. The result would look like this: the_id name – 1 curly 2 larry 3 moe This function must only return a scalar value, however, so if the following row was added to the table: INSERT xml_tab VALUES( ) GO the same SQL query would only use the first person, even though there are two person elements. Note that we ve had to use the subscript [1] in every query to ensure that we re only getting the first person. This is because of SQL Server XQuery s static typing. If we didn t use the subscript, the engine would have to assume that the document permits multiple person elements, and would fail with the error XQuery: Expression must return a singleton.

    Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Tomcat Web Hosting services