Windows Servers

XML IN THE DATABASE: THE XML DATA TYPE < ! other types omitted > Note that an XML schema collection has a SQL Server object name (dbo.peoplecoll, in this case) and consists of one or more schemas, defining the permitted types in one or more XML namespaces. It cannot be referenced outside the database it is defined in or by using a three-part object name, like pubs.dbo.peoplecoll. An XML schema that will be used to validate XML content also can have no associated namespace, like the first schema in the previous collection. This is known as the no-namespace schema and is distinguished by the fact that its schema element has no targetNamespace attribute. You are restricted to one nonamespace schema per XML schema collection. Typed XML XML data type columns, parameters, and variables may be typed or untyped; that is, they may conform to a schema or not. To specify that you are using a typed column, for example, in our XMLcolumn in the previous table, you would specify the schema collection name in parentheses, like this. CREATE TABLE xml_tab ( the_id INTEGER, xml_col XML(peoplecoll) ) By doing this, you ve just defined a series of integrity constraints with respect to what can appear in the XML that makes up that column! Typing the XML data in a column by using an XML schema collection not only serves as an integrity constraint, but is an optimization for SQL Server s

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

TYPED AND UNTYPED XML Listing 8-5: A Simple XML Schema < ! - defines types for the namespace http://example.org/People This is known as the targetNamespace but does not indicate The location of the schema document > Note that an XSD schema includes some schema elements, such as maxInclusive and minExclusive, that serve to constrain or restrict the base data types. This constraint process is known as derivation by restriction, and the schema elements and attributes that act as constraints are known as facets. Although there is an xsi:schemaLocationattribute that can be helpful in locating arbitrary schema documents in an instance document, the XML schema specification does not mandate an algorithm by which an XML document locates its schema. SQL Server 2005 stores schema documents inside the database and keeps track of them based on the schema collection; it doesn t use xsi:schemaLocation. SQL Server XML Schema Collections Schema documents are cataloged in SQL Server as part of a named XML schema collection by using the CREATE XML SCHEMA COLLECTION DDL statement. USE pubs GO CREATE XML SCHEMA COLLECTION peoplecoll AS < ! other types omitted >

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 IN THE DATABASE: THE XML DATA TYPE Built-in Numeric Types double float decimal integer longnonPositiveInteger intnegativeInteger unsignedLong short unsignedInt unsignedShort unsignedByte nonNegativeInteger positiveInteger byte Numeric Cluster Built-in XML Types anyURI QName NOTATION string normalizedString NMTOKEN NMTOKENS token language Name NCName ENTITY ENTITIES XML Intrinsics Cluster IDREF IDREFS Figure 8-1: XSD Base Data Types (continued ) ID

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

TYPED AND UNTYPED XML XML schemas define a series of data types that exist in a particular namespace. Schemas are themselves well-formed schema-compliant XML documents, just as relational table definitions and other DDL are valid Transact-SQL. Although there can be more than one schema definition document for a particular namespace, a schema definition document defines types in only one namespace. The XML Schema Definition language (XSD) defines a standard set of base types that are supported as types in XML documents, just as the SQL:1999 standard defines a set of base types that relational databases must support. The XSD base data types are shown in Figure 8-1. Schema documents may also import types from other namespaces, using the importelement. There are also some special namespaces that are imported by default in SQL Server s XML type. Two of the most important ones are: The http://www.w3.org/2001/XMLSchema namespace. This namespace defines the constructs (elements and attributes) used in XML schema documents. The http://www.w3.org/2001/XMLSchema-instance namespace. This namespace defines the constructs to be used in XML documents that are not schemas. These are usually assigned the namespace prefixes xs and xsi, respectively, although a lot of other schema-manipulating products will use the xsd prefix rather than xs. A complete explanation of the XML Schema specification is beyond the scope of this book, but Listing 8-5 illustrates the main concept. Built-in Misc. Types date time dateTime duration gDay gMonth gYear gYearMonth gMonthDay Time Cluster boolean base64Binary hexBinary Misc. Cluster Figure 8-1: XSD Base Data Types

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 IN THE DATABASE: THE XML DATA TYPE procedures or user-defined function parameters and user-defined function return codes may be XML types. This allows you to return dynamic XML to the user based on logical operations, such as in the following example. create the user-defined function CREATE FUNCTION my_business_logic( in_dept INTEGER ) RETURNS XML AS DECLARE @x XML do some business logic that produces an XML document RETURN @x GO now call it, using dept 10 SELECT * FROM dbo.my_business_logic(10) Note that the XMLreturn code is a scalar type, rather than a TABLEtype. As with XML data type columns and variables, procedure parameters and return codes can be declared with a schema collection name and used to ensure schema validity. Although being able to schema-validate input parameters may obviate the requirement for a lot of domain-checking of input, we can still use the fact that we can do processing inside procedures to make the XML a lot more dynamic. We ll see how using and producing XML based on dynamic rules and being able to pass in XML as just another data type can be used in conjunction with new extended features of the composition and decomposition functions, SELECT…FORXMLand OpenXML, in a few sections. Typed and Untyped XML Cataloging and Using XML Schema Collections In addition to storing untyped XML documents or fragments in an XML data type column, you can use SQL Server to validate your XML data type column, variable, or parameter by associating it with an XML schema collection. Therefore, you can think of XML data types as being either schema-validated (containing data types defined by a specific set of XML schemas) or untyped (containing any well-formed XML). Whether your XML type is typed or untyped, it can still contain documents or fragments, since fragments can also be schema-valid. In addition, when you define an XML type to be schema-validated, you can also specify that it can contain only XML documents, or XML documents or fragments (known as XML content).

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 XML DATA VARIABLES AND PARAMETERS lexical fidelity. Listing 8-4 illustrates what you can and cannot do with the XMLdata type in SQL statements. Listing 8-4: Using the XML Data Type in SQL Statements assume the same xml_tab as in previous examples comparison to NULL works SELECT the_id FROM xml_tab WHERE xml_col IS NULL illegal SELECT xml_col FROM xml_tab GROUP BY xml_col SELECT xml_col FROM xml_tab ORDER BY xml_col SELECT xml_col FROM xml_tab WHERE xml_col = SELECT SUBSTRING(xml_col,1,2) FROM xml_tab casting to string allows this to work SELECT xml_col from xml_tab WHERE CAST(xml_col AS VARCHAR) = Using XML Data Variables and Parameters SQL Server 2005 allows you to use the XML data type as a normal scalar variable in Transact-SQL. You can assign XML documents or fragments to the variable as you would any other variable. Usually, you will do this by casting or converting a character-based variable, as shown in this example. declare a variable of XML data type DECLARE @x XML cast a string to it (must be a valid XML document or fragment) SET @x = CAST( Bob AS XML) use it INSERT xml_tab VALUES(@x) Just as with an XML column, variables of the XML data type can be used as input to an assignment statement. Variables of the XML data type have the same processing limits as columns of the XML data type; they may not be used in place of a string in scalar functions such as SUBSTRING, in comparisons, or in ORDERBYor GROUPBYclauses in dynamic SQL, or as parameters, without first being cast or converted to a character data type. Stored

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 IN THE DATABASE: THE XML DATA TYPE the character or National character data types, as well as BINARY and VARBINARY. When you cast from BINARY and VARBINARY, you can either specify the encoding in the XML itself or include the beginning byte-order mark (0xFFFE) if the format is Unicode. When you cast to BINARY or VARBINARY, the XML will be cast to UTF-16 with the byte-order mark present. You can cast a TEXT or NTEXT data type instance to an instance of the XML type, but you cannot cast an instance of the XML type to TEXT or NTEXT. Using one of the special methods of the XML data type (the value method, discussed later in this chapter) can produce different SQL data types. Listing 8-3 shows retrieving data from an XMLdata type column. Listing 8-3: Returning Data from an XML Data Type Column CREATE TABLE xml_tab( the_id INTEGER PRIMARY_KEY IDENTITY, xml_col XML) GO INSERT INTO xml_tab VALUES( ) INSERT INTO xml_tab VALUES(N ) GO both rows values are cast to the same data type SELECT CAST(xml_col as nchar(2000)) FROM xml_tab SELECT CONVERT(nchar(2000), xml_col) FROM xml_tab GO illegal SELECT CAST(xml_col as sql_variant) FROM xml_tab SELECT CONVERT(sql_variant, xml_col) FROM xml_tab GO Since the values of two XML data type instances cannot be compared (except using IS NULL), you cannot use the XML data type in SQL predicates or SQL clauses that require comparison, such as GROUP BYand ORDER BY. The XML data type also cannot be used in any scalar function or aggregate where comparison is required. However, because the XMLdata type is castable to any character-based type, this functionality will work if CAST (or CONVERT) is used. This behavior is exactly the behavior specified for a distinct data type by the SQL:1999 specification. Although comparison of the XML document type as a string is risky, notice that in the preceding example, both and are converted to the same lexical form, , when the CAST or CONVERT functions are used. The XMLdata type does retain Infoset fidelity but does not guarantee

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

USING THE XML DATA TYPE IN TABLES these work fine INSERT INTO xml_tab VALUES( ) INSERT INTO xml_tab VALUES(N ) so does this (if first cast to varchar/nvarchar) DECLARE @v SQL_VARIANT SET @v = N INSERT INTO xml_tab VALUES(CAST(@v AS varchar(max))) this fails at the insert statement DECLARE @SOMENUM FLOAT SET @SOMENUM = 3.1416 INSERT INTO xml_tab VALUES(CAST(@SOMENUM as XML)) Although we ve only stored well-formed XML documents in the XML data type column so far, we can also use this column to store document fragments or top-level text nodes. This is useful because we can store the results of a SELECT…FORXMLquery or XQuery results, which may not be complete documents, in an XML data type column. However, the documents or fragments must abide by XML well-formedness rules. Listing 8-2 shows some examples. Listing 8-2: Inserting XML Data into a Table CREATE TABLE xml_tab( the_id INTEGER PRIMARY_KEY IDENTITY, xml_col XML) GO ok, complete document INSERT INTO xml_tab VALUES( ) ok, document fragment though it s not a single well-formed document (two root elements) INSERT INTO xml_tab VALUES( ) ok, text element INSERT INTO xml_tab VALUES( The Window and Shade Store ) error, not well-formed INSERT INTO xml_tab VALUES( The Window & Shade Store ) error, not well-formed INSERT INTO xml_tab VALUES( ) There is no implicit conversion from the XML data type to any other data types, but the CAST or CONVERT operators do convert between any of

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 IN THE DATABASE: THE XML DATA TYPE < ! This fails with an unknown encoding error > < ?xml version= 1.0 encoding= i-bogus ?> XML data type columns can have NULL constraints (the default nullability is the current default of your SQL Server session). CHECK constraints based on the XML Infoset model are supported, using methods that are specific to the XML type. Although we ll talk about the methods in more detail in the next chapter, a typical check constraint on an XML type is shown next. pdoc must have a person element as a child of the people root CREATE TABLE xmltab( id INTEGER PRIMARY KEY, pdoc XML CHECK (pdoc.exist( /people/person )=1) ) Because XML data types follow the XML Infoset data model, they are constrained not by relational constraints, but by a collection of one or more XMLschemas. SQLServer s XMLdata type supports schema validation. We will explore this in detail in the next section. The XML data type supports an implicit conversion from any character or National character data type, but not from other SQL Server data types. This includes CHAR, VARCHAR, NCHAR, and NVARCHAR. You can use CAST or CONVERTto convert BINARY, VARBINARY, TEXT, NTEXT, and IMAGEdata types to the XML data type for storage as well. Casting from TEXT and NTEXT is permitted to enable forward compatibility for users who have stored their XML data in these data types in previous versions of SQL Server. Casting from the BINARY data types is useful for features like SQL Server Service Broker that can communicate using binary or XML. In addition, you can store a SQL_VARIANTdata type into an XMLdata type table after casting it to a character-based type. Listing 8-1 shows inserting rows into a table containing an XMLdata type. Listing 8-1: Inserting Rows into a Table with an XML Data Type CREATE TABLE xml_tab( the_id INTEGER PRIMARY_KEY IDENTITY, xml_col XML) GO

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 THE XML DATA TYPE IN TABLES used to improve the performance of the functions associated with an XML data type. In addition to the previous example that created a single XMLtype column in a table, you can have tables that contain more than one XMLdata type column. You can create a table with an XML column in local or global temporary tables as well as ordinary tables. An XML data type column can be used in a VIEWas well. XMLdata type columns have certain limitations when used in tables. They may not be declared as a PRIMARYKEYin a table. They may not be declared as a FOREIGNKEYin a table. They may not be declared with a UNIQUEconstraint. They may not be declared with the COLLATEkeyword. These first three limitations exist because individual instances of the XML data type may not be compared with each other. Although it would not be difficult to perform a string comparison with XML data, it would be an order of magnitude more difficult to perform a comparison at the Infoset level. For example, the two XML documents in the following example are Infoset-equivalent, but not lexically equivalent. < ! These two documents are equivalent > The XMLdata type cannot use the COLLATEkeyword, because XML provides its own encoding via the encoding attribute on the XML document declaration. The following code is an example of using encoding. If the document uses an encoding that is unknown or unsupported, SQL Server will return an error. An error will also result if the content does not match the specified encoding; for example, the encoding specifies UTF-16 but contains characters encoded as ANSI. Although encoding specified in the XML document declaration is preserved, documents are always physically stored as UTF-16. < ! This works correctly > < ?xml version= 1.0 encoding= utf-8 ?>

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