XML And Database

Introduction

Relational database exploits structured query language to search for information and data modeled into a system transformed into analogous and extract data out of database and model into a particular data scheme using XML. Data is rendered as XML before sent back to client and XML data is stored in an ad hoc relational table in binary large object fields. XML and database represent the key alliance for data-driven and interoperable applications.

Reading XML Data from Database

There are two ways to retrieve data from as XML, use the XML extension to select command or execute a query on particular text. SQL server does not mark these fields with attribute to indicate XML data.

SQL Server XMLExtensions

XML support in SQL server 2000 which provide URL driven access to database resources and possibilities of using Xpath queries to select the data from relational tables and does not create ad hc storage structure to XML data.

Access SQL server URL
The Internet information service allow to directly query commands to SQL server by HTTP.
Create XML schema
It is used to represent a result set as an XML document and specify the mapping rules between the native field and xml attribute and element.The XML document can be queried using Xpath expressions.
Retrieve gathered data
The database internal engine is capable of formatting the raw column data to XML fragments and strings to callers.
Inserted data as an XML document
Read the relational data to hierarchal XML documents and write XML data to tables source document is preprocessed by system stored and parsed document is passed to module.

XML Auto mode

Auto mode return data packed as XML fragments without root node and the name of each node. The XML output automatically group child records by the below code:

< Customers customerID = "ALERT" ContactName = "Maria Anders" >
< orders OrderID = "2064" / >
< Orders OrderId = "2054" / >
< Orders OrderId = "1033" / >
</ Customers >
< Customers CustomerID = "ALERT" ContactName = " Ana Bella " >
< Orders OrderId = "1243" / >
< Orders OrderId = "3423" / >
< /Customer >

XML Explicit Mode

Add columns by using a relative complex syntax for columns. The Columns have an alias described by the below syntax:

SELECT column_name AS [ParentNade ! ParentTag TagName ! Directive ]

Each sub-tree corresponds to a different tag and is filled by resorting to different SELECT statements. The below query is used to fill the subtrees:

SELECT 1 AS Tag ,
NULL AS Parent ,
employeeid AS [ Employee!1!ID ] ,
lastname AS [ Empployee!1!Name]

Reading from Text fields

Text that contain XML data can be selected and processed using XML text reader and the query must include single column and single record and creates the XMLnet database by duplicating database and wrapping the string in column. The XML reader loops through nodes and moves from one record to the next record as shown below:

void ProcessNotes ( XmlTextReader reader )
{
try
{
while ( reader.read () )
{
if ( reader.NodeType == XmlNodeType.Text )
MessageBox.Show ( reader.Value );
}
}
catch { }
finally
{
MessageBox.Show (" Closed... ");
}
}

SQL Command Methods

CreateParameter - Creates an sqlXMLparameter object which represent parameters.
ClearParameters - Clears the parameter that was created for command.
ExecuteStream - Executes command and return a new Stream object.
ExecuteNonQuery - Executes the command but does not return anything.
ExecuteXmlReader - Executes the command and return the XmlReader object.

The sqlXmlAdapter class provides three constructors and it is more a command that manages. Dataset object than a true data adapter object. The constructor use information they receive to set up an internal instance of class and the fill method is used for all information passed through constructor.