How to Access a Database Using XML

Delivering XML with Data

Depending upon the applications, there are two methods of using XML data as a communication platform between a client and server.

1. Using the database engine to generate the XML data.
2. Using middleware systems to convert to/from XML and the legacy infrastructure.

Database created XML, to only incorporate data contained within the data base. It is difficult to flexibly represent some data, because they deal with individual rows and not hierarchical data. The data that does not change frequently, the XML data is generated only upon changes, and not the client requests. If data mining is a requirement, the real solution is generating the XML data from the SQL server, because there are only few tools for OLAP processing in the middleware realm.

Middleware computing provides a central point of access to many systems, database driven or not. Middleware also performs rules based processing
on data that is being translated. The middleware approach is expensive to build and may be overkill for a specific application. It is slower than database created XML for data that is not changing frequently.

Retrieving Data from SQL database

There are two different methods of generating XML from within SQL server. One way is to use Web Assistant Wizard, to create Web Tasks, which are automated tasks that run at specific intervals and generate either HTML or XML files, or the second way is to use stored procedures to return results sets that are formatted into XML.

Also using Web Assistant Wizard it is possible to create Web Task that generates the XML file that retrieves the data from SQL database and displays it in the browser.

Microsoft’s SQL feature is to make the generation of HTML files from SQL. There is no reason to build custom Web software that interactively queries for the data and builds HTML results. Web Tasks are designed and are meant to generate an HTML. An XML specification is not complete when Web Tasks are added to SQL server.

Web Tasks automatically creates the XML file based on the template created by the user and the settings given in the Web Assistant Wizard. The XML files retrieves the data from SQL Server and displays it to the user and viewed from the browser. To access the database using XML, an example is given below.

A table is created called “Bookinfo“ with the columns BName, Author, AuthorADD, Phone, Publisher and PublisherADD in SQL Server database and insert rows. A template file called “Bookinfo.tpl” is also created and the Web Assistant Wizard is used to create the Bookinfo.xml file.

The real flexibility of the template feature of SQL Server is the ability to use it to generate XML. The server does not care whether the file contains all anything other than the keywords it uses to insert current row data. Three files,one the template file called Bookinfo.tpl, one the DTD file called the Bookinfo.dtd and one CSS file called Bookinfo.css.

The CSS file contains the codes for styling format. The keywords <%begindetail%>, <%enddetail%> and <%insert_data_here%> are the ASP code used to fetch the data from SQL database. <%begindetail%> and <%endetail%> are used to mark the area where SQL query’s results set should be used to fill the <%insert_data_here%> statements. It indicates where column data should be placed from the results set. The following is the code for Bookinfo.tpl.

<?xml version=”1.0”?>
<?xml-stylesheet href=”Bookinfo.css” type=”text/css”?>
<!doctype penta SYSTEM “Bookinfo.dtd”>
<penta>
<pentabody>
<toptitle>Online books dot com</toptitle>
<weltext>Welcome to online books</weltext>
<bodytext>Happy to inform the facility of reading the following books online at free of cost</bodytext>
</pentabody>
<Bookinfo>
<%begindetail%>
<Entry>
<BName ><%insert_data_here%></BName>
<Author><%insert_data_here%></Author>
<AuthorADD><%insert_data_here%></AuthorADD>
<Phone><%insert_data_here%></Phone>
<Publisher><%insert_data_here%></Publisher>
<PublisherADD><%insert_data_here%></PublisherADD>
</Entry>
<%enddetail%>
</Bookinfo>
<Foot>
<Footer>Online books for demonstration</footer>
</Foot>
</penta>

The following is the code for Bookinfo.dtd

<!ELEMENT penta (pentabody, Bookinfo, Foot)>
<!ELEMENT pentabody (toptitle, weltext, bodytext)>
<!ELEMENT toptitle (#PCDATA)>
<!ELEMENT welltext (#PCDATA)>
<!ELEMENT bodytext (#PCDATA)>
<!ELEMENT Bookinfo (Entry)>
<!ELEMENT Entry (BName, Author, AuthorADD, Phone, Publisher, PublisherADD)>
<!ELEMENT BName (#PCDATA)>
<!ELEMENT Author (#PCDATA)>
<!ELEMENT AuthorADD (#PCDATA)>
<!ELEMENT Phone (#PCDATA)>
<!ELEMENT Publisher (#PCDATA)>
<!ELEMENT PublisherADD (#PCDATA)>
<!ELEMENT Foot (#PCDATA)>
<!ELEMENT Footer (#PCDATA)