Summary: In a world of mission-critical data stored in different heterogeneous sources, the Scalable Markup Language (XML) provides a powerful mechanism for data exchange between different applications and integration between different applications through a standard format.
XML has been tightly integrated into the Microsoft® SQL ServerTM Relations Database Management System (RDBMS), which helps developers build next-generation Web and enterprise applications. This white paper tells the XML function in SQL Server 2000 and compare these functions in Oracle.
Why integrate XML and RDBMS?
XML supports information exchange under the following business solutions:
Corporate companies (B2B)
The integrated supply chain of different suppliers.
Companies to consumers (B2C)
A browser-based application requiring data from the database is required.
Within the enterprise
Integration, such as Enterprise Resource Planning (ERP) and Customer Relationship Management (CRM) software from Different Suppliers.
These business programs require information exchange to provide the following XML functionality:
Map loosely combined commercial system. SQL Server 2000 provides XML views with XML views, XPath query, and SELECT statements for relational data. Mapping from XML data to a relational table is provided using OpenXML and comment architecture. A secure HTTP connection to the database. Inquiry, update, and loading capabilities for any source of XML data. SQL Server 2000 provides URL and Microsoft OLE DB providers for data for SQL Server (SQLOLEDB) and Microsoft ActiveX® Data Objects (ADO) to SQL Server.
XML Provider in SQL Server 2000
Microsoft® SQL ServerTM 2000 is a highly scalable and reliable platform for the establishment of XML-based applications. SQL Server 2000 provides the following XML features:
HTTP Accesses the new SELECT statement option (for xml) XML mode XML view XML mode XML view XML mode XML view XPath query OpenXML OLE DB and ADO Access
HTTP Access
Microsoft® SQL ServerTM 2000 provides three ways to access data via HTTP:
The SQL statement SQL statement and stored procedures at the URL can be executed directly from the URL. This allows you to access data in the database particularly quickly. The template template is a valid XML document containing one or more embedded SQL statements. Like the SQL statement, the template can be specified at the URL or in the file. The template allows the use of the URL to retrieve SQL data without exposing the structure of the database; the query can only know the template name. This province has a trouble of entering a long SQL statement at the URL. SQL Server 2000 supports the following features within the template:
SELECT statements and entity references. The execution of the stored procedure. Specifying parameters pass and default parameters. XPath query. HTML Event After the Integrated HTML Form Input Variable can be passed as a parameter in the URL specified template. Scalable Style Sheet Language (XSL) Style Table can be used to process the output of the client or web server access mechanism.
Options available when using HTTP access
HTTP Access Method supports the following options:
ContentType
This keyword specifies the type of content that returns a document. TEXT / XML is the default document content type.
XSL
This option allows the query results to be processed using the XSL file.
Safety
Safety is based on each virtual root directory setting, and the permissions are also set on SQL Server. ISAPI Dynamic Link Library (DLL) has three authentication options:
Anonymous access
Set the Microsoft Windows® or SQL Server Login / Password directly on the virtual root and used for all users.
Basic authentication
When using this option, a clear text SQL Server login / password is passed through network, so this option is to be used with the Secure Sockets layer (SSL). Integrated security
Use the Windows Access Control List (ACL).
New SELECT statement options for retrieving results in XML format (for XML)
You can return a result for an existing relational database to perform SQL queries and return results in XML format instead of standard rowsetting format. These queries can be executed directly or during the stored procedure. To retrieve XML results, use the SELECT statement for XML and specify XML mode such as RAW, AUTO, or EXPLICIT.
XML mode
The specified XML mode determines the shape that returns an XML document. You can specify the following XML mode:
Raw
The RAW mode gets the query results and converts each of the rows in the result set into an XML element, and uses a regular identifier as an element tag, the column in the Select is an attribute.
Auto
The AUTO mode returns to the query results in a simple nested XML tree. At least one column in the FROM statement appears in the SELECT statement is represented as an XML element. The columns listed in the SELECT statement are mapped to the corresponding element properties. Columns can also be mapped to child elements. The nesting or hierarchy of the result set or the hierarchy is determined by the table order identified in the SELECT statement. The leftmost table will be the top level element. The table on the left (the column ID in the SELECT statement) will nest within the top element and push it according to the class.
Explicit
In Explicit mode, you can explicitly define the shape of the XML tree. Use this mode to write queries in a special way, and additional information about the desired nested is also explicitly specified as part of the query.
The following figure (Fig. 1) shows the result of using the virtual path and the SQL string in the URL in the URL.
If your browser does not support the inline box, click here to view it in a separate page.
Figure 1 Example of using a virtual path and a SQL string in an AUTO mode output
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 binary data in the base64 encoded format. In RAW and EXPLICIT mode, the result of not specifying the option to retrieve binary data is an error.
Elements
If this option is specified, the column returns as the child element, as shown in the following figure (Fig. 2). Otherwise, the column will be mapped to an XML property. This option only supports it in Auto mode.
If your browser does not support the inline box, click here to view it in a separate page.
Figure 2 Example of an output using an element option
XML view
Microsoft® SQL ServerTM 2000 introduces several XML-data architectural language comments to provide an XML view of the database. You can use these annotations to specify a two-way mapping of XML- to--relations under the XML-data architecture. This includes mapping of elements and attributes into the tables and columns in the xml-data architecture, and mapping in the database, and reverse mapping. By default, the element names in the comment schema are mapped in the table (view) of the same name in the specified database, and the attribute is mapped in the same name. These comments can also be used to specify hierarchical relationships in XML.
XPath inquiry
SQL Server 2000 supports a subset of XPath languages, using it and the XML view technology described earlier, you can get XML results from the database.
OpenXML
OpenXML is a Transact-SQL keyword that provides a relationship / rowset view within an XML document in memory. OpenXML is a rowset provider similar to a table or view. OpenXML provides a mechanism for accessing XML data from the TRANSACT-SQL context by incorporating data from an XML document. Therefore, OpenXML allows you to manage XML documents and their interactions with the relevant environment. OpenXML can be used in SELECT, INSERT, UPDATE, and DELETE. You can specify OpenXML instead of specifying a data source table or view.
The XML document can be passed to the stored procedure as a text parameter (Char, nchar, varchar, nvarchar, text, ntext). OpenXML statements can be used with other Transact-SQL statements to provide a rowset view of XML data, so the XML document can be used as a data source when performing an insert, update, deletion, and coupling. This can be used to perform multi-line, multi-table updates to the database through a single stored procedure call.
OLE DB and ADO Access
SQLOLEDB has been extended for XML and XPath. Added a new iCommandStream interface to pass to the OLE DB template that needs to be processed. In addition, the properties set of the OLE-DB provider is also extended, allowing the ISTREAM object specification to return XML results and support XPath queries using XML views defined by the mapping schema. Stream objects are introduced in ADO 2.5, and now you can use the newly introduced language branches 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 the functionality in Oracle. Its main difference is that Oracle has established a regular XML frame, and Microsoft has established special XML support in SQL Server 2000. SQL Server 2000 supports mapping between loosely combined commercial systems (XML view) by simple "Plug and Play" publication mechanism (XML view). For example, it directly includes support for the search (for XML) and storage (OpenXML) XML data. Oracle's XML technology requires complex programming even if you perform simple XML tasks. Unlike Oracle's solution, SQL Server 2000 provides intrinsic support for the XML document generated in different formats without having to perform XSL conversion.
function list
Function SQL Server 2000 Oracle Loosing Combined with Business Systems These functions provide bidirectional mapping between XML and data relationships. SQL Server 2000 integrated XML features allow system to be enabled through the least programming work. Oracle does not have any integrated XML specific features. XML View XPath supports Transact-SQL for XML extended Transact-SQL OpenXML extension without specific features. Custom programs must be written. To the inner XML extension of SQL allows users to quickly enable applications with their SQL skills without having to make additional programming work. SQL Server 2000 provides internal extension to the SQL language, which can be used to return to XML data from a standard SQL query. The server-side utility provided by Oracle does not extends the standard SQL, requiring additional programming to return XML data. Transact-SQL for XML Extension Transact-SQL OpenXML extension is not specific. You must write custom programs using the XSQL utility. Different XML Access Methods These methods have brought flexibility to programmers accessing XML data over a network connection. Both SQL Server 2000 and Oracle support XML data in the database using different methods. HTTP Access OLE DB / ADO Access JDBC HTTP Access OLE DB / ADO Access JDBC XML Template XML Template allows you to store XML business logic on the server intermediate layer. Both SQL Server and Oracle support to store XML query templates over an optional parameter. URL / HTTP Access XML Template URL / HTTP Access XSQL Template For XML Data Security Web Access Network Connect Internet Application Requirements Security Access. SQL Server 2000 provides security that can be managed on a single form. Oracle provides limited level security and supports encrypted the entire database owner, but does not support single-form security. Database User-Level Security Database Object-Level Security Database User-Level Security Complex XML Document Advanced Search Options SQL Server 2000 and Oracle provide support for complex text documents (including XML). The full-text search with segmentation function Media text Search XML Analyst Programmers can use these tools to maximize the maximum capabilities and flexibility when designing an XML application. MSXML COM support for multiple languages supports multiple languages Oracle Analysis SQL Server 2000 and Oracle XML Features Comparison
Although Oracle provides a regular XML framework, you must write a custom program to use its large part of the XML feature. Oracle provides XML text search capabilities that can be extended to identify XML tags.
This article will compare the following SQL Server 2000 and Oracle XML features.
XML view
SQL Server 2000 includes technologies that provide XML views for relational data. Oracle is currently writing a custom program to provide mapping.
Introduction to the database
Integration functions in the database can provide higher performance than external implementations. Establishing the Decision of the XML function in SQL Server 2000 considers these performance factors. Oracle does not have intrinsic XML support.
Search dynamic XML format
Oracle query produces a fixed XML format similar to element-centric RAW format. To get more useful formats, programmers must write XSL conversion or Java code. SQL Server 2000 not only provides models similar to RAW, but also allows direct generation of more "valuable" XML (using XML view and for XML AUTO and EXPLICIT mode). In addition, SQL Server 2000 provides a stream interface for query results returning to the XML flow format.
XML update
Oracle and SQL Server 2000 (with web distribution) support bulk load. SQL Server has an OpenXML that provides rows on the server through XML and an update program (Web release) that uses XML syntax. Oracle has a Java package that performs similar functions. Oracle XSQL Template with SQL Server 2000 XML Template
Oracle uses XML SQL Utility for Java (used to arrange data as an XML format) and XSQL Servlet (for URL access) to perform template files. SQL Server 2000 provides a higher integration solution by providing the inner support for retrieval XML data. Oracle's XSQL template file can only be performed by XSQL servlet. To perform an Oracle template in any other way, you need to customize Java programming. The SQL Server 2000 template can be executed from the URL or can be executed from the ADO program.
XPath inquiry
Oracle has an alpha level demonstration that maps the XPath subset to SQL. SQL Server 2000 contains an XPath subset of sells.
Full text search and index
SQL Server 2000 provides OpenXML with overflow syntax, which can be used to extract index information from the document. Oracle provides similar methods for extracting data from documentation. Oracle contains a text retrieval component (WITHIN clause) performed by level. Although Oracle can't perform association, it supports multi-level nested queries. The full-text search system in SQL Server 2000 is currently not providing hierarchical indexing and Search XML in plain text format. It does not include special configurations for identifying tags and attribute names.
XML stream
SQL Server 2000 provides a semantic syntax and an XML flow interface from ADO. Oracle only supports the syntax syntax.
Scalability and performance
SQL Server 2000 can use OpenXML and For XML queries to push part of the load from the intermediate layer to the server.
Although it is difficult to compare the performance and scalability of the two products, SQL Server has a certain advantage due to the generation of XMLs directly by querying to generate XMLs without having to use XSLT or Java programs.
Object view
Oracle has an object relationship support and uses this feature 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 components
Oracle has a Transviewer Bean, architecture processor, and XML analysis program for different languages. These functions have no relationships with the database. MSXML and third-party utilities provide the same features.
XML class generator
Like Oracle Transviewer Beans, this function does not matter to database technology.
in conclusion
Microsoft SQL Server 2000 provides XML features designed specifically for Web and database developers. Web developers don't have to learn database programming, you can use SQL Server 2000 XML features because SQL Server 2000 supports standard XML construction (such as XPath), allowing developers to interact with the database. Similarly, database developers don't have to learn XML programming, you can use the for XML clause to get XML query results or use OpenXML to manipulate XML data. Finally, SQL Server 2000 provides a simple open mechanism and XML view to define the architecture mapping of business transactions.
The information contained in this document represents the current view of the issued problem in the date of publication, Microsoft Corporation to the discussion. Because Microsoft must comply with the changing market conditions, the document should not be understood as the commitment of the Microsoft party, and Microsoft does not guarantee the accuracy after the information given to the date of publication. This document is for reference only. In this document, Microsoft does not make any promises or suggestions.
Users must comply with all applicable copyright laws. Without restricting the rights of the copyright law, if you have not obtained a clear written permission from Microsoft, it is not possible to copy any form or means (electronic, mechanical, photocopying, recording, etc.). Disseminate any part of this article must not store it or introduce it into the retrieval system.
Microsoft may have patents, patents, trademarks, copyrights, or other intellectual property rights involved in this document. Unless explicitly mentioned in the Microsoft Words License Agreement, you will not provide any patents, copyrights, or other intellectual property rights to you.
© 2000 Microsoft Corporation. all rights reserved.
