XML IN THE DATABASE: THE XML DATA TYPE

XML IN THE DATABASE: THE XML DATA TYPE to a CLOB (VARCHAR(MAX)), but you must convert it to and from a varchar type, rather than assign it. Like distinct types, the XML data type cannot be compared with other data types without being cast or converted, but unlike distinct types, two instances of an XML data type cannot be compared at all. Like a SQL:1999 distinct type, the XML type has its own methods; these methods enable the use of an alternate query language, XQuery. The data in an XMLtype does not follow the relational data model, but is based on the XML Infoset model, which is used to model structured that is, hierarchical data. A column that is defined as being of type XMLstores its data in the database itself. The column is not a pointer to an XML document on the file system. This means that XML data is included in the backup and restore process, is subject to ordinary SQL Server security (and some extensions to security, as we ll mention later), and participates in transactions, constraints, and logging. Having XML data inside a relational database may offend some relational purists, but it means that your data lives in a single repository for reasons of administration, reliability, and control. Using the XML Data Type in Tables Let s begin by using the XML data type to define a column in a table. The following DDL statement creates a table. CREATE TABLE xml_tab ( the_id INTEGER, xml_col XML) Note that you can also have a table that consists of only a single XML data type column; however, the XMLdata type cannot be used itself as a primary key. The following code, which attempts to use the XML column as a primary key, fails. CREATE TABLE xml_tab ( xml_col XML PRIMARY KEY) Interestingly, the following error message occurs when you try to make an XMLdata type the primary key: Column xml_col in table xml_tab is of a type that is invalid for use as a key column in an index. Later we will see that you can create an XML-specific index on this column, but this index will not be used in SQL comparisons it will be

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

8 XML in the Database: The XML Data

8 XML in the Database: The XML Data Type SQL SERVER 2005 introduces a new scalar data type, the XMLdata type. XML is a first-class data type in SQL Server now, and this has a wide- ranging impact on the use of XML. The XML Data Type A new type of data has gained popularity in recent years, XML. XML has evolved from a simple data transfer format to a data model that includes its own schema-definition vocabulary, XSD, as well as query languages. In this chapter, we ll look at the XMLdata type and see how it differs from conventional CLOB(TEXTfield) storage of an XML document. You can use the XMLdata type like any other data type in SQL Server. It can be used in the following ways: As a column in a table As a variable in Transact-SQL As a stored procedure or user-defined function parameter As a user-defined function return value The XML type is quite similar, but not identical, to the distinct type defined by SQL:1999 and discussed in Chapter 1. It is similar in character

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

T-SQL ENHANCEMENTS CATCH block. TRY/CATCH constructs can be

T-SQL ENHANCEMENTS CATCH block. TRY/CATCH constructs can be nested, so to handle exceptions within a CATCH block, write a TRY/CATCH block inside the CATCH. The following code shows a simple example of the TRY/CATCHblock. make sure we catch all errors SET XACT_ABORT ON BEGIN TRY start the tran BEGIN TRAN do something here COMMIT TRAN END TRY BEGIN CATCH TRAN_ABORT ROLLBACK cleanup code END CATCH Notice how the first statement in the CATCH block is the ROLLBACK. It is necessary to do the ROLLBACK before any other statements that require a transaction. This is because SQL Server 2005 has a new transactional state: failed or doomed. The doomed transaction acts like a read-only transaction. Reads may be done, but any statement that would result in a write to the transaction log will fail with error 3930: Transaction is doomed and cannot make forward progress. Rollback Transaction. However, work is not reversed and locks are not released until the transaction is rolled back. We mentioned previously that errors had to be transactional abort errors in order to be caught. This raises the question: What about errors created through the RAISERROR syntax in other words, errors that you raise yourself? In SQL Server 2005, RAISERROR has a new option called TRAN_ABORT, which tags the raised error as a transactional abort error, which therefore will be handled in the CATCHblock. Where Are We? With the inclusion of the CLR in SQL Server 2005 and the ability to use .NET languages natively from within SQL Server, there has been speculation on the future of T-SQL. T-SQL continues to be advanced and remains the best (and in some cases the only) way to accomplish many things. We firmly believe that the enhancements to T-SQL in this release of SQL Server show the importance of T-SQL and its power and future.

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

TRANSACTION ABORT HANDLING DATEDIFF(yy,birthdate,getdate())AS age FROM employees )

TRANSACTION ABORT HANDLING DATEDIFF(yy,birthdate,getdate())AS age FROM employees ) AS a produces lastname country age rank oldest age in country – – – Dodsworth UK 37 1 48 Suyama UK 40 2 48 King UK 43 3 48 Buchanan UK 48 4 48 Leverling USA 40 1 66 Callahan USA 45 2 66 Fuller USA 51 3 66 Davolio USA 55 4 66 Peacock USA 66 5 66 Transaction Abort Handling Error handling in previous versions of SQL Server has always been seen as somewhat arcane, compared with other procedural languages. You had to have error handling code after each statement, and to have centralized handling of errors, you need GOTO statements and labels. SQL Server 2005 introduces a modern error handling mechanism with TRY/CATCH blocks. The syntax follows. BEGIN TRY { sql_statement | statement_block } END TRY BEGIN CATCH TRAN_ABORT { sql_statement | statement_block } END CATCH The code you want to execute is placed within a TRY block. The TRY block must be immediately followed by a CATCH block in which you place the error handling code. The CATCH block can only handle transaction abort errors, so the XACT_ABORT setting needs to be on in order for any errors with a severity level less than 21 to be handled as transaction abort errors. Errors with a severity level of 21 or higher are considered fatal and cause SQL Server to stop executing the code and sever the connection. When a transaction abort error occurs within the scope of a TRY block, the execution of the code in the TRY block terminates and an exception is thrown. The control is shifted to the associated CATCHblock. When the code in the CATCH block has executed, the control goes to the statement after the

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

T-SQL ENHANCEMENTS FROM orders ) AS a WHERE

T-SQL ENHANCEMENTS FROM orders ) AS a WHERE num BETWEEN 400 AND 410 Although the preceding case looks similar to selecting the entire result- set into a temporary table, with num as a derived identity column, and doing a SELECT of the temporary table, in some cases the engine will be able to accomplish this without the complete set of rows. Besides being usable in a SELECT clause, the ranking and windowing functions are also usable in the ORDER BYclause. This gets employees partitioned by country and ranked by age, and then sorted by rank. SELECT *, RANK() OVER(PARTITION BY COUNTRY ORDER BY age)) AS [rank] FROM ( SELECT lastname, country, DATEDIFF(yy,birthdate,getdate())AS age FROM employees ) AS a ORDER BY RANK() OVER(PARTITION BY COUNTRY ORDER BY age), COUNTRY produces lastname country age rank – – Dodsworth UK 37 1 Leverling USA 40 1 Suyama UK 40 2 Callahan USA 45 2 King UK 43 3 Fuller USA 51 3 Buchanan UK 48 4 Davolio USA 55 4 Peacock USA 66 5 You can also use other aggregate functions (either system-defined aggregates or user-defined aggregates that you saw in Chapter 5) with the OVER clause. When it is used in concert with the partitioning functions, however, you get the same value for each partition. This is shown next. there is one oldest employee age for each country SELECT *, RANK() OVER(PARTITION BY COUNTRY ORDER BY age) AS [rank], MAX(age) OVER(PARTITION BY COUNTRY) AS [oldest age in country] FROM ( SELECT lastname, country,

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

T-SQL LANGUAGE ENHANCEMENTS SELECT lastname, country, DATEDIFF(yy,birthdate,getdate())AS age

T-SQL LANGUAGE ENHANCEMENTS SELECT lastname, country, DATEDIFF(yy,birthdate,getdate())AS age FROM employees ) AS a produces lastname country age rank – – Dodsworth UK 37 1 Suyama UK 40 2 King UK 43 3 Buchanan UK 48 4 Leverling USA 40 1 Callahan USA 45 2 Fuller USA 51 3 Davolio USA 55 4 Peacock USA 66 5 There are separate rankings for each partition. An interesting thing to note about this example is that the subselect is required because any column used in a PARTITIONBYor ORDERBYclause must be available from the columns in the FROM portion of the statement. In our case, the seemingly simpler statement that follows: SELECT lastname, country, DATEDIFF(yy,birthdate,getdate())AS age, RANK() OVER(PARTITION BY COUNTRY ORDER BY age) AS [rank] FROM employees wouldn t work; instead, you d get the error Invalid column name age . In addition, you can t use the ranking column in a WHEREclause, because it is evaluated after all the rows are selected, as shown next. 10 rows to a page, we want page 40 this won t work SELECT ROW_NUMBER() OVER (ORDER BY customerid, requireddate) AS num, customerid, requireddate, orderid FROM orders WHERE num BETWEEN 400 AND 410 this will SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY customerid, requireddate) AS num, customerid, requireddate, orderid

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

T-SQL ENHANCEMENTS 10297 BLONP 8 10360 BLONP 8

T-SQL ENHANCEMENTS 10297 BLONP 8 10360 BLONP 8 … more rows Note that multiple rows have the same rank if their customerid is the same. There are holes, however, in the rankcolumn value to reflect the ties. Using the DENSE_RANK()function works the same way as RANK()but gets rid of the holes in the numbering. NTILE(n) divides the resultset into n approximately even pieces and assigns each piece the same number. NTILE(100) would be the well-known (to students) percentile. The following query shows the difference between ROW_NUMBER(), RANK(), DENSE_RANK(), and TILE(n). SELECT orderid, customerid, ROW_NUMBER() OVER(ORDER BY customerid) AS num, RANK() OVER(ORDER BY customerid) AS [rank], DENSE_RANK() OVER(ORDER BY customerid) AS [denserank], NTILE(5) OVER(ORDER BY customerid) AS ntile5 FROM orders WHERE orderid < 10400 AND customerid <= BN produces orderid customerid num rank denserank ntile5 - - 10308 ANATR 1 1 1 1 10365 ANTON 2 2 2 1 10355 AROUT 3 3 3 2 10383 AROUT 4 3 3 2 10278 BERGS 5 5 4 3 10280 BERGS 6 5 4 3 10384 BERGS 7 5 4 4 10265 BLONP 8 8 5 4 10297 BLONP 9 8 5 5 10360 BLONP 10 8 5 5 The ranking functions have additional functionality when combined with windowing functions. Windowing functions divide a resultset into partitions, based on the value of a PARTITION BY clause inside the OVER clause. The ranking functions are applied separately to each partition. Here s an example. SELECT *, RANK() OVER(PARTITION BY COUNTRY ORDER BY age) AS [rank] FROM ( Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services

T-SQL LANGUAGE ENHANCEMENTS ROW_NUMBERproduces a monotonically increasing number;

T-SQL LANGUAGE ENHANCEMENTS ROW_NUMBERproduces a monotonically increasing number; that is, no rows will ever share a ROW_NUMBER. SELECT orderid, customerid, ROW_NUMBER() OVER(ORDER BY customerid) AS num FROM orders WHERE orderid < 10400 AND customerid <= BN produces orderid customerid num - 10308 ANATR 1 10365 ANTON 2 10355 AROUT 3 10383 AROUT 4 10384 BERGS 5 10278 BERGS 6 10280 BERGS 7 10265 BLONP 8 10297 BLONP 9 10360 BLONP 10 RANK() applies a monotonically increasing number for each value in the set. The value of ties, however, is the same. If the columns in the OVER(ORDER BY ) clause have unique values, the result produced by RANK() is identical to the result produced by ROW_NUMBER(). RANK() and ROW_NUMBER() differ only if there are ties. Here s the second earlier example using RANK(). SELECT orderid, customerid, RANK() OVER(ORDER BY customerid) AS [rank] FROM orders WHERE orderid < 10400 AND customerid <= BN produces orderid customerid rank - 10308 ANATR 1 10365 ANTON 2 10355 AROUT 3 10383 AROUT 3 10384 BERGS 5 10278 BERGS 5 10280 BERGS 5 10265 BLONP 8 Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Cheap Web Hosting services

T-SQL ENHANCEMENTS id not mentioned in pivot expression

T-SQL ENHANCEMENTS id not mentioned in pivot expression Properties grouped by id id color type amount ———————–1 blue oil 1 gal 3 red latex 1 qt 4 white oil 1 pt Pivoted properties of Swish product Figure 7-10: Results of Open Schema Pivot Ranking and Windowing Functions SQL Server 2005 adds support for a group of functions known as ranking functions. At its simplest, ranking adds an extra value column to the resultset that is based on a ranking algorithm being applied to a column of the result. Four ranking functions are supported. ROW_NUMBER() produces a column that contains a number that corresponds to the row s order in the set. The set must be ordered by using an OVER clause with an ORDER BY clause as a variable. The following is an example. SELECT orderid, customerid, ROW_NUMBER() OVER(ORDER BY orderid) AS num FROM orders WHERE orderid < 10400 AND customerid <= BN produces orderid customerid num - 10248 VINET 1 10249 TOMSP 2 10250 HANAR 3 10251 VICTE 4 10252 SUPRD 5 10253 HANAR 6 10254 CHOPS 7 10255 RICSU 8 ... more rows Note that if you apply the ROW_NUMBER function to a nonunique column, such as customerid in the preceding example, the order of customers with the same customerid (ties) is not defined. In any case, Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services

T-SQL LANGUAGE ENHANCEMENTS table, as was shown in

T-SQL LANGUAGE ENHANCEMENTS table, as was shown in Figure 7-7. What we want to do is to take selected rows from the Properties table and pivot them that is, rotate them and then add them as columns to the Product table. This is shown in Figure 7-8. Figure 7-9 shows the PIVOTwe will use to select the line from the Product table for Swish products and joint them with the corresponding pivoted lines from the Properties table. This query selects row from the Properties table that have a string equal to color , type , or amount in the value column. They are selected from the value column because value is the argument of the MAX function that follows the PIVOT keyword. The strings color , type , and amount are used because they are specified as an argument of the INclause after the FOR keyword. Note that the arguments of the INclause must be literal; there is no way to calculate them for example, by using a subquery. The results of the pivot query in Figure 7-9 are shown in Figure 7-10. Note that the columns that were selected from the Properties table now appear as rows in the output. Product Table Pivot Column Turned into Row Swish Table Created from Product and Properties Tables Figure 7-8: Rotating Properties Pivot Value Column Column 1 color blue 1 type oil 1 amount 1 gal 2 pitch 12-3 2 diameter .25 in Value Column Pivot Column Make column where name = one of these Select only properties for the Swish product Properties Table Figure 7-9: Basic PIVOT Properties Table SELECT * FROM properties PIVOT ( MAX(value) FOR name IN ([color], [type], [amount]) ) AS P WHERE id IN (SELECT id FROM products WHERE name=’Swish’)

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