USING XQUERY WITH THE XML DATA TYPE the

USING XQUERY WITH THE XML DATA TYPE the XQuery xml.exist expression to see if any row in the table contains a person named moe would look like this: SELECT xml_col.exist( /people/person[@name= moe ] ) AS is_there_moe FROM xml_tab The result will be a three-row rowset containing the value True (1), False (0), or NULLin the is_there_moecolumn. Notice that if we add just a single row containing a single XML document with multiple elements: insert one row containing an XML document with 3 persons rather than 3 rows each containing an XML document with 1 person INSERT xml_tab VALUES( ) the result is a one-row rowset, not one row for each person in the XML document. The rows are the rows of a SQL rowset, rather than XML results. The xml.exist function would most often be used as a predicate in a SQL statement that is, in a SQL WHERE clause. This SQL statement would only select rows that had an XML document containing a person named moe . SELECT the_id FROM xml_tab WHERE xml_col.exist( /people/person[@name= moe ] ) = 1 A good use for xml.existis in a selection predicate in a query that then produces complex XML for the same or another XML column by using the xml.query function. We ll come back to this when we discuss xml.query. xml.value(string xquery-text, string SQLType) The xml.value function takes a textual XQuery query as a string and returns a single scalar value. The SQL Server type of the value returned is specified as the second argument, and the value is cast to that data type. The data type can be any SQL Server type except the following: SQL Server XMLdata type A user-defined type SQL Server TIMESTAMPdata type

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 xml.valuetakes

XML QUERY LANGUAGES: XQUERY AND XPATH xml.valuetakes an XQuery query as input and returns a SQL type as a scalar value. xml.existtakes an XQuery query as input and returns 0, 1, or NULL, depending on whether the query returns an empty sequence (0), a sequence with at least one item (1), or the column is NULL. xml.nodestakes an XQuery query as input and returns a one- column rowset that contains references to XML documents with the XQuery context set at the matching node. Currently, SQL Server 2005 XQuery is aligned to the November 2003 XQuery draft, with some implementation restrictions. As SQL Server 2005 approaches RTM (release to manufacturing), the dialect may be aligned to a later draft. Although the XQuery language itself can be used to produce complex documents, as we ve seen, this functionality is really only used to a great extent in SQL Server in the xml.query function. When using xml.value and xml.exist, we ll really be using simple XPath 2.0 expressions rather than complex XQuery with sequences, constructors, formatting information, and so on. Although we could use more complex XQuery, the reason for this is that xml.value can only return a sequence containing a single scalar value (it cannot return an XML data type), and xml.exist is used to check for existence. So let s cover them first. xml.exist(string xquery-text) The xml.exist function refers to a single column or variable of the XML data type. For example, if we define the following table: CREATE TABLE xml_tab( the_id INTEGER PRIMARY KEY IDENTITY, xml_col XML) and fill the table with some rows, the xml.exist function will search the XML column specified, looking for data that matches the given XQuery expression. For each row, if the row contains matching data in the specified column, the function returns true (1). For example, if the rows inserted look like this: INSERT xml_tab VALUES( ) INSERT xml_tab VALUES( ) INSERT xml_tab VALUES( ) GO

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

USING XQUERY WITH THE XML DATA TYPE 7.

USING XQUERY WITH THE XML DATA TYPE 7. If the cast fails, a dynamic error is raised. 8. The result of the comparison is true if the value of the first operand is (equal, not equal, less than, less than or equal, greater than, greater than or equal) to the value of the second operand; otherwise, the result of the comparison is false. B.2 Operator Mapping describes which combinations of atomic types are comparable and how comparisons are performed on values of various types. If the value of the first operand is not comparable with the value of the second operand, a type error is raised. Quite a bit more complex, don t you agree? SQL is a more strongly typed language than XQuery. In XQuery, static typing is possible but optional, although SQL Server s implementation of XQuery implements static typing. In SQL, strong typing is required. When static typing is used, having schemas assists optimization. Both SQL and XQuery are declarative languages; however, Transact-SQL is more procedural, especially when combined with stored procedures that can contain procedural logic. Oracle s Transact-SQL equivalent, PL/SQL, actually stands for Procedural Language extensions to SQL. While we re mentioning stored procedures, the XQuery equivalent to SQL s stored procedures and user-defined functions (Persistent Stored Modules, as they are called in ANSI SQL) is user-defined functions. XQuery allows for implementation- dependent extensions to store and access user-defined functions in libraries. XQuery s functions are supposed to be side effect free. All of SQL s PSMs are not guaranteed to be side effect free, although SQL user- defined functions can make this guarantee. In conclusion, the SQL SELECT statement has a lot in common with the XQuery language, though XQuery is more complex because of the underlying data model. XQuery has some of the functionality of data manipulation through the use of constructors, but no way to affect the underlying data. It more reflects the querying and reporting functionality of SQL SELECTand stored procedures. Using XQuery with the XML Data Type SQL Server supports XQuery on the XML data type directly. It does this through four of the XMLdata type s methods. xml.querytakes an XQuery query as input and returns an XMLdata type as a result.

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

XML QUERY LANGUAGES: XQUERY AND XPATH WHERE clause

XML QUERY LANGUAGES: XQUERY AND XPATH WHERE clause is based on existence (this is known as existential qualification), the SQL one is not. Both XQuery let and SQL s SET can be used for assignment, though let can be used as an adjunct to for inside a FLWOR expression. SET in SQL is usually a clause in a SQL UPDATE statement or a standalone statement in a SQL batch or a stored procedure. In fact, in Transact-SQL, SELECT can also be used for assignment. Though some of the matches are not 100% exact, it should not be a big stretch for SQL users to learn XQuery. The ease of adoption by SQL programmers, rather than data models served, is probably the reason for relational database vendors (or for that matter, OODBMS vendors, since XQuery also resembles OQL) to embrace XQuery over the functionally equivalent XSLT language. SQL and XQuery differ with respect to their data models. Attributes (column values) in SQL usually have two possible value spaces: an atomic value or NULL. Having NULL complicates some of the SQL operations; for example, NULLs are not included by default in aggregate operations. XQuery has the NULL sequence (a sequence with zero items), which has some of the same semantics (and complications) as the SQL NULL. In addition, XQuery has to deal with complications of nodes versus atomic values as well as the complications of possibly hierarchical nodes or multivalue sequences. This makes some of the rules for XQuery quite complex. As an example, value comparisons between unlike data types in SQL are not possible without casting (although SQL Server performs implicit casting in some cases). Comparing a value with NULL (including NULL) is always undefined. This rule could be stated in two sentences with two cases. Contrast this to the rules for value comparison in XQuery. 1. Atomization is applied to each operand. If the result is not an empty sequence or a single atomic value, a type error is raised. 2. If either operand is an empty sequence, the result is an empty sequence. 3. If either operand has the most specific type xdt:untypedAtomic, that operand is cast to a required type, which is determined as follows: 4. If the type of the other operand is numeric, the required type is xs:double. 5. If the most specific type of the other operand is xdt:untypedAtomic, the required type is xs:string. 6. Otherwise, the required type is the type of the other operand.

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

COMPARING AND CONTRASTING XQUERY AND SQL SQL currently

COMPARING AND CONTRASTING XQUERY AND SQL SQL currently is a more complete language than XQuery with regard to its use. SQL can be used for data definition (DDL), data manipulation (INSERT, UPDATE, and DELETE), and queries (SELECT). XQuery delegates its DDL to XML Schema, and the rest of XQuery equates to the SQL SELECT statement, although Microsoft and others have proposed data manipulation extensions to XQuery, and data manipulation extensions are part of SQL Server 2005. So we ll compare SQL SELECT and XQuery s FLWOR expressions. The clauses of a SQL SELECTstatement have an almost one-for-one correspondence in function to the clauses of a FLWOR statement, as is shown in Figure 9-3. Both SQL s SELECT and FLWOR s return statements enumerate data to be returned from the query. SELECT names columns in a resultset to be returned, whereas return can name a node or an atomic value to be returned. Fairly complex nodes can be returned using, for example, element constructors and nested elements, but items in a sequence are what is being returned nevertheless. Another similarity is that both return and SELECTcan be nested in a single statement. SQL s FROM clause lists the tables from which the data will be selected. Each row in each table is combined with each row in other tables to yield a Cartesian product of tuples, which make up the candidate rows in the resultset. XQuery s for works exactly the same way. Multiple clauses in a for statement, separated by commas produce a Cartesian product of candidate tuples as well. Because a for clause in XQuery can contain a selection using XPath, for also has some of the functionality of a SQL WHERE clause; this is especially noticeable when the XPath predicate is used. Both SQL and XQuery do support WHERE and ORDER BY; although the XQuery SQL FLWOR SELECT a,b,c RETURN a,b,c FROM tablex,tabley FOR $b in doc( x ), FOR $y in doc( y ) SET @z = 20 LET $z = 20 WHERE WHERE ORDER BY ORDER BY Figure 9-3: SQL SELECT and FLWOR

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 Quantified

XML QUERY LANGUAGES: XQUERY AND XPATH Quantified expressions Where one element satisfies a condition Expressions involving types Cast, instance of, treat, and typeswitch Finally, let s explore the question of what type of data models (XML can represent many different data models) should XQuery be used on? Some authors have attempted to make a distinction between XQuery and XSLT based on the premise that although XSLT and XQuery can be used for almost all the same functions (at least if you compare XSLT 2.0 and XQuery 1.0), XSLT with its template mechanism is better for data-driven transformations, while XQuery is better suited for queries. We find it instructive to look at the XQuery use cases document to see what the inventors of XQuery had in mind. The use cases mention many different kinds of data. Experiences and exemplars General XML Tree Queries that preserve hierarchy Sequence Queries based on document ordering Relational Relational data (representing tables as XML documents) SGML Queries against SGML data String String search (a.k.a. full-text search) Queries using namespaces Parts explosion Object and Codasyl databases usually do this well Strong types Queries that exploit strongly typed data Full-text use cases (separate specification) So it appears that XQuery can be useful with all kinds of data; however, particular implementations may not implement certain features that their data types cannot take advantage of. We ll see this point when we look at the XQuery implementation inside SQL Server. Comparing and Contrasting XQuery and SQL SQL (specifically, Transact-SQL) is the native language of SQL Server. SQL Server programmers have used it exclusively, until the CLR language becomes available in SQL Server 2005. These same programmers are probably not familiar with XQuery, an XML-centric query language that is still a work in progress, and it is instructive to compare the two languages to make SQL programmers more at home.

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

AN INTRODUCTION TO XQUERY query orders the returned

AN INTRODUCTION TO XQUERY query orders the returned items by the text of the lastNamesubelement of the name. for $p in doc( mydocument.xml )/people/person[age > 50] order by $p/name/lastName/text() descending return $p/name/firstName/text() The return keyword defines what the sequence that constitutes the return value will look like. In the last few queries, we have only been returning information derived from tuples as simple items. But items can be composed from scratch or using existing tuple information using node constructors. Node constructors can use simple literal strings with individual items separated by commas, but they can also use calculated values enclosed in curly braces. An example follows. { for $i in doc( catalog.xml )//item, $p in doc( parts.xml )//part[partno = $i/partno], $s in doc( suppliers.xml )//supplier[suppno = $i/suppno] order by $p/description, $s/suppname return { $p/description, $s/suppname, $i/price } } Notice that this example uses construction twice. The element is constructed by simply using it as a literal. The s in the return clause are constructed with a literal, but all subelements are constructed by evaluation of the expressions between the curly braces. Here s a list of some of the other types of expressions available in XQuery: Function calls Expressions combining sequences Arithmetic and logic expressions Comparisons Content, identity, and order based

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 for

XML QUERY LANGUAGES: XQUERY AND XPATH The for and let keywords are used to set up a stream of tuples, each tuple containing one or more bound variables. The for keyword is used for iteration. In the following query: for $p in doc( mydocument.xml )/people/person let $a := $p/age where $a > 50 return $p/name/firstName/text() the for statement uses an XPath expression to select a sequence of items. The for statement iterates over all the items that match the XPath expression, assigning a different item to the variable $p, one value for each time around the loop. For example, if the sequence produced is ( , ), the loop is executed three times, with $p assigned to one node for each iteration. The let keyword is used by assignment. The expression on the right side of the assignment is assigned to the variable in its entirety. The assignment takes place whether the value is a single item (as in the let $a := $p/age statement in the preceding query) or an entire sequence. To show the difference between iteration and assignment, consider the following two queries. for $p in (1,2), $q in (3,4) let $a := (7,8,9) return ($p, $q, $a) (: this returns (1,3,7,8,9),(1,4,7,8,9), (2,3,7,8,9), (2,4,7,8,9) :) for $p in (1,2) let $a := (7,8,9), $q := (3,4) return ($p, $q, $a) (: this returns (1,3,4,7,8,9),(2,3,4,7,8,9) :) You can use the where keyword to filter the resulting tuples. You can filter based on the value based on any XQuery expression. Interestingly, in many situations you could use an XPath predicate in the for statement to achieve the same effect as using a wherekeyword. For example, the earlier query that looks for people could be just as easily expressed as this. for $p in doc( mydocument.xml )/people/person[age > 50] return $p/name/firstName/text() The order by keyword changes the order of the returned tuples. Usually, tuples are returned in the order of the input, which often is the document order (which we defined previously in this chapter). The following

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

AN INTRODUCTION TO XQUERY seller nodes, description nodes,

AN INTRODUCTION TO XQUERY seller nodes, description nodes, and all others, we could change the path expression to the following. doc( data/test/items.xml )/items/itm/* Each evaluation step has an axis; in our simple query, we have let the axis default to child by leaving it out. The same query could be expressed this way. doc( data/test/items.xml )/child::items/child::itm/child::* Any other valid axis could also be used. Another common abbreviation is to abbreviate the attribute axis to @. The evaluation step /attribute:: status in a query against the document above could be abbreviated to /@status. Although XPath has much more functionality and a function library of its own (that can be used in path expressions in XQuery) in addition to being able to use the XPath and XQuery operators and functions, we ll finish by discussing XPath predicates. After you filter any evaluation step by using a node test, you can filter the results further by using a query predicate. Query predicates follow the node test in square brackets. For example, this predicate filters itm elements whose itemno subelement has a text subelement with a value less than 1000. doc( data/test/items.xml )/items/itm[itemno/text() < 1000] A predicate can be as simple or complex as you want, but it must evaluate to a boolean value. More exactly, it is evaluated using the Effective Boolean Value (essentially, the rules for fn:boolean) for example, /a[1]. You can even have multiple predicates on the same evaluation step if you want. The heart of XQuery expressions, what forms most of the selection logic in an XQuery query, is called the FLWOR expressions. FLWOR is an acronym for the five main expression keywords: for, let, where, order by, and return. We ve already seen the letexpression, to assign a value to a variable, and the returnexpression, for returning the value of a variable in the query. let $a := doc( data/test/items.xml ) return $a We ll briefly define the use of each expression. Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Tomcat Web Hosting services

XML QUERY LANGUAGES: XQUERY AND XPATH The

XML QUERY LANGUAGES: XQUERY AND XPATH The attribute axis contains the attributes of the context node; the axis will be empty unless the context node is an element. The self axis contains just the context node itself. The descendant-or-self axis contains the context node and the descendants of the context node. Figure 9-2 shows the nodes that make up the main XPath axes for a particular document, given a particular context node. The context node that we are referring to here is the starting point of the evaluation step. In our earlier simple XPath expression, the starting point of the first evaluation step was the document node, a concrete node. In the XQuery data model, this is the root node (that is, the root node returned by fn:root()). The root node is the items node in this example document. In the second evaluation step, the items node would be the context node; in the third evaluation step, each itm node would be the context node in turn; and so on. Each evaluation step can filter the resulting sequence using a node test that can filter by name or by node type. For example, in the last evaluation step, only children of itm named itemno will be selected. If we wanted to select all the children of itm nodes, include XPath Axes dlihcE tnadnecsedH,FB rotsecnaH,G,Fgniwolloftoor,A,BgnidecerpN,M,L,K,J,I C,Dflestnerapfles-ro-tnadnecsed,G,F,EH fles-ro-rotsecnatoor,A,B,Eerpc de bisgnignilC gnilbis-gniwollofJ,I C E I A B M toorF G H J K L N D E is the context node Figure 9-2: XPath Axes

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