7.3 XML Access to SQL Server

Figure 7.1 shows a high-level architectural block diagram of SQL Server 2000's XML support. It is interesting to note that depending on the overall system or service architecture, different access components and/or protocols are preferable. If there is a limited amount of business logic, or when most of the business logic can be completely pushed either to the client (for example, via ECMAScript embedded in the HTML page) or to the database server (into the database's stored procedures), a simple HTTP or SOAP access mechanism suffices. For two-tier architectures or where the business logic needs to be performed on the Web server (the middle tier), a closer coupling of the business logic to database access is often used due to performance and programmability reasons. Thus, the XML database access story should also be accessible through the standard access components such as OLEDB, ADO, and the .NET APIs.

For these reasons, SQL Server 2000 provides all access to its XML features (except Bulkload) via the SQLOLEDB provider, ADO, and a .NET interface as well as via an IIS ISAPI extension that provides access to the functionality via HTTP and SOAP. In the following sections, we will discuss each XML feature in detail and show where it fits into the architecture and how it can be accessed both via HTTP/SOAP and ADO.NET.

7.3.1 Access via HTTP

Several ways exist to access SQL Server via HTTP. The most common one is to write an active server page that accesses the database via ADO. SQL Server 2000 introduces an HTTP access mechanism via an ISAPI extension that avoids ASP if no mid-tier business logic is needed and?with the latest Web release?can also expose templates, user-defined functions, and stored procedures as SOAP methods.

The URL formats of the new HTTP access methods start with:

http://domainname/vroot

The SQL Server ISAPI is registered with IIS to handle messages to a particular virtual root (vroot). The ISAPI will receive the requests for that particular vroot, and after performing authorization, will then pass the appropriate commands via the SQLOLEDB provider (in Web releases via a special-purpose SQLXML OLEDB provider) to the database. The virtual root as part of the URL provides an abstraction mechanism that encapsulates the accessed database server and database instances, the access rights, and the enabled access methods. Currently, SQL Server 2000 provides the following access methods:

• Ad hoc URL query mechanism:

  http://domainname/vroot?sql=select+xmldoc+from+table

  Allows arbitrary T-SQL statements (including updates and data definition statements). Query results are returned in their native binary representation. Together with the T-SQL FOR XML extensions, it can return XML. Since this mechanism is dangerous to enable in production systems, we will not further elaborate on this mechanism.

• Direct query access:

  http://domainname/vroot/dbobject/Table[ @column1=value] /@column2

  Provides native access to the binary representation of the data while only allowing a safer set of queries via an XPath-like simple query syntax. An example will be shortly presented where it is used.

• Template access:

  http://domainname/vroot/template/templatefile?param

  Templates are XML documents that provide a parameterized query and update mechanism to the database. Since they hide the actual query (or update) from the user, they provide the level of decoupling that makes building loosely coupled systems possible.

  Elements in the urn:schemas-microsoft-com:xml-sql namespace are processed by the template processor and used to return database data as part of the resulting XML document. Elements in other namespaces are returned to the client unmodified. The templates support named parameters to parameterize the queries. For security purposes, only values can be parameterized and not query components.

• XPath XML view access:

  http://domainname/vroot/schema/schemafile/XPATH?param

  This URL provides a way to query an XML view that is specified by the annotated schema referenced by the schema file in an ad hoc way using XPath queries. The URL can be parameterized in the same way templates are. More details on XPaths against annotated schemata are presented shortly.

• SOAP access:

  http://domainname/vroot/soaptypename?wsdl

  This URL provides the WSDL (Web Services Description Language) file for the exposed Web service. Each user-configured SOAP type name provides the WSDL file and a configuration file. The services exposed via SOAP can then be accessed using any Web service access mechanism (see http://msdn.microsoft.com/sqlxml for details).

dbobject, template, and schema designate three types of virtual names for use with the ISAPI extension. They provide an abstraction for the access methods, and in the case of the last two types of virtual names, they also provide the location of the files that are associated with the access. In addition, all access mechanisms can be parameterized with predefined parameters that among others allow specifying the output encoding, the character set used in the URL, and a server-side XSLT transform.

One of the most important aspects of providing access to a database via HTTP is security. The URL access mechanism has to guarantee that only authorized people can access those parts of the database to which they are allowed access. The ISAPI extension provides three authentication modes on a per-vroot basis. This allows the database administrator to set the database internal access rights for the authenticated users inside the database since only those users who are authenti cated will be allowed access. The first mode is the standard HTTP/HTTPS-based basic authentication that prompts for a database user/password combination via HTTP (only secure with a secure HTTP connection). The second mode allows every user who connects to the server to impersonate the vroot-specific Windows or SQL Server user. The user/password combination is associated with the vroot and cannot be changed or retrieved by the user. The final authentication mode takes advantage of Windows ACL such that the authentication is done by a previous authentication event (e.g., the login to the client account), and the credentials are then passed on to the database. This allows the use of a single vroot with multiple access rights without prompting for the user/password combination.

7.3.2 Using the XML Features through SQLOLEDB, ADO, and .NET

All the XML features are also accessible through SQLOLEDB and in later Web releases via a special SQLXML OLEDB provider that provides better performance. Both of the OLEDB providers have been extended with a stream interface that is accessible via ADO's stream interface. XML and XPath were added as new dialects to SQLOLEDB. XML indicates that the input is a template file; XPath indicates that the input is an XPath query with its associated annotated schema. Using a FOR XML query as described shortly does not necessitate a new dialect, but as for any XML-specific result, the result needs to be returned via the stream interface and not the rowset interface of SQLOLEDB or ADO. Some of the predefined URL parameters such as the output encoding or the XSLT transform are also exposed as SQLOLEDB and ADO properties.

Finally the latest Web release also includes a new .NET class named Microsoft.Data.SqlXml that provides access to SQL Server and its XML features. It provides the following three primary classes:

• SqlXmlCommand: Used to send a Transact-SQL statement to the database, execute a stored procedure, or query the database using other technologies (such as annotated schemas and XML templates) and get the results back as XML. This object supports a wide range of options, which are discussed in detail in http://msdn.microsoft.com/sqlxml.

• SqlXmlParameter: Used to specify the value for a parameter in the command. This can be a parameter to an ad hoc query, stored procedure, XPath query, or XML template.

• SqlXmlAdapter: Used to populate a DataSet object with an XML result set, or update the database with an XML DiffGram.

For more information about the definition and use of these APIs, the reader is referred to http://msdn.microsoft.com/sqlxml.

Top Title TOC Preface P.1 What Is XML? P.2 XML Concepts P.3 XML-Related Technologies P.4 XML Data Management P.5 How This Book Is Organized P.6 Who Should Read This Book P.7 Resources Acknowledgments Chapter 8 Chapter 12 Chapter 13 Chapter 14 Chapter 19 Part I: What Is XML? Chapter 1. Information Modeling with XML 1.1 Introduction 1.2 XML as an Information Domain 1.3 How XML Expresses Information 1.4 Patterns in XML 1.5 Common XML Information-Modeling Pitfalls 1.6 A Very Simple Way to Design XML 1.7 Conclusion Part II: Native XML Databases Chapter 2. Tamino-Software AG's Native XML Server 2.1 Introduction 2.2 Tamino Architecture and APIs 2.3 XML Storage 2.4 Querying XML 2.5 Tools 2.6 Full Database Functionality 2.7 Conclusion Chapter 3. eXist Native XML Database 3.1 Introduction 3.2 Features 3.3 System Architecture Overview 3.4 Getting Started 3.5 Query Language Extensions 3.6 Application Development 3.7 Technical Background 3.8 Conclusion Chapter 4. Embedded XML Databases 4.1 Introduction 4.2 A Primer on Embedded Databases 4.3 Embedded XML Databases 4.4 Building Applications for Embedded XML Databases 4.5 Conclusion Part III: XML and Relational Databases Chapter 5. IBM XML-Enabled Data Management Product Architecture & Technology 5.1 Introduction 5.2 Product and Technology Offering Summaries 5.3 Current Architecture and Technology 5.4 Future Architecture and Technology 5.5 Conclusion Notices Chapter 6. Supporting XML in Oracle9i 6.1 Introduction 6.2 Storing XML as CLOB 6.3 XMLType 6.4 Using XSU for Fine-Grained Storage 6.5 Building XML Documents from Relational Data 6.6 Web Access to the Database 6.7 Special Oracle Features 6.8 Conclusion Chapter 7. XML Support in Microsoft SQL Server 2000 7.1 Introduction 7.2 XML and Relational Data 7.3 XML Access to SQL Server 7.4 Serializing SQL Query Results into XML 7.5 Providing Relational Views over XML 7.6 SQLXML Templates 7.7 Providing XML Views over Relational Data 7.8 Conclusion Chapter 8. A Generic Architecture for Storing XML Documents in a Relational Database 8.1 Introduction 8.2 System Architecture 8.3 The Data Model 8.4 Creating the Database 8.5 Connecting to the Repository 8.6 Uploading XML Documents 8.7 Querying the Repository 8.8 Further Enhancements 8.9 Conclusion Chapter 9. An Object-Relational Approach to Building a High-Performance XML Repository 9.1 Introduction 9.2 Overview of XML Use-Case Scenario 9.3 High-Level System Architecture 9.4 Detailed Design Descriptions 9.5 Conclusion Part IV: Applications of XML Chapter 10. Knowledge Management in Bioinformatics 10.1 Introduction 10.2 A Brief Molecular Biology Background 10.3 Life Sciences Are Turning to XML to Model Their Information 10.4 A Genetic Information Model 10.5 NeoCore XMS* 10.6 Integration of BLAST into NeoCore XMS Conclusion Chapter 11. Case Studies of XML Used with IBM DB2 Universal Database 11.1 Introduction 11.2 Case Study 1: "Our Most Valued Customers Come First" 11.3 Case Study 2: "Improve Cash Flow" 11.4 Conclusion Notices Chapter 12. The Design and Implementation of an Engineering Data Management System Using XML and J2EE 12.1 Introduction 12.2 Background and Requirements 12.3 Overview 12.4 Design Choices 12.5 Future Directions 12.6 Conclusion Chapter 13. Geographical Data Interchange Using XML-Enabled Technology within the GIDB System 13.1 Introduction 13.2 GIDB METOC Data Integration 13.3 GIDB Web Map Service Implementation 13.4 GIDB GML Import and Export 13.5 Conclusion Chapter 14. Space Wide Web by Adapters in Distributed Systems Configuration from Reusable Components 14.1 Introduction 14.2 Advanced Concept Description: The Research Problem 14.3 Integration of Components with Architecture 14.4 Example 14.5 Future Generation NASA Institute for Advanced Concepts, Space Wide Web Research, and Boundaries 14.6 Advanced Concept Development 14.7 Conclusion Chapter 15. XML as a Unifying Framework for Inductive Databases 15.1 Introduction 15.2 Past Work 15.3 The Proposed Data Model: XDM 15.4 Benefits of XDM 15.5 Toward Flexible and Open Systems 15.6 Related Work 15.7 Conclusion Chapter 16. Designing and Managing an XML Warehouse 16.1 Introduction 16.2 Architecture 16.3 Data Warehouse Specification 16.4 Managing the Metadata 16.5 Storage and Management of the Data Warehouse 16.6 DAWAX: A Graphic Tool for the Specification and Management of a Data Warehouse 16.7 Related Work 16.8 Conclusion Part V: Performance and Benchmarks Chapter 17. XML Management System Benchmarks 17.1 Introduction 17.2 Benchmark Specification 17.3 Benchmark Data Set 17.4 Existing Benchmarks for XML 17.5 Conclusion Chapter 18. The Michigan Benchmark: A Micro-Benchmark for XML Query Performance Diagnostics 18.1 Introduction 18.2 Related Work 18.3 Benchmark Data Set 18.4 Benchmark Queries 18.5 Using the Benchmark 18.6 Conclusion Chapter 19. A Comparison of Database Approaches for Storing XML Documents 19.1 Introduction 19.2 Data Models for XML Documents 19.3 Databases for Storing XML Documents 19.4 Benchmarking Specification 19.5 Test Results 19.6 Related Work 19.7 Summary Chapter 20. Performance Analysis between an XML-Enabled Database and a Native XML Database 20.1 Introduction 20.2 Related Work 20.3 Methodology 20.4 Database Design 20.5 Discussion 20.6 Experiment Result 20.7 Conclusion Chapter 21. Conclusion References Contributors Editors Chapter 1: Information Modeling with XML Chapter 2: Tamino-Software AG's Native XML Server Chapter 3: eXist Native XML Database Chapter 4: Embedded XML Databases Chapter 5: IBM XML-Enabled Data Management Product Architecture and Technology Chapter 6: Supporting XML in Oracle9i Chapter 7: XML Support in Microsoft SQL Server 2000 Chapter 8: A Generic Architecture for Storing XML Documents in a Relational Database Chapter 9: An Object-Relational Approach to Building a High-Performance XML Repository Chapter 10: Knowledge Management in Bioinformatics Chapter 11: Case Studies of XML Used with IBM DB2 Universal Database Chapter 12: The Design and Implementation of an Engineering Data Management System Using XML and J2EE Chapter 13: Geographical Data Interchange Using XML-Enabled Technology within the GIDB System Chapter 14: Space Wide Web by Adapters in Distributed Systems Configuration from Reusable Components Chapter 15: XML as a Unifying Framework for Inductive Databases Chapter 16: Designing and Managing an XML Warehouse Chapter 17: XML Management System Benchmarks Chapter 18: The Michigan Benchmark: A Micro-Benchmark for XML Query Performance Diagnostics Chapter 19: A Comparison of Database Approaches for Storing XML Documents Chapter 20: Performance Analysis between an XML-Enabled Database and a Native XML Database Remember the name: eTutorials.org Copyright eTutorials.org 2008-2023. All rights reserved.