SQL Server 2000 XML Overview

Abstract: In a world where key data of a task is stored in different heterogeneous sources, the Extensible Markup Language (XML) A standard format provides a powerful mechanism for data exchange between different applications and integration between different systems.

XML has been tightly integrated into Microsoft SQL Server Relational Database Management System (RDBMS), which can help developers build next-generation Web and enterprise applications. This White Paper describes the XML functions in SQL Server 2000 and their comparison with those in Oracle.

Why integration of XML and RDBMS?

XML supports information exchange in the following commercial scenarios:

Enterprise-to-enterprise (B2B)

The integrated supply chain of different suppliers.

Enterprise-to-consumer (B2C)

Browser-based applications that require data retrieval from databases.

Enterprise

Integration, such as enterprise resource planning (ERP) and Customer Relationship Management (CRM) software from different vendors.

These business scenarios require the following XML functions for information exchange:

• Commercial Systems with loose ing.

  SQL Server 2000 provides an XML view for relational data through the XML view, XPath query, and select statements. The ing from XML data to Relational Tables is provided using the openxml and annotation architecture.

• Secure HTTP connection to the database.
• Query, update, and load XML data from any source.

  SQL Server 2000 provides URLs and Microsoft ole db providers for SQL Server (sqloledb) and Microsoft ActiveX Data Object (ADO) to access data stored in SQL Server.

XML providers in SQL Server 2000

Microsoft SQL Server 2000 is a highly scalable and reliable platform for Building XML-based applications. SQL Server 2000 provides the following XML functions:

• HTTP access
• The new SELECT statement option (for XML) used to retrieve results in XML format)
• XML Mode
• XML View
• XPath Query
• Openxml
• Ole db and ADO access

HTTP access

Microsoft SQL Server 2000 provides three methods for accessing data through http:

• SQL statement at URL

  SQL statements and stored procedures can be executed directly from the URL. This allows quick access to data in the database.

• Template

  A template is a valid XML document that contains one or more embedded SQL statements. Like SQL statements, templates can be specified at URLs or in files. The template allows you to use a URL to retrieve SQL data without exposing the database structure to the queryer. The queryer can only know the Template Name. This saves the trouble of inputting a long SQL statement at the URL. SQL Server 2000 supports the following functions in the template:

  • Select statement and entity reference.
  • Execution of stored procedures.
  • Specify parameters and default parameters.
  • XPath query.
• HTML post-event Integration

  HTML form input variables can be passed as parameters in the URL specified template through a program. The extensible style sheet language (XSL) style sheet can be used to process the output of the client or web server access mechanism.

Options available for HTTP access

The HTTP access method supports the following options:

Contenttype

This keyword specifies the content type of the returned document.Text/XMLIs the default document content type.

XSL

This option allows you to use an XSL file to process query results.

Security

Security is set based on each virtual root directory, and permissions are also set on SQL Server. The ISAPI dynamic link library (DLL) has three authentication options:

Anonymous Access Set Microsoft Windows or SQL Server logon/password directly on the virtual root and use it for all users. Basic Authentication When this option is used, clear text SQL Server logon/password must be transmitted over the network. Therefore, this option should be used together with the Secure Socket Layer (SSL. Integrated Security Use the Windows Access Control List (ACL ).
The new SELECT statement option (for XML) used to retrieve results in XML format)

You can perform SQL queries on existing relational databases and return results in XML format instead of standard row set format. These queries can be executed directly or from the stored procedure. To retrieve XML results, use the for XML statement of the SELECT statement and specify XML modes such as raw, auto, and explicit.

XML Mode

The specified XML mode determines the shape of the returned XML document. You can specify the following XML mode:

Raw

Obtain the query results in RAW mode, convert each row in the result set into an XML element, and use a regular identifier as the element tag. Columns in select become attributes.

Auto

The auto mode returns the query result with a simple Nested XML tree. In the from statement, at least one table in the SELECT statement is displayed as an XML element. Columns listed in the SELECT statement are mapped to corresponding element attributes. Columns can also be mapped to child elements. The nesting or hierarchy of elements in the result set is determined by the table sequence identified by the columns specified in the SELECT statement. The leftmost table is the top-level element. The table on the left (identified by the columns in the SELECT statement) is nested in the top-level element, and so on.

Explicit

In the explicit mode, you can explicitly define the shape of the result XML tree. This mode requires that the query be written in a special way, and additional information about the required Nesting is also explicitly specified as part of the query.

(Figure 1) displays the results output in auto mode using virtual paths and SQL strings in the URL.

If your browser does not support embedded boxes, click here to view them on a separate page.

Figure 1 example of results output in auto mode using virtual paths and SQL strings in URLs

Options available when using XML Mode

The XML mode supports the following options:

Xmldata

This option returns the document architecture with the XML document.

Binary base64

Specify this option in the query to return base64 encoded binary data. In raw and Explicit Modes, if you do not specify this option to retrieve binary data, an error is returned.

Elements

If this option is specified, the column is returned as a child element, as shown in figure 2. Otherwise, columns are mapped to XML attributes. This option can only be used in auto mode.

If your browser does not support embedded boxes, click here to view them on a separate page.

Figure 2 output example using element options

XML View

Microsoft SQL Server 2000 introduces several XML-data architecture language annotations to provide the XML view of the database. You can use these annotations in the XML-data architecture to specify the bidirectional ing between XML-to--relationship. This includes ing elements and attributes in the XML-data architecture to tables and columns in the database, and reverse ing. By default, element names in the annotation architecture are mapped to tables (Views) with the same names in the specified database, and attributes are mapped to columns with the same name. These annotations can also be used to specify the hierarchy in XML.

XPath Query

SQL Server 2000 supports a subset of the XPath language. You can obtain XML results from the database using the XML view technology described earlier.

Openxml

Openxml is a keyword of transact-SQL. It can provide the link/row set view in the XML file in the memory. Openxml is a row set provider similar to a table or view. Openxml provides a mechanism to access XML data in a Transact-SQL context by importing data from an XML document to a relational table. Therefore, openxml allows you to manage XML documents and their interaction with related environments.

Openxml can be used in select, insert, update, delete, and other statements. You can specify openxml instead of the specified data source table or view.

XML documents can be used as text parameters (Char,Nchar,Varchar,Nvarchar,Text,Ntext) To the stored procedure. Openxml statements can be used with other Transact-SQL statements to provide the row set view of XML data. Therefore, XML documents can be used as data sources when performing insert, update, delete, join, and other relational operations. This can be used to execute multi-row and multi-table updates to the database through a single stored procedure call.

Ole db and ADO access

Sqloledb has extended XML and XPath. Added a newIcommandstreamInterface is used to pass the template to be processed to ole db. In addition, the property set of the OLE-DB provider is extended to allow supportIstreamThe XML results returned by the object specification and the XML view defined by the ing architecture are supported for xpath query. Stream objects are introduced in ADO 2.5. They can now be used together with the newly introduced language branch to send XPath queries to the database and receive XML results from the database.

Oracle Comparative Analysis

The XML function in SQL Server 2000 is similar to that in Oracle. The main difference is that Oracle has established a general XML framework, while Microsoft has set up special XML support in SQL Server 2000. SQL Server 2000 supports ing between loose commercial systems through a simple "plug-and-play" public mechanism (XML view. For example, it directly contains support for retrieval (for XML) and storage (openxml) XML data. Oracle's XML technology requires complex programming even for simple XML tasks. Unlike Oracle solutions, SQL Server 2000 provides internal support for generating XML documents of different formats without the need for XSL conversion.

Function list

Function	SQL Server 2000	Oracle
Loose integration with public ing of commercial systems These functions provide bidirectional ing between XML and Data Relationship views. The XML feature integrated with SQL Server 2000 allows you to enable the system with minimal programming effort. Oracle does not provide any XML-specific functions.	XML View XPath support Transact-SQL for XML Extension Transact-SQL openxml Extension	No specific function. You must write a custom program.
SQL internal XML Extension Allows users to use their SQL skills to quickly enable applications without additional programming work. SQL Server 2000 provides an internal extension to the SQL language and can be used to return XML data from standard SQL queries. The server utility provided by Oracle does not extend standard SQL and requires additional programming to return XML data.	Transact-SQL for XML Extension Transact-SQL openxml Extension	No specific function. You must use the XSQL utility to write custom programs.
Different XML access methods These methods provide flexibility for programmers who access XML data through network connections. Both SQL Server 2000 and Oracle Support accessing XML data in the database using different methods.	HTTP access Ole db/ADO access JDBC	HTTP access Ole db/ADO access JDBC
XML template The XML template allows you to store XML business logic on the server middle layer. Both SQL Server and Oracle Support storing XML query templates on the server using optional parameters.	URL/HTTP access XML template	URL/HTTP access XSQL Template
Security of XML data Web Access Network connection to Internet applications requires secure access. SQL Server 2000 provides the security that can be managed at the single table level. Oracle provides a limited level of security and supports encryption of the entire database owner, but does not support single-table-level security.	Database user-level security Database Object-level security	Database user-level security
Advanced search options for complex XML documents Both SQL Server 2000 and Oracle provide support for searching complex text documents (including XML.	Full-text search	Media text search with split Function
XML analysis program Programmers can use these tools to achieve maximum capacity and flexibility when designing XML applications.	Support for multi-language MSXML com support for analytic programs	Supports Oracle analysis programs in multiple languages

Comparison of SQL Server 2000 and Oracle XML Functions

Although Oracle provides a general XML framework, you must write a custom program to use most of its XML functions. Oracle provides XML text search capabilities that can be extended to identify XML tags.

This document compares the following SQL Server 2000 and Oracle XML functions.

XML View

SQL Server 2000 includes the XML view technology for relational data. At present, Oracle must write a custom program to provide ing.

Internal expansion of the database

The integration function in the database can provide higher performance than the functions implemented by the external. These performance factors are taken into account when setting up SQL Server 2000's internal XML function. Oracle does not support internal XML.

Search dynamic XML format

Oracle queries generate a fixed XML format similar to the element-centric raw format. To get a more useful format, programmers must write XSL transformations or Java code. SQL Server 2000 not only provides a raw-like model, but also allows you to directly generate more "valuable" XML (using the XML view and for XML auto and Explicit Modes ). In addition, SQL Server 2000 provides a stream interface for returning query results in XML Stream format.

XML update

Both Oracle and SQL Server 2000 (with Web release) support batch loading. SQL Server has openxml that provides row sets on the server through XML and an update program (Web release version) that uses XML syntax ). Oracle has Java packages that execute similar functions.

Oracle XSQL template and SQL Server 2000 XML template

Oracle uses xml SQL utility for Java (used to orchestrate data in XML format) and XSQL Servlet (used for URL access) to execute template files. SQL Server 2000 provides a more integrated solution for execution template files by providing internal support for retrieving XML data. Oracle XSQL template files can only be executed through XSQL servlet. To execute the Oracle template in any other way, you need to customize Java programming. The SQL Server 2000 template can be executed either from the URL or from the ADO program.

XPath Query

Oracle has an alpha-level demonstration of how to map XPath subsets to SQL. SQL Server 2000 contains a subset of the available versions of xpath.

Full-text retrieval and Indexing

SQL Server 2000 provides overflow column syntax for openxml to extract index information from documents. Oracle provides a similar method for extracting data from documents to create indexes. Oracle contains a hierarchical text retrieval component (within clause ). Although Oracle cannot perform Association, it supports multi-level nested queries. The full-text retrieval system in SQL Server 2000 does not currently provide hierarchical indexes and search XML in plain text format. It does not contain special configurations used to identify tags and attribute names.

XML Stream

SQL Server 2000 provides the row set syntax and XML Stream interfaces from ADO. Oracle only supports the row set syntax.

Scalability and Performance

SQL Server 2000 can use openxml and for XML queries to push part of the load from the middle layer to the server.

Although it is difficult to compare the performance and scalability of the two products, SQL Server has some advantages because it can generate xml directly through queries without using XSLT or Java programs for conversion.

Object View

Oracle supports object relationships and uses this function to provide an XML object view for data stored in the database. SQL Server 2000 has different models supported by ADO objects.

XML analysis program, architecture processor, and XML development component

Oracle has transviewer beans, architecture processors, and XML analysis programs for different languages. These features have nothing to do with the database. MSXML provides the same functionality as a third-party utility.

XML class Generator

Like Oracle transviewer bean and XML analytics, this function has nothing to do with database technology.

Conclusion

Microsoft SQL Server 2000 provides XML functions specially designed for Web and database developers. Web developers can use SQL Server 2000 XML without learning database programming, because SQL Server 2000 supports standard XML structures (such as XPath) and allows developers to interact with databases. Similarly, database developers can use the for XML clause to obtain XML query results or manipulate XML data using openxml without learning XML programming. Finally, SQL Server 2000 provides a simple open mechanism and XML view for defining enterprise transaction architecture ing.

The information contained in this document represents the current views of Microsoft Corporation on the issues discussed at the date of publication. Because Microsoft must comply with changing market conditions, this document should not be understood as a commitment of the Microsoft side. Microsoft does not guarantee the accuracy of the information given after the date of publication.

This document is for reference only. Microsoft does not provide any express or implied warranty in this document.

Users must comply with all applicable copyright laws. Without the prior written consent of Microsoft, no part of this article shall be reproduced or disseminated for any purpose, in any form or by means (electronic, mechanical, photoprinting, recording, etc, it cannot be stored or imported into the retrieval system.

Microsoft may own patents, patent use, trademarks, copyrights or other intellectual property rights covered in the subject of this document. Unless explicitly mentioned in the Microsoft written license agreement, the purchase of this document shall not provide you with any patents, copyrights or other intellectual property rights.

2000 Microsoft Corporation. All rights reserved.