The Role of XML in SQL Server 2000

One of the most common problems that one is faced with managing data is the exchange of data between multiple data sources. This situation is more serious in business-to-business or business to consumer environment.

.

Solutions such as Electronic Data Exchange have not become popular due their heavy costs and limited flexibility. This was solved using the eXtensible Markup Language, that was developed under W3C.XML is often compared with HTML, where the only difference is that in XML, our own tags can be created.

SQL 2000 has a built in feature for XML.This includes the following features.

• RETREIVES DATA IN XML FORMAT USING THE FOR XML CLAUSE IN SELECT STATEMENT:

XML results can be retrieved using SQL queries. These Queries can be executed directly or using a stored procedure. If the results have to be retrieved directly then the FOR XML clause of the SELECT statement can be used. Within the statement the mode can be specified as either RAW, AUTO OR EXPLICIT. The data can be retrieved as an XML document by modifying the SELECT statement. The general format is,

FOR XML mode [, XMLDATA] [, ELEMENTS] [, BINARY BASE 64]

An example for the above Syntax is:

Select cus_Id, cus_lname, cus_fname from customer
FOR XML RAW

Mode can be RAW, AUTO, OR EXPLICIT.

* RAW MODE:

RAW mode transforms the query results into an XML element with the generic identifier row. Each column value that is not null is mapped to an attribute in which the attribute takes the same name as the column name.

* EXPLICIT MODE:

In the EXPLICIT mode the Query writer controls the shape of the XML document returned by the execution of the Query. Query must be written in a specific way so that the additional information about the nesting of the statements is explicitly stated.

* AUTO MODE:

In the AUTO mode, the Query results are displayed in the nested form.

The format of the XML document returned by the query is completely returned in the EXPLICIT mode.

* XML mode specifies whether the mode is RAW, AUTO or EXPLICIT.
* XMLDATA specifies that an XML schema has to be returned.
* If the ELEMENTS option is specified then the columns are returned as sub elements.
* If BINARY BASE 64 is specified then the binary data returned by the Query is represented as base64-encoded format.

II) INSERTION OF THE XML DATA INTO THE DATABASE USING THE OpenXML ROWSET PROVIDER

* Run the sp_xml_preparedocument stored procedure that creates memory resident representation of the XML document and then returns an integer value.
* Execute OpenXML function that uses the XML document created in the first step. Use the OpenXML function into the INSERT statement. This inserts the converted data into any SQL database.
* Run the sp_xml_removedocument stored procedure to free the memory allocated to the XML document by sp_xml_preparedocument.

OpenXML has the following format:

OpenXML (idoc, rowpattern [, flags])
WITH (SchemaDeclaration | TableName)]

Idoc:

It is the document handle of the internal representation of the XML document.

Rowpattern:

It is the Xpath that is used to identify the nodes to be processed as rows.

Flags:

These specify that the mappings should be made between XML data and the relational rowsets. It is an optional value.

Schema declaration:

It is the schema definition of the form:

ColName ColType [ColPattern | MetaProperty][, ColName ColType [ColPattern | MetaProperty]...]
* Colname is the name in the rowset
* Coltype is the data type of the column in the rowset.
* Colpattern: This is optional and is generally used to define how XML nodes have to be mapped to the columns.
* Tablename is the name of the table.

An example of the above syntax is given below:

SELECT *
FROM OPENXML (@idoc, ‘/ROOT/Student’, 1) WITH (Rollno varchar (5), Marks varchar (10))
The above example displays the name of the first student, along with the Roll number and marks from the XML document.