Types of Secondary XML Indexes in SQL Server

These are non-clustered index of the primary XML index. There must be a primary xml index before each secondary xml index. Following are some types of secondary xml indexes:

Path Indexes

The path index is built on the path value columns of the primary XML indexes. This index improves the performance of queries that use paths and values to select data.

For example, if you execute a query that checks for the existence of a product model ID using an XQuery expression as /PD:ProductDescription/@ProductModelID[.=”19”], you can create a path secondary index on the CatalogDescription column of the ProductModel table. In this path index, you can use the primary index created previously.

The following statement creates a Path index on the CatalogDescription column:

CREATE XML INDEX PIdx_ProductModel_CatalogDescription_PATH ON Production.ProductModel (CatalogDesctiption)USING XML INDEX PXML_ProductModel_CatalogDescription FOR PATH

The preceding code create a path index, Pldx_ProductModel_CatalogDesctiption_PATH

Value Indexes

The value indexes contain the same items as path indexes but in the reverse order. It contains the value of the column first and then the path id. This index improves the performance of queries that use paths to select data.

For example, if you execute a query that checks the existence of a node in an XQuery expression such as//Item@ProductID=”1”], you can create a value secondary index by using the primary index created previously.

The following statement creates a value index on the CatalogDesctiption column:

CREATE XML INDEX PIdx_ProductModel_CatalogDesctiption_VALUE ON Production.ProductModel (CatalogDesctiption)
USING XML INDEX PXML_ProductModel_CatalogDescription
FOR VALUE

The preceding code creates a value index,
PIdx_ProductModel_CatalogDescription_VALUE on the CatalogDescription column of the table.

Property Indexes

The property index contains the primary key of the base table, path id, and the clause columns of primary XML indexes. This index improves the performance of queries that use paths to select data.

For example, if you execute a query that returns a value of the node in an XQuery expression, such as /ItemList/Item/@ProductID)[1], you can create a property secondary index on the CatalogDescription column of the ProductModel table by using the following statement:

CREATE XML INDEX PIdx_ProductModel_CatalogDescription_PROPERTY ON Production.ProductModel (CatalogDescription)
USING XML INDEX PXML_ProductModel_CatalogDescription FOR PROPERTY

The preceding code creates a property index, PIdx_ProductModel_CatalogDescription_PROPERTY, on the CatalogDescription column of the table.

You need to consider the following guidelines while creating an XML index:

• XML indexes can be created only on XML columns.
• XML indexes only support indexing a single XML column.
• XML indexes can only be added to tables, views, table-valued variables with XML column or XML variables.
• XML indexes created on a table do not allow you to modify the primary key. To do so, you first need to drop all the XML indexes on the table.

Types of XML Indexes used in SQL Server: Speed up Execution

When a query is based on an XML column, the query processor needs to parse the XML data each time the query is executed. In SQL Server, and XML data value can be of a maximum of two gigabytes (GB).

Therefore, the XML values can be very large and the server might take time to generate the result set. To speed up the execution of the query based on the XML data type, SQL Server allows you to create an index that is based on columns storing XML data values. Such indexes are called XML indexes.

Primary XML Index

This is a clustered B-Tree representation of the nodes in the XML data. When an index is created on a column with the XML data type, an entry will be created for all the nodes in the XML data. Therefore, the index creates several rows of data for each XML value in the column.

You can create XML indexes on XML columns by using the CREATE PRIMARY XML INDEX and CREATE XML INDEX T-SQL commands. For example, the ProductModel table contains the CatalogDescription column that stores XML values. You can create a primary XML index on this column by using the following statement:

CREATE PRIMARY XML INDEX PXML_ProductModel_CatalogDesctiption ON Production.ProductModel (CatalogDescription)

The preceding statement will create an index for all the nodes in the XML data stored in the CatalogDescription column.

Secondary XML Index

This is a non-clustered index of the primary XML index. A primary XML index must exist before any secondary index can be created. After you have created the primary XML index, an additional three kinds of secondary XML indexes can be defined on the table. The secondary XML indexes assist in the XQuery processing.

The three types of secondary XML indexes are:

• Path indexes
• Value indexes
• Property indexes

How to Modify XML Data using Functions in SQL Server

Similar to any other type of data, programmer might also need to modify the XML data. To modify data, you can use the modify function provided by the XML data type of the SQL Server. The modify function specifies an XQuery expression and a statement that specifies the kind of modification that needs to be done.

This function allows you to perform the following modifications:

• Insert: Used to add nodes to XML in an XML column or variable. For example, the management of AdventureWorks wants to add another column specifying the type of customer, in the CustDetails table. The default value in the Type column should be ‘Credit’. To resolve this problem, the database developer of AdventureWorks will create the following query:
  
  UPDATE CusomtDetails SET Cust_Details.modify (‘ inser attribute Type{“Credit”} as first into (/Customer) [1]’)
  
• Replace: Used to update the XML data. For example, James Stephen, one of the customers of AdventureWorks, has decided to change his customer type from Credit to Cash. As a database developer, you can create the following query to reflect this change:
  
• Delete: Used to remove a node from the XML data. For example, the management of AdventureWorks has decided to remove the ‘City’ column from the customer details. You can write the following query to display the results:
  
  UPDATE CustomDetails SET Cust_Details.modify (‘delete (/Customer/@City) [1]’)

Retreive XML data using XQuery

How to Retrieve XML Data Using XQuery

In addition to FOR XML, SQL Server allows programmer to extract data stored in variables or columns with the XML data type by using XQuery. XQuery is a language that uses a set of statements and functions provided by the XML data type to extract data. As compared to the FOR XML clause of the SELECT statement, the XQuery statements allow you to extract specific parts of the XML data.

Each XQuery statement consists of two parts, prolog and body. In the prolog section, you declare the namespaces. In addition, schemas can be imported in the prolog. The body parts specifies the XML nodes to be retrieved. The XQuery language includes the following statements:

• For: Used to iterate through a set of nodes at the same level as in an XML document.
• Let: Used to declare variables and assign values.
• Order by: Used to specify a sequence.
• Where: Used to specify criteria for the data to be extracted.
• Return: Used to specify the XML returned from a statement.

The XQuery statements also use the following functions provided by the XML data type:

Query: Used to extract XML from an XML data type. The XML to be retrieved is bicycle is manufactured at AdventureWorks, it passes through a series of work centre locations. Each work centre location produces a different cycle component. Therefore, the number of production steps varies between different work centres.

To analyse the production process, the management of AdventureWorks needs to retrieve a list of the location IDs of all the work centers, which have more than four steps. You need to generate the list displaying the location ids in the ascending order of the steps included in the work centres.

To perform this task, the database developer can create the following query:

SELECT Instructons.query
(‘ declare namespace
ns=http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions:
for $work in /ns:root/ns:Location
where count(#work/ns:step) > 4
order by count ($work/ns:step)
return
count($work/ns:step)’) AS Result
FROM Production.ProductModel
WHERE Instructions IS NOT NULL

Value: Used to return a single value from an XML document. To extract a single value, you need to specify an XQuery expression that identifies a single node and a data type of the value to be retrieved.

For example, the management of AventureWorks, Ins. Wants a list containing the product model id, product name, machine hours, and labour hours. However, not all product have production instructions. As a database developer, you have stored this data in the XML format in the ProductModel table. You can create the following query to display the results:

SELECT ProductModelID, Name, Instructions.value (‘declare namespace ns=”http//schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductManuInstructions”;
(/ns:root/ns:Location/@LaborHours) [1]’, ‘float’)AS
LaborHours,
Instructions.value(declare namespace
Ns=”http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions”;
(/ns:root/ns:Location/@MachineHours) [1]’, ‘float’) AS MachineHours
FROM Production.ProductModel
WHERE Instructions IS NOT NULL

Exist: Used to check the existence of a node in an XML data. The function returns I if the specified node exists else it returns 0. For example, the management of AdventureWorks, wants the details of all the customers in the city ‘NJ’. The details of all the customers are stored in an XML format in the CustDetails table. You can use the following query to display the results:

SELECT Cust_ID, Cust_Details.exist
(‘Customer[@City=’NJ”]’) AS ‘True’ FROM CustDetails

Retrieve XML data from DataSet

How to Retrieve XML Data from a Database Table: SQL

At times, you need to retrieve the relational data from a table into the XML format for reporting purposes or to share the data across different applications. This involves extracting data from a table in the form of well-formed XML fragments. You can retrieve the XML data in the following ways:

• Using the FOR XML clause in the SELECT statement
• Using XQuery

Using the FOR XML Clause in the SELECT statement

SQL Server allows you to extract data from relational tables into an XML format by using the SELECT statement with the FOR XML clause. You can use the FOR XML clause to retrieve the XML data by using the following modes:

• RAW
• AUTO
• PATH
• EXPLICIT

Using the RAW Mode

The RAW mode is used to return an XML file with each row representing an XML element. The RAW mode transforms each row in the query result set into an XML element with the element name row. Each column value that is not NULL is mapped to an attribute with the same name as the column name.

The following statements display the details of employees with employee ID as 1 or 2:
SELECT EmployeeID, ContactID, LoginID, Title
FROM HumaneResources.Employee
WHERE EmployeeID=1 OR EmployeeID=2
FOR XML RAW

The preceding query displays the employee details in the following format:

<row EmployeeID=”1” ContactID=”1209” LoginID=”adventure-works/guy1” Title=”Production Technician – WC60” />
<row EmployeeID=”2” ContactID=”1030” LoginID=”adventure-works/kevin0” Title=”Marketing Assistant” />

Using the AUTO Mode

The AUTO mode is used to return query results as nested XML elements. Similar to the RAW mode, each column value that is not NULL is mapped to an attribute that is named after either the column name or the column alias. The element that these attributes belong to is named to the table that they belong to or the table alias that is used in the SELECT statement, as shown in the following query:

SELECT EmployeeID, ContactID, LoginID, Title
FROM HumanResources.Employee Employee
WHERE EmployeeID=1 OR EmployeeID=2
FOR XML AUTO

If the optional ELEMENTS directive is specified in the FOR XML clause, the columns listed in the SELECT clause are mapped to sub-elements, as shown in the following query:

SELECT EmployeeID, ContactID, LoginID, Title
FROM HumanResources.Employee Employee
WHERE EmployeeID=1 OR EmployeeID=2
FOR XML AUTO, ELEMENTS

Using the PATH Mode

The PATH mode is used to return specific values by indicating the column names for which you need to retrieve the data, as shown in the following query:

SELECT EmployeeID “@EmpID”,
FirstName “EmpName/First”,
MiddleName “EmpName/Middle”,
LastName “EmpName/Last”
FROM HumanResources.Employee e JOIN Person.Contact c
AND e.EmployeeID=1
FOR XML PATH

The preceding query displays the output in the following format:
<row EmpID=”1”>
<EmpName>
<First>Guy</First>
<Middle>R</Middle>
<Last>Gilbert</Last>
</Employee>
</row>

In the preceding result set, the EmployeeID column is mapped to the EmpID attribute with @ sign. The FirstName, MiddleName, and LastName columns are mapped as subelements of the EmpName element with the slash mark(/)

You can also use the optional ElementName argument with the PATH mode query to modify the name of the default row element, as shown in the following query:

SELECT EmployeeID “@EmpID”,
FirstName “EmpName/First”,
MiddleName “EmpName/Middle”,
LastName “EmpName/Last”
FROM HumanResources.Employee e JOIN Person.Contact c ON e.ContactID = c.ContactID
AND e.EmployeeID=1
FOR XML PATH (‘Employee’)

Using the EXPLICIT Mode

The EXPLICIT mode is used to return an XML file that obtains the format as specified in the SELECT statement. Separate SELECT statement can be combined with the UNION ALL statement to generate each level/element in the resulting XML output. Each of these SELECT statements requires the first two tags to be called Tag and Parent. The Parent element is used to control the nesting of elements. It contains the tag number of the parent element of the current element. The top-level element in the document should have the Parent value set to 0 or NULL.

For example, the managers of AdventureWorks want to access the information regarding products through their mobile devices. These devices cannot directly connect to the SQL Server, but can read the data provided in the XML format. Therefore, you need to convert the details of the products from the Product table into the XML document. To perform this task, you need to create an XML document with <Product> as the parent tag. The <Product> tag will contain ProductID as an attribute and <ProductName> and <Color> as child elements.

To perform this task, the database developer can create the following query:

SELECT 1 AS Tag,
NULL AS Parent,
ProductID AS [Product!1!ProductID],
Name AS [Product!1!ProductName!element],
Color AS [Product!1!Color!elementxsinil]
FROM Production.Product
FOR XML EXPLICIT

How to Store Typed XML Data in XML Columns: SQL

To store the typed XML data, programmer need to first register the schema associated with the data in the XML schema collection objects in the database. The XML schema collection is an object on the SQL Server that is used to save one or more XML schemas. You can create an XML schema collection object by using the following SQL statement:

CREATE XML SCHEMA COLLECTION <Name> as Expression
Where,

• Name specifies an identifier name with which the SQL Server will identify the schema collection.
• Expression specifies an XML value that contains one or more XML schema documents

For example, the customer details are associated with the following schema:
<?XML version=”1.0” ?>
<xsd:schema targetNamespace=”http//schemas.adventure-
Works.com/Customers” xmlns=”http//shemas.adventure-
Works.com/Customers” elementFormDefault=”qualified”
attributeFormDefault=” unqualified”
xmlns:xsd=”http://www.w3.org/2001/XMLSchema”?>
<xsd:element name =”Customer” type=”xsd:string” />
<xsd:attribute name=”City” type=”xsd:string” />
</xsd:schema>

You can use the following statements to register the preceding schema, named as CustomerSchemaCollection, with the database:

CREATE XML SCHEMA COLLECTION CustomerSchemaCollection AS n’<?xml version= “1.0” ?>
<xsd:schema targetNamespace=”http://schemas.adventure-
Works.com/Customers” xmlns=”http://schemas.adventure-
Works.com/Customers” elementFormDefault=”qualified”
attributeFormDefault=”unqualified”
xmlns:xsd=”http://www.w3.org/2001/XMLSchema”.>
<xsd:element name =”Customer” type=”xsd:string” />
<xsd:attribute name=”Name” type=”xsd:string” />
<xsd:attribute name=”City” type=”xsd:string” />
</xsd:schema>’

You can view information about the registered schemas in a database by querying the sys.XML_schema_collection catalog view, as shown in the following statement:
SELECT * FROM sys.XML_schema_collections

After registering the XML schema, you can use the schemas to validate typed XML values while inserting records into the tables. You need to specify this while creating a table that will store the XML data. In the preceding example, if you need to validate the customer details with the CustomerSchemaCollection schema, you need to create the CustDetails table by using the following statement:

CREATE TABLE CustDetails
(
CustID int,
CustDetails XML
)

You can insert data into this table by using the following statement:
INSERT INTO CustDetails VALUES (2, ‘<?xml version=”1.0”?> <CustomerName=”Abrahim Jones” City=”Selina” />’)

While executing the preceding statement, the SQL Server will validate the values for the CustDetails column against the CustomerSchemaCollection schema.

How to Store XML Data in XML Columns: SQL

At times, Programmer need to store the XML data in its original state in a column of a database table. For example, you need to save the details of customers in the database. The details of individual customers are maintained by a website. The website saves the details of each customer in an XML file. As a database developer, you need to save this data in the SQL Server. For this, you can create the following table to store the customer details:

CREATE TABLE CustDetails ( CUST_ID int, CUST_DETAILS XML )

You can save the following types of data in the columns with the XML data types:
Untyped XML data: is also a well-formed data, but is not associated with a schema. The SQL Server does not validate this data, but ensures that the data being saved with the XML data type is well-formed.

Typed XML data: is a well-formed data that is associated with a schema defining the elements and their attributes. It also specifies a namespace for the data. When you save the typed XML data in a table, the SQL Server validates the data against the schema and assigns the appropriate data type to the data based on the data types defined in the schema. This helps in saving the storage space.
As a database developer, you should know how to store both types of data on the SQL Server.

Staring Untyped XML Data

To store the untyped XML data, you can use columns or variables with the XML data type. For example, to store customer data in the CustDetails table, you can use the following INSERT statement:

INSERT INTO CustDetails VALUES (2, ‘<Customer Name=’Abrahim Jones” City= “Selina” />’)

In the preceding statement, the string value that contains an XML fragment is implicitly converted to XML. However, you can also convert a string value to XML by using the CONVERT or CAST functions. In this example, you can use the following statement to convert the data type of the string value to XML before inserting the record into the table.

INSERT INTO CustDetails VALUES (2, convert (XML, ‘<CustomerName=”Abrahim Jones” City=”Selina” />’) )

Similarly, you can also use the CAST function, as shown in the following statement:

INSERT INTO CustDetails VALUES (4, cast (‘<Customer Name=”Abrahim Jones” City=”Selina” />’ as XML) )

How to Delete Data from Table or Related Table, SQL

Programmer need to delete data from the database when it is no longer required. The smallest unit that can be deleted from a database is a row. You can delete a row from a table by using the DELETE DML statement. The syntax of the DELETE statement is:

DELETE [FROM] table_name
[FROM table (s)]
[WHERE condition]
Where,

• Table_name specifies the name of the table from which you have to delete rows.
• Table_name specifies the name of the table(s) required to set the condition for deletion.
• Condition specifies the condition that identifies the row(s) to be deleted.

For example, the following statement deletes the address details of AddressID 104 from the Address table:

DELETE Address
WHERE AddressID = ‘104’

Deleting Data from Related Tables

While deleting records form related tables, you need to ensure that you first delete the records from the table that contain the foreign key and then from the table that contains the primary key.

Consider the example of the Adventure Works. The Employee table contains data of those employees who have retired from the company. This data is not required anymore. This increases the size of the database.

You are required to ensure that this old data is removed from the Employee table. You can delete this data by using the following SQL statement:

DELETE FROM HumanResources.Employee
WHERE BirthDate < dateadd (yy, -60, getdate ())

The database contains tables related to the Employee table. The related tables are HumanResources.EmployeeAddress, HumanResources.EmployeeDepartmentHistory, HumanResources.EmployeePayHistory, and HumanResources.JobCandidate. The EmployeeID attribute in these tables is a foreign key to the EmployeeID attribute of the Employee table. Therefore, the query results in an error. Therefore, you need to delete data from the related tables before executing the preceding DELETE statement.

Deleting All the Records from a Table

As a database developer, you might need to delete all the records from a table. You can do this by using the following DELETE statement:

DELETE table_name

You can also use the TRUNCATE DML statement. The syntax of the TRUNCATE statement is:
TRUNCATE TABLE table_name
Where,

• Table_name specifies the name of the table from which you have to delete rows. However, TRUNCATE TABLE is executed faster.

TRUNCATE TABLE does not support the WHERE clause. In addition, the TRUNCATE TABLE statement does no fire a trigger. When truncate is used, the deleted rows are not entered in the transaction log.

For example, the following statement deletes all the records from the Address table:
TRANCATE TABLE Address

Manipulate XML Data and Parsing with XML document.

How to Manipulate XML data in Database Table, SQL

With a growth in clients accessing data through heterogeneous hardware and software platforms, a need across for a language that could be interpreted by any environment. This resulted in the evolution of a language called XML. SML is a mark-up language that is used to describe the structure of data in a standard hierarchical manner.

The structure of the documents containing the data is described with the help of tags contained in the document. Therefore, various business applications store their data in XML documents.

SQL Server allows you to save or retrieve data in the XML format. This enables the SQL Server to provide database support to various kinds of applications. As a database developer, it is important for you to learn to manipulate the XML data by using SQL Server.

The structure of the XML data can be defined in the form of a supporting Document Type Definition (DTD) or schema. You can read more about XML in the appendix.

Staring XML Data in a Table

The XML data is available in the form of XML fragments or complete XML documents. An XML fragment contains XML data without a top-level element that contains the complete data.

SQL Server 2005 uses XML as a data type to save the XML data in its original state. You can create tables or variables by using this data type to store the XML data. However, you can also shred the XML data and store the values in different columns in a rowset. This process of transforming the XML data into a rowset is called as shredding.

In SQL Server, you can store the XML data in the following ways:

• A rowset
• An XML column

Staring the XML Data in a Rowset

Consider an example. You have received the order details from a vendor. The order details are generated by the application used by the vendor in an XML document. You need to store this data in a database table. For this, you need to shred the XML data. The SQL Server allows you to shred the XML data by using the OPENXML function and its related stored procedures.

Shredding an XML document involves the following tasks:

• Parse the XML document.
• Retrieve a rowset from the tree.
• Store the data from the rowset.
• Clear the memory.

Applying a Style Sheet to an XML Document

A style sheet object that passes into the transformNode method needs to be recompiled every time the method is called. Compiling a style sheet means setting all its template rules in an executable state. By using the XSLTemplate object and the XSLProcessor object to perform transformation facilitates  the reduction of overheads and increases the performance of an XSLT application.

The XSLTemplate Object

The XSLTemplate object is a dom object that is used to access an XSLT style sheet. This object is used to hold a cached style sheet that can then be dynamically associated with an XML document.

Before a style document can be applied to an XML document, it is converted into a tree structure by the parser. In the early versions of MSXML, the style sheet had to be compiled each time it was required for processing an XML document. In other words, the tree structure for a style sheet had to be recreated for the use of XSL document, as the compiled version was not stored in the system. This resulted in a longer processing time. However, in MSXML 2.6 and later, a compiled version of the XSLT document is stored in the cache memeory of the computer. This means that the XSLT tree is created the first time the document is compiled. For each successive use of the style sheet, the compiled version is reused.

The XSLT tree structure is loaded into the memory of the computer and used to process the XML document. This is because the XSLTemplate object stores the compiled XSLT document. Therefore, you must first create an XSLTemplate object. In the following example, an XSLTemplate object called xsltobj is created using JavaScript:

           xsltobj= new ActiveXObject ("MSXML2.XSLTemplate . 6 . 0") ;

This object must be associated with an XSLT style sheet document. Consider the following code snippet:

          var xsldocobj= new  ActiveXObject ("Msxm12.freeThreadedDOMDocument . 6 . 0") ;
           xsldocobj . load ("products . xsl") ;
           xsltobj . stylesheet=xsldocobj ;

In the preceding example, an instance of the FreeThreaded DOMDocument object is created. This object can be used to access an XML document as well as an XSLT style sheet. This is because XSLT is an application of XML. The load () method of the FreeThreaded DOMDocument object is used to associate the XSL document products.xsl with the xsldocobj object. The stylesheet property of the xsltobj object is then used to associate the XSLTemplate object with the object that holds the XSL document.

The XSLProcessor Object

To process an XML document by using a style sheet, you must create an instance of the XSLProcessor object. This object is used to apply a style sheet to an XML document and then process that document. The XSLProcessor object is supported only in IE 5.0 and higher versions.

The XSLProcessor object applies the given XSLT document to a specifc XML document. In other words, this object transforms an XML document by using the XSLT style sheet. For example, an XML document can be transformed into an HTML document by applying the appropriate XSLT style sheet by using XSLProcessor object.

The JavaScript code to create an XSLProcessor object is as follows:

        var  xsalprocobj= xsltobj.createProcessor ( ) ;

In this example, the createProcessor ( ) method is used to create a new XSLProcessor object called xslprocobj. The createProcessor ( ) method is called by xsltobj, which represents a cached version of a compiled XSLT template. The xsltobj object is associated with a specific style sheet contained in the variable xsldocobj.

You can then create the XML document for which the style sheet contained in the xsltobj object must be applied. Consider the following example:

        var xmldocobj = new ActiveXObject ("Msxm12 . DOMDocument . 6 . 0") ;
        xmldocobj . load ("products . xml") ;

In the example, a new DOMDocument object called xmldocobj is created. The load ( ) method of this object is used to associate an XML document called products . xml with this object. This XML document is then passed to the XSLProcessor object as an input by using the input property. This property is used to specify the XML tree that must be transformed, as follows:

        xslprocobj . input=xmldocobj ;

The transform ( ) method of the XSLProcessor object is then invoked. This method performs the transformation of an XML document. During transformation, the tree structures of the XML and XSLT documents are used as input. The XSLT tree is applied to the XML tree and the document is then processed. The output of this process is an XML document that is rendered in the manner specified in the XSLT document. Consider the following example:

       xslprocobj . transform ( ) ;

In the preceding example, the transform ( ) method of the XSLProcessor object is invoked. The transform() method can be called multiple times for an XML Document to transform the different sections of the XML document.. The result of the transform ( ) method is displayed using the output property of the XSLProcessor object. The output can be displayed in a browser window or a message box. Consider the following example:

      alert (xslprocobj . output ) ;

In this example, the alert ( ) method is used to display the data stored in the transformed XML document in a message box.

Reusing Components of a XML Schema

One of the key features of schemas is their ability to support a high degree of reusability among other schemas. This can be done by using import elements.

The include element

The include element is used to include or refer to an external schema that is located at a definite address. The syntax for using the include element is as follows:

<include id="ID" schemaLocation="filename"/>

In the preceding syntax, the include element consists of two attributes, id and schemaLocation. The id attribute is used to specify the element ID. The ID must be unique within the XSD (XML Schema Definition) document. The id attribute is optional. The another attribute is schemaLocation attribute. The value of this attribute specifies the physical location of the schema file.

The include element can have multiple occurrences in an XSD document. The schema element is the parent element of the include element. The only restriction placed on the use of include element is, both the containing and contained schema files must belong to the same target namespace.

A target namespace has a reference to the URI to which the schema belongs. You can create different target namespaces for the different schemas. For example, you can create a schema that contains the declarations for the elements and attributes required to store the purchase order details. You can then specify www.encomatcybershoppe.com/purchase as the targetnamespace for the schema.

The include element allows reference to any external schema that is defined in the context of the same target namespace. The target namespace for a schema is declared using the target Namespace attribute of the schema element.

Consider the following:

<schema xm1ns="http://www.w3.org/2001/XMLSchema"
targetNamespace="www.ecomatcybershoppe.com/purchase">
<simpleType name="prstring">
      <restriction base="string">
            <pattern value="[p] {1} \d {3}"/ >
      </restriction>
</simpleType>
</schema>

In the preceding example, a simple data type called prstring is created in an XML schema. This data type has a restriction that specifies that an element or attribute that contains prstring as its type should match a specific pattern. You can use the prstring data type in other XML schemas, as follows:

<schema xmlns="http://www.w3.org/2001/XMLSchema"
targetNamespace="www.ecomtcybershoppe.com/purchase"
xmlns:prd="www.ecomatcybershoppe.com/purchase">
<include schemaLocation="potype.xad"/>
                :
<element name="PRODID" type="prd:prstring"/>
                :
</schema>

In the preceding code snippet, the default namespace is specified as
http://www.w3.org/2001/XMLSchema. When you do not include any prefix with the element name or the data type, it is assumed that the element or data type belong to the default namespace. The target namespace is specified as www.ecomatcybershoppe.com/purchase.

Note that the target namespaces are the same in both the schemas. The prd prefix is used as an alias to refer to the namespaces URI www.ecomatcybershoppe.com/purchase. Now, can refer to the data types declared in potype.xsd, which is the physical location of the schema file in the target namespace, by using the prd prefix before the name of the data type. If you do not use the prefix, prstring will be considerd to belong to the default namespace.

The import Element

The import element performs the same function as the include element. however, the import element allows you to access components from multiple schemas that may belong to different target namespaces.

The syntax for using the import element is as follows:

<import id="ID" namespace="namespace" schemaLocation="filename" />

In preceding syntax, the import element contains three attributes:

• The id attribute takes the ID of the element as its value. This ID must be unique for the XSD document. This attribute is optional.  

• The namespace attribute specifies a namespace URI to which the imported schema belongs. The namespace attribute also specifies the prefix used to associate an element or an attribute also with a particular namespace.

• The schemaLocation attribute is identical to the value used by the include element. The value of this attribute is set to the physical location of the specified schema file.

While importing an XML schema, the importing  schema must contain a namespace reference to the target namespace of the imported schema. This namespace prefix can then be appended to the elements that are declared in the imported document, but used in the importing document.

Advantages of XML

Some of the advantages of XML are:

• Domain-specific vocabulary
• Data interchange
• Smart searches
• Granular updates
• Users-selected view of data
• Message transformation

Domain - Specific Vocabulary

HTML uses a set of predefined tags to present data in various formats. While working with HTML, you are restricted to the use of only these tags. On the other hand, XML does not have any predefined tags. You can create new tags based on the requirerments of the application, that is, you can create domain-specific vocabulary in XML.

Consider the following HTML code snippet:

 
      <B> My Book </B>
      <p>
               john smith <br>
               Tech books publications <br>
               $30.00<br>
    </p>
The preceding code snippet represents information about the author, publisher, and cost of a book. However, the tags used for presenting this content do not reveal this information. The tags specify the format in which the content must be displayed on a browser. The same information can be represented in XML as:

        <Book>
               <NAME> My Book </NAME>
               <AUTHOR> John smith </ AUTHOR>
               <PUBLISHER> tech books publications </PUBLISHER>
<PRICE>$30.00</PRICE>
</BOOK>
In the preceding code snippet, the content is described by using meaningful tags to represent the data. XML enables you ti create a markup language for your application and does not place any restriction on the number of tags that you can define. There are several languages derived from XML, such as MathML, which is a markup language used for the representation of mathematical formulae and scientific data, and Wireless Markup Language(WML), which is used for Web Applications that can be accessed by using a cellular phone.

Data Interchange

Data interchange is essential to carry out business transactions. You need to establish standard interfaces among related enterprises in which the data is stored is disparate forms to facilitate data interchange. XML produces files that are unambiguous, easy to generate, and easy to read. It provides a structure to store data in textual format, which can then be used as a standard format or protocol for data interchange. This eliminates the differences in the system that exchange data.

Smart Searches

Although HTML provides you with a set of predefined tags, it is difficult to implement a search on an HTML document. for example, consider the following code snippet in HTML :

The best picture award in 1999 went to the film <b> Titanic. </b> This film was based on the story of a ship called <b> Titanic.</b>

In the preceding code snippet, the search engine will not be able to determine whether you are referring to Titanic as a ship, a play, or a film. If you specify a search for the next Titanic within the tags <b> </b>, then the search will return all the words matching Titanic within the <b> and </b> tags.
The flexibility to create user-defined tags in XML enables you to create smart search engines. For example, consider the following code snippet:

The best picture award in 1999 went to the film
<FILM> Titanic.</FILM>
This film was based on the story of a ship called.
<SHIP>Titanic.</SHIP>

In the preceding code snippet, it is very clear that Titanic is a reference to a film by that name. When you perform a search, you can specify that the search needs to be performed for the text Titanic within the <FILM> and </FILM> tags. This enables the browser to perform a focused search and return precise information that matches the search query.

Granular Updates

 Document updates in HTML are slow as the entire document needs to be refreshed from the server. In comparison with this, only the changed content needs to be downloaded in XML documents, thus making updates faster. For example, if you need to update stock prices on a Web page, it will take more time to update an HTML document, as the entire page would have to be reloaded. With XML, only the prices can be updated.

User -Selected View of Data

In HTML, you need to create separate HTML pages to display the same information in different formats. This is because an HTML page contains data, as well as, instructions to the browser for the presentation of data. On the other hand, XML concentrates on the data and not on its presentation. You can display XML document in the required format by using Cascading Style Sheets(CSS) and Extensible Style Sheet Language (XSL). This separation of data from its presentation has various advantages. For example, you can download an XMl document once and reply different formatting styles by using CSS or XSL.
A user can select one, several, or all records, and sort by different attributes, format data based on a condition, or switch to the graphical view without requesting the XML document from the server for each operation. The same data can be presented differently, perhaps as a subset, depending on the viewer's role with respect to the data. For example, the account department my be able to access financial information, whereas a buyer may not be able to access the same information.

HTML does not allow conditional formatting of a document. For example, it is not possible using HTML to display the name of the product with the highest sales figure in green and the lowest sale figure is red. You can implement conditional formatting of data by using XML.

Message Transformation 

In XML, a message ca be stored in the form of a document, object data, or data from a database. XML message are designed in such a way they reflected the information content and not the intended use of the messages. XML design provides flexibility while storing data as it does not impose any restriction o the field size and the order in which the data is stored. Data can be extracted based on the needs of the client application.

Atomic,List and Union Data Types in XML Schemas

Atomic Data Types

Atomic data types cannot be broken down into smaller units. These data types can be primitive or derived. For example, the string primitive data type cannot be broken down further into smaller units, such as a characters.

List Data Types 

List data types are derived data types that contain a set of values of atomic data types elements referring to a list data type can contain a value only from that defined set. For example, you can create a list data type called pointlist, which is derived from the data type decimal and contains the following values .5, 2.5, and .75. Any element that is associated with the pointlist data type can contain only .5, 2.5, or .75 as its value.

Union Data Types

Union data types are derived from the atomic list data types. For example, if zip name is an atomic data type that can n create a data type called zips that can contain a combination of both these data types. Now, if you create an element called ZIPCODE of type zips, then ZIPCODE  element can contain the values shown in the following code snippet:

<ZIPCODE>NY</ZIPCODE>
<ZIPCODE>90547  96098 90contain only string values and zipnum is a list data type that can contain a list of zip code numbers, then you ca878 </ZIPCODE>
<ZIPCODE>CA </ZIPCODE>

Derived Data Types in XML Schemas

Introduction

Derived data types are defined by using other data types called base types. Base data types can either be primitive or derived data types. Derived data types can be built-in or user-defined. For example, integer is a built-in data type that is derived from the decimal data type.

Derived Data Types	Base Data Type	Description
Integer	Decimal	Represents a sequence of decimal digits with an optional leading sign (+ or -)
long	Integer	Represents a value within the range -9223372036854775808 to 9223372036854775807
nonNegativeInteger	Integer	Represents an integer that is greater than or equal to zero
Positive Integer	nonNegativeInteger	Represents an integer greater than zero
int	Long	Represents an integer within the range -2147483648 to 2147483747
time	recurringDuration	Represents an instance of time that recurs every day.
date	timeDuration	Represents a time period that starts at the midnight of a particular day and ends at the next midnight.

Primitive Data Type in XML Schemas

Introduction

Primitive data type are the fundamental data types of XSD. These data types are the basis for defining other data types in XSD. Primitive data types dot not contain elements or attributes. They contain only values.

Some of the primitive data types supported by XSD are listed in the following table.

Primitive Data Type	Description
String	Represents a collection of characters.
Decimal	Represents decimal numbers. This can be used to accurately define a value.
Float	Represents single-precision 32-bit floating-point numbers
Boolean	Represents Boolean values. This data type can have a value of true or false
timeDruration	Represents certain duration of time.
RecurringDuration	Represents a time duration that recurs after a specific interval.

Advantages of XML Schemas Created Using XSD

An XML schema defines the list of elements and attributes that can be used in an XML document. In addition to the list of elements, an XML schema specifies the order in which these elements appear in the XML document, and their data types.
Microsoft has developed the XML Schema Definition(XSD) language to define the schema of an XML document. XML schema s  have now become a w3c recommendation for creating valid XML documents.

Advantages of XML Schema s Created Using XSD
Some of the advantages of creating an XML schema by using XSD are:

• XSD provides control over the type of data that can be assigned to elements and attributes.
• XSD enables you to create your own data types. This feature enhances the flexibility of defining the structure of the XML document.
• XSD enables you to specify restriction on data. For example, you can ensure that the content of an element is a positive integer value.
• The syntax for defining an XSD is the same as the syntax used for xml documents. Therefore, it is easier to learn the syntax of an XSD.
• XML schema content models can be used to validate mixed content.
• XML schema is extensible. This enables you to reuse parts of a schema in another schema and derive custom data types from existing data types.
• XML schema is self documenting. Using the annotation element you can specify the intended purpose and attribute of a schema. This enables effective use of the schema by other.

Creating an XML Document

Problem Statement

CyberShoppe, Inc. sells toys and books in the United States. It has three branches in different parts of the country. Currently, the three branches maintain data on their local computer systems. The IT manager at CyberShoppe has identified that a centralized data repository on the products sold through its e-commerce site is required. The data from all branches must be collated and housed in a centralized location. This data must be made available to the accounts and sales sections at the individual branches, regardless of the hardware and software platforms being used at the branches. In addition, the sales personnel require access to the data using palmtops and cellular phones.
The product details of CyberShoppe consists of the product name, a brief description, the price, and the available quantity on hand. A product ID uniquely identified each product.

Solution
To solve the preceding problem, you need to perform the following tasks:

1. Identify the method to store data in a device-independent format.
2. Identify the structure of the document in which data is to be stored.
3. Create an XML document to store the data.
4. View the XML document in a browser.

Task 1: Identifying the method to store the Data in a Device-Independent Format

In the given problem statement, device ranging from mainframe computers to mobile phones must be able to access information from the data store. For example, the employee of the sales department may need to access product costs using an IBM PC. XML enables different types of devices to access information. Therefore, XML can be used to create the data store in the given scenario.

Task 2 : Identifying the Structure of the Document in Which Data is to be stored.

In the given scenario, PRODUCTDATA has been Identified as the root element. It has one child element, PRODUCT. The PRODUCT element can occur one or more times in the XML document. It acts as the container element for the PRODUCTNAME , DESCRIPTION, PRICE, AND QUANTITY elements. PRODUCTNAME has one attribute called PRODID, which is used to uniquely identify each product. The following figure illustrate the structure of the elements used in the XML document to store the product data.

Structure of the XML Document to Store Product Data

Task 3: Creating an XML Document to Store Data

To create an XML file that contains information about the products sold by CyberShoppe, type the following code in Notepad, and save the file as cyber_products.xml:

<?xml version="1.0" encoding="UTF-8" ?>
<!-- PRODUCTDATA is the root element -->
<PRODUCTDATA>
<PRODUCT PRODID="P001">
<PRODUCTNAME>Barbie Doll</PRODUCTNAME>
<DESCRIPTION>This is a toy for children in the age group of 5-10 years </DESCRIPTION>
<PRICE>12</QUANTITY>
</PRODUCT>
</PRODUCTDATA>

Task 4: Viewing the XML Document in a Browser

To View the XML document in the browser, open the cyber_products.xml file in Internet Explorer . IE provides a default tree view layout for an XML document. You can expand and collapse the tree.

The tree-view structure of the cyber_products.xml document is shown in the following figure

Future of XML

XML has revolutionized the web and the nature of distributed computing. It is the preferred file format for web development, document interchange, and data interchange. Currently, news organizations and wireless services exchange news articles, stock information, and other similar information through proprietary formats. XML will enable wider use and exchange of such information in future. XML will also help global businesses exchange data in a customized and universally acceptable format.
The future uses of XML can be summarized as:

• XML will be widely used in e-commerce.
• XML will have a huge core market in the form of B2B.
• XML will be used for mobile devices, such as mobile phones, PDAs, and palmtops due to its ability to easily convert into the appropriate format for any device.
• XML will be used to solve communication problems in EDI and Enterprise Application Integration(EAI) as it provides interoperability between disparate  applications.

Lets us look at some real life examples of XML implementation.

identifying Real-Life implementation of XML 

The Microsoft site, http://msdn.microsoft.com/xml/, provides information about customers who have successfully implemented XML in real-life. A few of these customers are listed as follows:

• FreeEDGAR.com, Inc. is an organization that provides updated information on finance, institutional holdings, and insider trading patterns to its customers. To provide this information, FreeEDGAR.com uses an XML based delivery service. This service is based on real-time filling from the US securities and Exchange commission. One of the project requirements was that FreeEDGAR's customers should be able to use the service on multiple platforms. The service had to be independent of any database technology. FreeEDGAR also had to ensure are able to query and access the structured information easily. The development team realized that XML was the perfect solution for its project's requirements. The team used a processing server and built its XML service on top of it. To generate Web Pages, it used MSXML DLL along with some XSL style sheets that were used to convert XML into HTML. The team used MSXML inside a Web page created using Visual InterDev and Visual Basic components. This approach reduced code maintenance and resulted in the development of more readable code.
• Shopping.com is another example of the real life implementation of XML. Shopping.com uses context based shopping to ensure that its uses get relevant results for their searches. The organization had to ensure that the users who conduct keyword searches through AltaVista Search also receive suitable product offers from Shopping.com for example, The keyword , "CD player", on AltaVista Search will return Web search results along with relevant CD players and electronics merchandise available for purchase from shopping.com. The development team faced the challenge of ensuring quick transfer of data between two different operating systems. Since the XML syntax is simple to learn and can be used quickly , they decided to use XML for the project. The use of XML enabled them to reduce the size of the data file and the time taken to transfer the data file to a great extent.

Difference Between SGML, HTML, and XML

XML , markup languages such as standard Generalized Markup Language(SGML) and Hypertext Markup Language(HTML) are also available.

SGML was released in 1980. It allows documents to describe their grammar by specifying the tag set used in the document and the structural relationship that these tags represent. This makes it possible to define individual formats for documents, handle large and complex documents, and manage large information repositories. However, SGML is complex and difficult for developers to master.

HTML was created by Tim Berners-Lee in 1989 as a simple and effective way of generating clear and readable documents. HTML enables you to create documents and Web pages that can be read by all web browsers . It uses a set of tags in conformance with the SGML specification.

The World Wide web consortium (W3C) developed XML to enable the expansion of Web technologies into the new domains of document processing and data interchange. XML is a subset of SGML. It is designed to ease data exchange over the Internet.  Though HTML and XML are markup languages, they have different purposes. HTML is used for data presentation, whereas XML is used for data description and definition.

Getting Started with XML

Introduction

Traditionally, organizations have conducted business on paper. Preprinted formats were most widely used to exchange information between businesses. As the number of transactions between different organizations increased over the years, there was a need for a more effective way of communicating and processing business data. Electronic Data Interchange (IDE) emerged as a result of this need. EDI refers to the process of exchanging documents in a standard format between two computer systems.

The two widely used EDI standards for transmitting data between computers are ANSI X12 and UN/EDIFACT. if data is transmitted using EDI standards, the data can be translated to or from the end-user's application format with the help of EDI software, such as RealWorld and Macola.

Consider an example of two companies, X and Y, using RealWorld and Macola, respectively to convert their files into EDI formats. Without an EDI standard, company Y cannot translate the EDI files received from company X into Macola-compatible format. This is because the EDI formats of RealWorld and Macola are dissimilar, However, if the files formatted using RealWorld are converted into one of the EDI standards, then company Y can translate invoice files received from company X into Macola-compatible format.

Although EDI standards provide an effective solution for e-commerce transactions, they Have not been widely accepted due to the following  limitations:

• Rigid transaction set: Traditional EDI was built on fixed transaction sets. Consider The following example. Company A’s invoice bill currently includes the customer Name, company name, phone number, and cash amount. If one of its trading partners Starts accepting  credit cards, then company A has to modify the invoice bill to Include credit card details. If Company A uses EDI to exchange data, then the EDI Format has to reflect this change. This is a tedious and time-consuming process, Because the Accredited Standards Committee X12  sub-group of ANSI or the UN/EDIF ACT working group must recognize the new format. Therefore, the fixed Transaction set becomes a bottleneck to business units that evolve new services, Products, and business processes.

• Fixed business rules: The business rules of small, medium, and large business units Of the same industry widely vary. Due to this, the same set of  EDI standards cannot Be uniformly implemented across all of them.
• High costs: It can be expensive for small- and medium-size business units to Implement EDI standards as compared to large business units, because of high Networking costs. Even large business units that have high investments in automation Will not be ready to replace their systems based on EDI standards. Therefore, Acceptance of EDI is restricted to a few business units who are willing to invest in EDI.
• Slow pace of standards evolution : As EDI standards cater to companies with different needs, the process of developing these standards is time consuming. In addition, the standards may not cater to the needs of all companies.

Therefore, EDI does not server as a cost-effective solution to implement data interchange among heterogeneous systems.

Introducing XML

XML is a text-based markup language that enables you to store data in a structured format by using meaningful tags. The term "extensible" implies that you can extend your ability to describe a document by defining meaningful tags for your application. XML is a cross-platform, hardware and software independent markup language. It enables computers to transfer structured data between heterogeneous systems. XML is used as a common data interchange format in a number of applications. In the example of the B2B e-commerce model, xml can be used to exchange data between the trading partners, thus eliminating the problems faced by EDI.

Web Architecture Using XML

In a traditional Web architecture, a client sends a request to the server in a predefined format and receives the appropriate response. The advantage of using XML in Web architecture is that the structure of the request can be obtained from the server at runtime. This is possible because the data stored in a XML document does not assume its intended use. Different applications can extract data according to their customized needs. Since XML is used to exchange data between various Web applications, the coupling between the server application and the client application is relatively loose.

XML can encode non-relational data, as well as, relational data structures. This enables the server application to extract data from any data source, and helps the programmers to quickly build applications for the manipulation of that data.

XML Web Architecture