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
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.
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.
OpenXML has the following format:
OpenXML (idoc, rowpattern [, flags])
It is the document handle of the internal representation of the XML document.
It is the Xpath that is used to identify the nodes to be processed as rows.
These specify that the mappings should be made between XML data and the relational rowsets. It is an optional value.
It is the schema definition of the form:
ColName ColType [ColPattern | MetaProperty][, ColName ColType [ColPattern | MetaProperty]...]
An example of the above syntax is given below:
“Amazon and the Amazon logo are trademarks of Amazon.com, Inc. or its affiliates.”Copyright - © 2004 - 2019 - All Rights Reserved.