SPECIAL CONSIDERATIONS WHEN USING XQUERY Special Considerations When

SPECIAL CONSIDERATIONS WHEN USING XQUERY Special Considerations When Using XQuery inside SQL Server Using Strongly Typed XML with XQuery Unlike XPath 1.0, XPath 2.0 and XQuery 1.0 are strongly typed query languages. This is quite evident in the wide range of constructors and casting functions for different data types. The reasoning behind this is that XQuery is built to be optimized, and using strong types rather than using every element and attribute as type stringallows the query engine to do a better job in optimizing and executing the query. Imagine that the Transact- SQL query parser had to deal with everything as a single data type! Every XML data type column, XML variable, and XML procedure parameter can be strongly typed by reference to an XML schema or can be untyped. When typed XML is used in an XQuery, the query parser has more information to work with. If untyped XML is used, the query engine must start with the premise that every piece of data is type string; the xf:data()built-in function can be used with strongly typed data to return the exact data type. XQuery supports static type analysis, meaning that if the types of items that are input to an expression are known, the output type of an expression can be inferred at parse time. In addition, strong typing permits the XQuery language to syntax check the input based on types at parse time, as Transact-SQL can, so fewer runtime errors occur. XQuery also allows the query processor to reorder the queries; although this is a performance optimization, this could on occasion lead to runtime errors. You ve seen a very noticeable example of SQL Server XQuery s strong typing in almost all the example queries in this chapter. In these queries, whenever a function or return value requires a singleton, we must use the [1]subscript to ensure it s really a singleton; otherwise, a parse-time error results. Almost all other XQuery processors (without strong typing) will execute the query and produce a runtime error if the value is not actually a singleton. If we had a strongly typed XML column instead, in which the schema defined the elements we re referring to as maxOccurs=1 minOccurs=1 (the default), a query without the subscript would succeed, because the parser knows that the element in question is a singleton. Having a schema-validated column permits you to do the type-checking at column insert/update time, rather than take the extra measure of requiring a subscript to enforce it at XQuery parse time.

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 with Expression2

XML QUERY LANGUAGES: XQUERY AND XPATH with Expression2 Expression1 must return a single node; if a sequence of zero nodes or multiple nodes is returned, an error is produced. Note that, again, this is unlike a SQL searched UPDATE statement in SQL, where returning zero rows to update is not an error. It is more similar to UPDATEWHERECURRENT OF in SQL when using a cursor. Expression1 is used to find the current node. Expression2 must be a sequence of atomic values. If it returns nodes, they are atomized. The sequence in Expression2 completely replaces the node in Expression1. Here s an example that updates the CustomerName element s textchild element from Jane Smith to John Smith . SET @x.modify( update value of /Invoice[1]/CustomerName[1]/text()[1] to John Smith ) General Conclusions and Best Practices XML DML can be used to update portions of an XML data instance in place. The insert, update, and delete can be composed as part of a transaction. This is a unique ability for XML; although XML is often compared to databases and complex types, the usual pattern was to retrieve the XML, update the corresponding XML DOM (or in .NET, XPathNavigator), and then write the entire document back to the file system. XML DML treats XML more like a complex type or a database and as similar to functionality found in XML-specific databases. There are a few general rules to remember when using XML DML. The nodes referenced by modify operations must be nodes in the original document. XML DML is not analogous to SQL DML. Although insertand deleteare similar, position is all important. For SQL programmers, it is the equivalent of using an updateable cursor. The resulting XML document or fragment cannot be malformed, or the xml.modifyoperation will fail. If the XML instance is validated by an XML schema, the xml.modify operation cannot interfere with schema validation.

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 DML UPDATING XML COLUMNS 153 5 Vanilla Ice

XML DML UPDATING XML COLUMNS 153 5 Vanilla Ice Cream 1.50 154 20 Chocolate Ice Cream 1.50 go xml.modify( delete . . . ) The deleteXML DML command, as input to the modifyfunction, deletes zero or more nodes that are identified by the output sequence of the XQuery query following the keyword delete. As in SQL, you can qualify the deletestatement with a whereclause. The general syntax of deleteis: delete Expression Each node returned by Expressionis deleted. Returning a sequence of zero nodes just deletes zero nodes; it is not an error. As with the insert command, attempting to delete a constructed node (a node that was produced earlier in the query rather than a node in the original document) will cause an error. Attempting to delete a value rather than a node will result in an error. Also, attempting to delete a metadata attribute, such as a namespace declaration, will result in an error. To delete all the LineItem elements in our example, you could execute the following statement: set @x.modify( delete /Invoice/LineItems/LineItem ) xml.modify( replace value of . . . ) Unlike a searched UPDATE in SQL and also unlike xml.modify ( delete… ), xml.modify( replacevalueof… )modifies the value of a single node. It is not a searched UPDATE that uses a WHERE clause to select a sequence of nodes. The general syntax for update follows. replace value of Expression1

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 Chocolate Ice

XML QUERY LANGUAGES: XQUERY AND XPATH Chocolate Ice Cream 1.50 ) into /Invoice[1]/LineItems[1] ) The afterand beforekeywords are used to insert siblings at the same level of hierarchy in the document. These keywords cannot be used in the same statement as into; this produces an error. It is also an error to use the afterand beforekeywords when inserting attributes. Following our earlier example, if you want to set the InvoiceDate at a specific position in the Invoice element s set of children, you need to use the before or after keywords and have an XPath expression that points to the appropriate sibling. SET @x.modify( insert 2002-06-15 before /Invoice[1]/CustomerName[1] ) this works too, and equates to the same position SET @x.modify( insert 2002-06-15 after /Invoice[1]/InvoiceID[1] ) The key to understanding the insert statement is that although Expression1can be any of the seven node types and can contain multiple nodes in a sequence or even hierarchical XML, Expression2 must evaluate to a single node. If Expression2 evaluates to a sequence of nodes and no node, the insert statement will fail. In addition, Expression2 cannot refer to a node that has been constructed earlier in the query; it must refer to a node in the original XML instance. This is what the variable looks like after all the previous modifications. Although InvoiceDate was used multiple times in multiple examples, we ve chosen to show only the insert position from the last example (ignoring the first two examples, where InvoiceDatewas inserted in a different position). value in the variable @x after modifications 1000 2002-06-15 Jane Smith 134 10 Chicken Patties 9.95

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 DML UPDATING XML COLUMNS 134 10 Chicken Patties

XML DML UPDATING XML COLUMNS 134 10 Chicken Patties 9.95 153 5 Vanilla Ice Cream 1.50 GO You could insert a new InvoiceDateelement as a child of the Invoice element using the following statement. SET @x.modify( insert 2002-06-15 into /Invoice[1] ) This statement would insert the InvoiceDate element as the last child of Invoice, after LineItems. To insert it as the first child of Invoice, simply change the statement to the following. SET @x.modify( insert 2002-06-15 as first into /Invoice[1] ) Here s an example of inserting an attribute, status= backorder , on the Invoiceelement. SET @x.modify( insert attribute status{ backorder } into /Invoice[1] ) Notice that this uses the constructor evaluation syntax (curly braces) to define the value of the attribute (backorder). You can also insert an entire series of elements using the insertstatement. For example, if you wanted to add a new LineItem, you would do the following. SET @x.modify( insert ( 154 20

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

XML QUERY LANGUAGES: XQUERY AND XPATH NULL, you

XML QUERY LANGUAGES: XQUERY AND XPATH NULL, you must use the normal XMLdata type construction syntax. With the xml.modify function, you can use XQuery syntax with the addition of three keywords: insert, delete, and replacevalueof. Only one of these keywords may be used in a single XML DML statement. xml.modify( insert . . . ) You use the XML DML insert statement to insert a single node or an ordered sequence of nodes into the XML data type instance as children or siblings of another node. The node or sequence to be inserted can be an XML or XQuery expression, as can the target node that defines the position of the insert. The general format of the insertstatement is as follows: insert Expression1 {as first | as last} into | after | before Expression2 where Expression1is the node or sequence to be inserted and Expression2 is the insert target. Any of the seven node types and sequences of those types may be inserted as long as they do not result in an instance that is malformed XML. Remember that well-formed document fragments are permitted in XML data types. The keywords asfirst, aslast, or intoare used to specify inserting child nodes. Using the into keyword inserts the node or sequence specified in Expression1as a direct descendant, without regard for position. It is usually used to insert child nodes into an instance where no children currently exist. Using as first or as last ensures that the nodes will be inserted at the beginning or end of a sequence of siblings. These are usually used when you know that the node already contains child nodes. These keyword are ignored and do not cause an error when attribute nodes are being inserted. When processing instruction or comment nodes are being inserted, child refers to the position in the document rather than a real parent-child relationship. Let s start with an instance of an XML invoice and mutate the invoice. Here s our starting point. declare XML variable and set its initial value DECLARE @x xml SET @x = 1000 Jane Smith

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 DML UPDATING XML COLUMNS implementation of XQuery) inside

XML DML UPDATING XML COLUMNS implementation of XQuery) inside SQL Server. With the client-side implementation of XQuery, the input, document, and collection functions are supported, and multiple documents (or multiple instances of the XML data type) can be passed in as input parameters to a .NET stored procedure. We ll look at the .NET client XML APIs in Chapter 13. XML DML Updating XML Columns One thing that was left out of the XQuery 1.0 specification was a definition of an XQuery syntax for mutating XML instances or documents in place. A data manipulation language (DML) is not planned for the first version of XQuery, but a working draft is under development. Because SQL Server 2005 will use XQuery as the native mechanism for querying the XML data type inside the server, it is required to have some sort of manipulation language. The alternative would be to only be able to replace the instance of the XML type as an entire entity. Since changes to XML data type instances should participate in the current transaction context, this would be equivalent to using SELECT and INSERT in SQL without having a corresponding UPDATEstatement. Therefore, SQL Server 2005 introduces a heretofore nonstandard implementation of XML DML. XML DML is implemented using XQuery-like syntax with SQL-like extensions. This emphasizes the fact that manipulating an XML instance inside SQL Server is equivalent to manipulating a complex type or, more accurately, a graph of complex types. You invoke XML DML by using the xml.modifyfunction on a single XMLdata type column, variable, or procedure parameter. You use XML DML within the context of a SQL SET statement, using either UPDATE…SETon an XMLdata type column or using SET on an XML variable or parameter. As a general rule, it would look like this. change the value of XML data type column instance UPDATE some_table SET xml_col.modify( some XML DML ) WHERE id = 1 change the value of an XML variable DECLARE @x XML initialize it SET @x = initial XML now, mutate it SET @x = @x.modify( some XML DML ) Note that this syntax is used only to modify the XML nodes contained in an existing instance of an XML data type. To change a value to or from

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 sql:variable The

XML QUERY LANGUAGES: XQUERY AND XPATH sql:variable The sql:variable function allows you to use any Transact-SQL variable that is in scope at the time the XQuery query is executed. This will be a single value for the entire query, as opposed to the sql:column, where the column value changes with every row of the result. This function is subject to the same data type limitations as sql:column. An example of using sql:variablein an XQuery query would look like this. DECLARE @occupation VARCHAR(50) SET @occupation = is a stooge SELECT xml_col.query( for $b in //person where $b/name= moe return

  • { $b/name/text() } { sql:variable( @occupation ) }
  • ) FROM xml_tab This statement uses the value of a T-SQL variable, @occupation, in the returned sequence in the XQuery query. Multiple Document Query in SQL Server XQuery As we saw in the XQuery specification discussion earlier in this chapter, queries can encompass more than one physical XML document, by using the doc or collection functions. However, in SQL Server s implementation of XQuery, the functions that allow input are not implemented. This is because the XQuery functions are implemented as instance functions that apply to a specific instance of the XML data type. Using the instance functions (xml.query, xml.exist, and xml.value), the XQuery query is single-document-only. So what if you want to combine more than one document, the XQuery equivalent of a SQL JOIN? One way to combine multiple documents to produce a single XQuery sequence as a result is to perform the query over each document separately and concatenate the results. This can be easily accomplished using the SELECT…FOR XML syntax. This may not always be what you want, however. Some multidocument queries are based on using one document to join another in a nested for loop. These types of queries cannot be accomplished by using sequence concatenation. Another way to accomplish multidocument queries is to use the xml.queryfunction on different types, combined with the SQL JOIN syntax. This doesn t deal with the nested tuple problem either. Because .NET stored procedures and UDFs can use most of the .NET APIs, it is possible to use System.Xml.Query (the client-side

    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 two

    USING XQUERY WITH THE XML DATA TYPE two extension functions for exactly this purpose: sql:column and sql: variable. These keywords allow you to refer to relational columns in tables and Transact-SQL variables, respectively, from inside an XQuery query. These functions can be used anywhere an XQuery query can be used, namely xml.exist, xml.value, xml.query, and xml.modify; xml.modify is discussed in the next section. The functions cannot be used to assign a value to SQL data; that means that they cannot be used on the left-hand side of an assignment statement in XQuery, as in let sql:column := somevalue . The functions can refer to any SQL data type with the following exceptions: The XMLdata type User-defined data types TIMESTAMP UNIQUEIDENTIFIER TEXTand NTEXT IMAGE sql:column This function refers to a column in the current row of the current SQL query. The column can be in the same table as the XMLdata type column, or it can be included in the result as part of a join. The table must be in the same database and owned by the same schema owner, and is specified using a two-part name in the format Tablename.Columnname (without the quotes). Here s an example, using the table with name as a subelement that we ve been using in the XQuery xml.nodesexamples. SELECT xml_col.query( for $b in //person where $b/name= moe return

  • { $b/name/text() } in record number {sql:column( xml_tab.the_id )}
  • ) FROM xml_tab This returns the following result (note: the first two rows in the result are empty).

  • moe in record number 3
  • moe in record number 4
  • 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 box

    XML QUERY LANGUAGES: XQUERY AND XPATH The box titled XQuery Operators, Expressions, and Constructs Supported by SQL Server lists all the operators and expressions supported by SQL Server 2005 s implementation of XQuery. The list may change slightly by the time of release. XQuery Operators, Expressions, and Constructs Supported by SQL Server Supported Operators Numeric operators: +, -, *, / Value comparison operators: eq, ne, lt, gt, le, ge General comparison operators: =, !=, < , >, < =, >= Supported Expressions and Constructs Prolog with namespace declarations and schema imports Node comparison Order comparison Path expressions Constructors with evaluation Element and attribute dynamic constructors FWOR (no let) Conditional (if-then-else) Quantified expressions Note also that although the specification defined that each standard function live in a specific namespace, SQL Server s XQuery engine does not require the namespace prefix. This saves on required namespace declarations in every query. SQL Server XQuery Extended Functions Because the XQuery engine is executing in the context of a relational database, it is convenient to provide a standard way to use non-XML data (that is, relational data) inside the XQuery query itself. SQL Server provided

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