XML Support in Microsoft SQL SERVER

This article explains how to retrieve XML document by using data stored in SQL SERVER.And here iam not doing any hard code, simply by configuring the IIS to support XML to retrieve data from database.

About XML

In the business world, clients share data to carry out business. The data that client share can be stored in different formats. To integrate and exchange the data stored in different data formats, you need to convert the data into one common format for exchange between different applications.

To overcome this, W3C defines XML as the common data format for exchange between different applications. And also XML is CrossPlatform, hardware and software independent. XML is purely Text Based Mark Up Language; here we concentrate on “What the DATA “not “How the DATA�. It enables you to store data in a structured format by using meaningful tags. XML is very similar to database. Unlike database, XML document store data in the form of plain text, which can be used across various platform.

In XML, you can very well specify the structure of the data by creating a Document Type Definition (DTD) or XML schema. When you include DTD in the XML Document, the software checks the structure of the XML Document against the DTD. The process of checking the structure of the XML Document is called validating. And this carry out by PARSER In DTD, we can very well specify the Structure and what comes next and also the data type in DTD.

Sample XML Document

</p> <p><? xml version=�1.0�? ><br /> <stores><br /> <store id=�1001� ><br /> <title>BU1032 </title><br /> <price>450 </price><br /> <ord_date>29-062006 </ord_date><br /> </store><br /> <store id=�1002� ><br /> <title>AU1032 </title><br /> < price>550 </price><br /> < ord_date>13-052006 </ord_date><br /> </store><br /> </stores></p> <p>

The above XML Document does not contain any formatting instruction. To change the look and feel of the XML Document, W3C has specified extensible stylesheet language transformation (XSLT). XSLT is the language used to format the XML Document and displaying them in required format. XSLT follows the XML syntax.

Retrieving XML Document By Using Data Stored In SQL SERVER

Most of them are familiar with, How webpage is being hosted in the web server and how the data getting retrieved from the database and how the data getting updated in the database. However, if you want to display the data stored on the database in web page, you need to write the code by using any technology like ASP or ADO. In order to simplify the task of creating web pages that display data from Database, Ms-SQL Server2000 provides integration with XML. SQL Server 2000 allows you to generate XML Document by using data stored in a DB. These document can be used for providing devices-independent data. You can also display the resulting XML Document in the supporting web browser.

Support feature in SQL Server 2000

Sql server is a RDBMS. Therefore all operations on the tables stored in a SQL SERVER DB produce a result in the form of a table. SQL SERVER 2000 has introduced features, such as URL Query and XPath queries, for integrating XML with SQL Server.

* FOR XML clause: The FOR XML clause of the SELECT statement can be used to generate an XML document based on the data returned by a query.

* OPENXML function: It provide the OPENXML function that can be used in a T-SQL statement. This function has the ability to represent an XML document as a rowset.

* Direct URL Queries: The SQL Sever XML Support for IIS utility is used to process direct URL queries. This utility parses the query and sends it to SQL server, which generate the result as an XML document.

* XPath Queries: An XPath Query is an XML-based query language that can be used to process the data in XML documents. The XML Support for IIS utility of SQL Server 2000 can be used to process XPath queries and generate the result as an XML Document.

Generating an XML Document

SQL Server 2000 provides the SQL-XML mapping rules for extracting the XML data from DB. You can use FOR XML clause of the select statement to extract data from the DB and display it as an XML Document.

In FOR XML Clause, data are retrieved in 3 different modes such as AUTO, RAW, and EXPLICIT.

Syntax for FOR XML

</p> <p>SELECT *<br /> FROM table_name<br /> FOR XML mode_type</p> <p>

AUTO MODE: The AUTO mode returns the query result as nested XML element.

RAW MODE: The RAW mode takes the query result and transforms each row in the result set into an XML element with a generic row identifier as the element tag. This mode maps the columns that have non-null values to an attribute of an XML element.

EXPLICIT: If you specify the EXPLICIT mode, you must ensure that the generated XML document is well-formed and valid. An EXPLICIT mode query produces a universal table that contains all the information about resultant XML tree.

As I stated earlier, you can use the SELECT statement with the FOR XML clause to generate an XML document from the db. If you want to display the resulting XML document in a browser, you need to compose the URL according to the requirement.

Syntax of a URL query: http:///?sql=&root=root_element_name

To use this URL in the browser, you need to create the web site on a web server. A web server can be implemented by installing IIS in any windows OS. After that you need to create the virtual directory and that virtual directory map to physical directory.

SQLSERVER also allows you to create the a virtual directory on a web site by using the Configure SQL XML Support in IIS option in the SQL SERVER program group.

Steps to create Virtual Directory:

1. Create a folder called stores (depends on you) under the c:\inetpub\wwroot directory.
2. Select the Configure SQL XML Support in IIS option from Microsoft SQL SERVER program group. This will open the IIS virtual directory management. Right click the default web page site node in the tree displayed. Select New->Virtual Directory->this will invoke the new virtual directory properties window.
Click the general tab. Type the name of the new virtual dir as stores. Set the physical path of the virtual directory  to c:\inetpub\wwroot\stores
3. Then select the security tab, specify the User Name and Password to be used to connect the sql server.
4. Then select the data source tab, and specify the Database name and server name to be associated with the virtual dir.
5. Click the Setting tab. Check the Allow URL Queries and Allow XPath check boxes to enable a user to send URL and Xpath queries from the browser.
6. Click Ok and close the IIS Virtual Directory Management.

Execution Part: http://localhost/stores?sql=SELECT+*+FROM+sales+FOR+XML+AUTO&root=sales

Copyright @  Sathiyasivam 2007