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.
_______________________________________________
FREE
Subscription
Subscribe to our mailing list and receive new articles
through email. Keep yourself updated with latest
developments in the industry.
Note
: We never rent, trade, or sell my email lists to
anyone.
We assure that your privacy is respected
and protected.
_______________________________________
Recommended
XML Books
|
|
