XML DATA AND DOCUMENT STORAGE constraints and declarative integrity

XML DATA AND DOCUMENT STORAGE constraints and declarative integrity constraints to ensure that an XML document is correct. A nonvalidated XML schema still must conform to XML well-formedness rules, and a single XML document adheres to a set of rules known as the XML Information Set (Infoset), consisting of structure and some content information. Validating an XML document against schemas produces what is called a Post-Schema-Validation InfoSet (PSVI). The PSVI information makes it possible to determine a strong, well-defined type for each XML element and attribute. SQL Server 2005 introduces an XML data type. This data type can be used in table definitions to type a column, as a variable type in Transact- SQL procedural code, and as procedure parameters. A definition of a simple table containing an XML type would look like a normal CREATE TABLEstatement. CREATE TABLE xml_tab( id INT primary key, xml_col XML) In addition, columns, variables, and parameters of the XML data type can be constrained by an XML schema. XML schemas are defined in the SQL Server catalog. XML, like relational databases, has its own query language optimized for the data format. Since XML data is hierarchical, it s reminiscent of a hierarchical file system. The archetypical query language for XML documents is known as XPath. Queries in XPath reflect the hierarchical nature of XML, since nodesets are selected by using syntax similar to that used to specify files in the UNIX file system. As an example, when a typical XML document is queried using a hierarchical XPath query, the result is a node- set containing all the nodes at that level of hierarchy. Listing 1-9 shows an example of an XPath query that, when run against the purchase order document in Listing 1-8, produces a nodeset result containing all the item elements. Think of an XPath query as analogous to a SQL query. Listing 1-9: Simple XPath Query < ! this query > //pre:Item < ! produces this nodeset > < ! two Item nodes >

   
Dons Boxers
11.95

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

INTRODUCTION An XML schema defines the namespace that

INTRODUCTION < ! global element definition using type above > An XML schema defines the namespace that its types belong to by specifying the targetNamespace attribute on the schema element. An XML document that uses types from a namespace can indicate this by using a default namespace or explicitly using a namespace prefix on each element or attribute of a particular type. Namespace prefixes are arbitrary; the xmlns attribute established the correspondence between namespace prefix and namespace. This is illustrated in Listing 1-8. This is analogous to using SQL Server two-part or three-part names in SQL statements. Listing 1-8: Referring to a Type via Namespace and Namespace Prefixes

  
1001
DM Traders
  
Dons Boxers
11.95
   
Essential ADO.NET
49.95

Only when an XML document contains types defined by XML schemas is it possible to determine the exact data types of elements or attributes. XML elements and attributes are data type string by definition. A predecessor to XML schemas, known as Document Type Definition (DTD), was primarily concerned with defining document structure and allowed only limited information about data types. XSD is a superset of the aforementioned type systems, including all the DTD structure types. Using an XSD schema or schemas to determine if a document is correct is known as schema validation. Schema validation can be thought of as applying 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 DATA AND DOCUMENT STORAGE Since subelements can be

XML DATA AND DOCUMENT STORAGE < ! and cannot themselves be complex types >

Since subelements can be nested in XML documents, a document more closely corresponds to a hierarchical form of data than a relational form. This is reinforced by the fact that, by definition, an XML document must have a single root element. Sets of elements that do not have a single root element are called document fragments. Although document fragments are not well- formed XML documents, multiple fragments can be composed together and wrapped with a root element, producing a well-formed document. In addition to being able to represent relational and hierarchical data, XML Schema Definition language can represent complex type relationships. XSD supports the notion of type derivation, including derivation by both restriction and extension. This means that XML can directly represent types in an object hierarchy. A single XML schema document (which itself is defined in an XML form specified by the XML Schema Definition language) represents data types that scope a single XML namespace, although you can use XML name- spaces in documents without having the corresponding XML schema. An XML namespace is a convenient grouping of types, similar to a user- schema in SQL Server. This is illustrated in Listing 1-7. Listing 1-7: An XML Namespace Defining a Set of Types < ! define a new type > < ! a nested anonymous 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

INTRODUCTION relational database. Because it was originally designed

INTRODUCTION relational database. Because it was originally designed as a system that could represent documents with markup as well as what is traditionally thought of as data, the XML type system is somewhat bifurcated into attributes and elements. Attributes are represented in the XML serialization format as HTML attributes are, using the name= value syntax. Attributes can hold only simple data types, like traditional relational attributes. Elements can represent simple or complex types. An element can have multiple levels of nested subelements, as in the following example.

1 Tom 2 Maureen

This means that an element can be used to represent a table in a relational database. Each tuple (row) would be represented as a child element, with relational attributes (columns) represented as either attributes or subelements. The two ways of representing relational column data in XML are known as element-centric mapping (where each column is a nested subelement) and attribute-centric mapping (where each column is an attribute on an element tuple). These are illustrated in Listing 1-6. Listing 1-6: Element-Centric Mapping and Attribute-Centric Mapping < ! element-centric mapping > < ! all data values are element content >

1 Tom 2 Maureen

< ! same document in attribute-centric mapping > < ! id and name are represented as attributes >

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 DATA AND DOCUMENT STORAGE in Listing 1-5. In

XML DATA AND DOCUMENT STORAGE in Listing 1-5. In SQL Server 2005, the server is unaware of the inheritance relationships among types (although inheritance may be used in the implementation) or polymorphic methods, however, as in traditional object-oriented systems. That is, although we can define a complex user- defined type called Person that contains multiple data values (member variables) and instance methods, and define a complex type called Author that inherits from Person, we cannot invoke methods of the Person type when using an Author type or cast Author to Person. A domain-specific scalar type like ComplexNumberwill be a more likely implementation. In addition to supporting user-defined types, SQL Server 2005 supports user-defined aggregate functions. These types extend the concept of user-defined functions that return a single value, and can be written in any .NET language, but not T-SQL. The SQL specification defines five aggregates that databases must support (MAX, MIN, AVG, SUM, and COUNT). SQL Server implements a superset of the specification, including such aggregates as standard deviation and variance. By using SQL Server 2005 support for .NET languages, users need not wait for the database engine to implement their particular domain-specific aggregate. User-defined aggregates can even be defined over user-defined types, as in the case of an aggregate that would perform aggregates over the ComplexNumber data type described earlier. Support of user-defined types and aggregates moves SQL Server closer to SQL:1999 compliance, and it extends SQL:1999 in that SQL:1999 does not mention user-defined aggregates in the specification. XML Data and Document Storage XML is a platform-independent data representation format based originally on SGML. Since its popularization, it is becoming used as a data storage format. It has its own type system, based on the XML Schema Definition language (XSD). Both XML and XSD are W3C standards at the Recommendation level.1 An XML schema defines the format of an XML document as a SQL Server schema defines the layout of a SQL Server database. The XML type system is quite rigorous, enabling definition in XML Schema Definition language of almost all of the constructs available in a 1 The W3C defines seven levels of specification. These are (in order of importance): Recommendation, Proposed Recommendation, Candidate Recommendation, Working Draft In Last Call, Working Draft in Development, Requirements, and Note. This means that a W3C Recommendation is an agreed-upon standard.

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

INTRODUCTION SELECT * from people WHERE iq

INTRODUCTION SELECT * from people WHERE iq < shoe SQL:1999 syntax invalid in SQL Server SELECT * FROM people WHERE CAST(iq AS shoesize) < shoe SQL Server 2005 goes beyond previous versions in support of SQL:1999 distinct and complex user-defined types. Extended data types must be defined as .NET classes and cannot be defined in Transact-SQL, although they are accessible in T-SQL stored procedures, user-defined functions, and other procedural code. These classes (types) may have member functions that are accessible in T-SQL la SQL distinct types, and in addition they may have mutator functions that are usable in T-SQL UPDATE statements. In addition to enabling users to define distinct types based on a single built-in data type, SQL Server 2005 allows user-defined types to have multiple storage items (attributes). Such a user-defined data type is considered a complex type in SQL:1999. Once defined to the SQL Server catalog, the new type may be used as a column in a table. Variables of the type may be used in stored procedures, and the type s attributes and methods may be used in computed types and user-defined functions. Although we ll see how to define user-defined distinct and complex types in Chapter 5, Listing 1-5 shows an example of defining a user-defined complex type, ComplexNumber, and using it as a column in a table. Listing 1-5: Defining a User-Defined Type and Using It in a Table CREATE TYPE ComplexNumber EXTERNAL NAME SomeTypes:ComplexNumber GO CREATE TABLE Transforms( transform_id BIGINT, transform_input, ComplexNumber, transform_result ComplexNumber) GO After even a fundamental description, we should immediately point out that SQL Server complex types extend relational data types. The most common usage will not be to define object data types that might be defined in an object-oriented database, but to define new scalar types that extend the relational type system, such as the ComplexNumber type shown Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

USER-DEFINED TYPES AND SQL SERVER SQL Server rule

USER-DEFINED TYPES AND SQL SERVER SQL Server rule can be bound to more than one user-defined type. Creating two SQL Server data types, creating rules, and binding the rules to the types is shown in Listing 1-3. Listing 1-3: Creating Types and Binding Them to Rules define two user-defined types EXEC sp_addtype iq, FLOAT , NULL EXEC sp_addtype shoesize, FLOAT , NULL specify constraints CREATE RULE iq_range AS @range between 1 and 200 CREATE RULE shoesize_range AS @range between 1 and 20 bind constraint to type EXEC sp_bindrule iq_range , iq EXEC sp_bindrule shoesize_range , shoesize SQL Server user-defined types have some things in common with SQL distinct types. Like distinct types, they extend the SQL types by adding user-defined behaviors, in that a rule can be considered a behavior. Unlike SQL distinct types, they may not have associated user-defined functions that are scoped to the type. For example, although we defined the shoesize type and limited its values to floating point numbers from 1 to 20, we cannot associate a function named derive_suit_size_from_ shoesize with the type. This would be possible if shoesize were a SQL standard derived type. In addition, SQL Server user-defined types are comparable based on where the underlying built-in type is comparable, without using the SQL CASToperator. The SQL specification mandates that a user-defined type must be cast to a built-in or user-defined type before it can be used in a comparison operation, but attempting to apply the CAST operator to a user-defined type in SQL Server causes an error. Listing 1-4 shows this difference in behavior. Listing 1-4: Comparing Unlike User-Defined Types use the type CREATE TABLE people ( personid INTEGER, iq iq, shoe shoesize, spouse_shoe shoesize ) SQL Server syntax

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

INTRODUCTION Table 1-1: New Data Types in SQL:1999

INTRODUCTION Table 1-1: New Data Types in SQL:1999 Data Type Description BOOLEAN Bit switch BLOB Binary large object CLOB Character large object Structured types Distinct types and user-defined types REF Pointer to a persisted structured type Array Array LOCATORs Pointers to types inside the DBMS DATALINK Reference to an external data source In addition, complex type specific methods could be defined, and the SQL language was extended to support using attributes of a complex type in queries. An example of a complex type and a SELECTstatement that uses it would look like the following. SELECT ADDRESS FROM ADDR_TAB WHERE ADDR.addr_city like Sea% SQL:1999 expanded the type system to add some less revolutionary types, such as the BOOLEAN data type (which can contain TRUE, FALSE, or NULL) and the LOCATOR and DATALINK data types, which point to other storage inside or outside the database. A complete list of the new types is shown in Table 1-1. User-Defined Types and SQL Server SQL Server has always supported its own concept of a user-defined data type. These data types are known as alias types and are defined by using the system stored procedure sp_addtype. These data types share some functionality with SQL distinct types. They must be derived from a SQL Server built-in data type. You can add integrity constraints by using SQL Server RULEs. You create a SQL Server RULE using CREATE RULE and associate a rule with a SQL Server user-defined type by using sp_bindrule. A single user-defined data type can be used in multiple tables, and a single

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

THE SQL:1999 STANDARD OrderID OrderDate Customer Name Address

THE SQL:1999 STANDARD OrderID OrderDate Customer Name Address Phone Street City State Postcode Figure 1-3: Complex Types in Otherwise Relational Tables GIF types cannot be compared (or otherwise operated on) without using the SQL CAST operator. CAST indicates that the programmer is aware that two distinct types are being compared. Using the JPEG type s get_ background_coloris likely to get incorrect results against a GIFcolumn. Complex types contain multiple data values, also called attributes. Including these data types in the SQL standard was inspired by the popularity of object-oriented databases in the early and mid-1990s. An example of a complex type is a person type that consists of a name, an address, and a phone number. Although these data types violate the first normal form of the relational data model and can be easily represented as a discrete table in a properly normalized database, these types have a few unique features. A diagram representing the data structures involved is shown in Figure 1-3. The person type could be used in multiple tables while maintaining its personness that is, the same attributes and functions are usable against the person type even when the person column is used in three unrelated tables. In addition to allowing complex types, SQL:1999 defined types that could be references to complex types. For example, a person type could contain a reference (similar to a pointer) to an address type in a different table, as shown in the following code. CREATE TYPE PERSON ( pers_first_name VARCHAR(30), pers_last_name VARCHAR(30), other fields omitted pers_base_currency REF(ADDRESS) SCOPE ADDR_TAB) ) CREATE TYPE ADDRESS ( addr_street VARCHAR(20), addr_city VARCHAR(30), addr_state_province VARCHAR(10), addr_postal_code VARCHAR(10) ) CREATE TABLE ADDR_TAB ( addr_oid BIGINT, addr_address ADDRESS )

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

INTRODUCTION conversion or marshaling is required, and that

INTRODUCTION conversion or marshaling is required, and that means faster execution. SQL Server 2005 s internal ADO.NET provider, known as System.Data. SqlServer, also contains optimizations such as the SqlExecution Context class, which allows .NET procedural code to share an execution environment (including Connection and Transaction) with its caller. .NET procedures can run in the security context of the user that cataloged the procedure or of the current user. The SQL Server internal managed provider is discussed in Chapter 4. The SQL:1999 Standard Extending the Relational Model Many of the interesting changes to the programmability and data type extensions in SQL Server 2005 are related to ongoing changes to the SQL standard, so it is instructive here to take a look at that standard. SQL:1999 is the latest version of ANSI standard SQL, although at this writing a newer version yet, known as SQL:2003, is in progress. Some of the features added to standard SQL in SQL:1999 have always been part of SQL Server, such as triggers. The more interesting features of SQL:1999 have to do with extending the type system to support extended scalar types, distinct types, and even complex types. In SQL Server 2005, you can add a new scalar type to the relational type system yourself, without waiting for Microsoft to implement it in the engine. The most common use case for SQL Server 2005 user-defined types will be to add new scalar types. A distinct type extends simple data types (such as INTEGERor VARCHAR) with special semantics. For example, a JPEG data type may be defined. This type is stored as an IMAGEdata type in SQL Server, but the IMAGEtype is extended with user-defined functions such as get_background_color and get_foreground_color. Extending a simple type by adding behaviors was inspired by object-relational databases of the mid-1990s. Adding functions to the simple IMAGE type enables SQL queries that accomplish a task, such as select all the rows in the database where the JPEG column x has a background color of red. Without the user-defined functions to extend the type, the IMAGEwould have to be decomposed into one or more relational tables for storage. As an alternative, the background color could be stored separately from the IMAGE, but this could introduce update anomalies if the IMAGE was updated but the background color was not. SQL:1999 codified the definition of the distinct type and defined some rules for its use. As an example, if the JPEGtype and the GIF type are both distinct types that use an underlying storage type of IMAGE, JPEGtypes and

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