SQL Server 2000存储过程与XML编程详解

需积分: 9 36 浏览量更新于2025-01-04 收藏 14.13MB PDF 举报

"《SQL Server 2000 Sorted Procedure & XML Programming》第二版是由Dejan Šunderic编著的一本专业书籍，它针对SQL Server 2000这一数据库管理系统深入探讨了存储过程（StoredProcedure）以及XML编程的相关知识。本书是McGraw-Hill/Osborne出版社出版的作品，旨在帮助读者理解和掌握在SQL Server 2000环境中高效地使用存储过程进行数据管理和操作，同时利用XML（可扩展标记语言）的强大功能来构建灵活、结构化的数据交换和处理解决方案。内容涵盖了以下几个核心知识点： 1. 存储过程：SQL Server 2000中的存储过程是一组预编译的SQL语句，它们可以被封装在一个单独的单元中，提高了代码复用性和执行效率。书中详细介绍了如何设计、创建、调用存储过程，包括参数传递、事务管理、错误处理等高级主题。 2. XML编程：随着数据库系统对XML的支持日益增强，这本书会教授如何在SQL Server 2000中使用XML进行数据交换、数据存储和查询，包括XML数据类型、XQuery、XML Schema以及XML函数的应用。 3. 实战应用：通过丰富的示例和实战项目，作者展示了如何将存储过程与XML技术结合，实现如数据清洗、数据转换、数据整合等实际业务场景中的问题解决策略。 4. 版权声明：该书享有McGraw-Hill Companies的版权，强调了未经出版商许可，任何形式的复制、分发或存储都必须得到书面授权，特别是程序列表可以输入计算机系统执行，但不能用于其他目的。《SQL Server 2000 Sorted Procedure & XML Programming》第二版不仅适合SQL Server开发者、DBA和架构师作为深入学习的参考资料，也适合希望提升数据库管理和数据处理能力的IT专业人士。书中提供的理论和实践指导有助于读者适应快速发展的数据库技术趋势，提升职业竞争力。"

Publishing Database Information Using HTTP . . . . . . . . . . . . . . . . . . . . . . . . 547

Configuring Database Access Through HTTP . . . . . . . . . . . . . . . . . . . . . 548

Accessing Database Information Using a URL . . . . . . . . . . . . . . . . . . . . 552

Troubleshooting Virtual Directories . . . . . . . . . . . . . . . . . . . . . . . . . 554

Executing a Stored Procedure Through HTTP . . . . . . . . . . . . . . . . . . . . 556

Accessing Database Information Using Templates . . . . . . . . . . . . . . . . . . 557

POSTing Queries to the Server . . . . . . . . . . . . . . . . . . . . . . . . . . . 567

XML Views Based on Annotated XDR Schemas . . . . . . . . . . . . . . . . . . . . 571

XML Views Based on Annotated XSD Schemas . . . . . . . . . . . . . . . . . . . . 582

Programmatic Database Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 588

Retrieving XML Data Using SQLXML Managed Classes . . . . . . . . . . . . . . . . 588

Retrieving XML Data Using ADO.NET . . . . . . . . . . . . . . . . . . . . . . . . . . . . 597

Using SqlCommand . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 597

Using DataSet Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 598

Client-Side XML Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 599

Using URL Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 600

Using Templates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 600

Using SQLXML Managed Classes . . . . . . . . . . . . . . . . . . . . . . . . . . 601

Processing of Queries with the For XML Clause . . . . . . . . . . . . . . . . . . . 602

Chapter 15 Modifying Databases Using SQLXML . . . . . . . . . . . . . . . . . . . . . 605

OpenXML() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 606

Document Preparation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 606

Closing the Document . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 607

Retrieving the XML Information . . . . . . . . . . . . . . . . . . . . . . . . . . . 608

Metaproperties in OpenXML() . . . . . . . . . . . . . . . . . . . . . . . . . . . . 611

What if an XML Document Is Longer Than 8000 Characters? . . . . . . . . . . . . . 613

UpdateGrams . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 615

Executing UpdateGrams . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 617

Element-centric vs. Attribute-centric UpdateGram . . . . . . . . . . . . . . . . . . 619

UpdateGrams with Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . 619

Setting Parameters to Null . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 621

Returning Identifier Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 622

Special Characters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 624

UpdateGrams Behind the Scene . . . . . . . . . . . . . . . . . . . . . . . . . . . 624

Optimistic Locking with UpdateGrams . . . . . . . . . . . . . . . . . . . . . . . . 625

Multiple Records and Multiple Tables in a Single UpdateGram . . . . . . . . . . . . 627

Contents

D_Base / SQL Server 2000 Stored Procedure & XML Programming / Sunderic / 222896-2 / Front Matter

P:\010Comp\D_Base\896-2\fm.vp

Wednesday, April 30, 2003 12:13:55 PM

Color profile: Generic CMYK printer profile

Composite Default screen

DiffGrams . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 628

Using DiffGrams to Insert Data . . . . . . . . . . . . . . . . . . . . . . . . . . . 629

Using DiffGrams to Update Data . . . . . . . . . . . . . . . . . . . . . . . . . . 631

Using DiffGrams to Delete Data . . . . . . . . . . . . . . . . . . . . . . . . . . . 633

Processing Multiple Records Using DiffGrams . . . . . . . . . . . . . . . . . . . . 634

DiffGrams Behind the Scene . . . . . . . . . . . . . . . . . . . . . . . . . . . . 634

Executing DiffGrams Programmatically Using SqlXmlCommand . . . . . . . . . . . 636

Executing DiffGrams Using URLs . . . . . . . . . . . . . . . . . . . . . . . . . . 636

Generating DiffGrams After DataSet Change . . . . . . . . . . . . . . . . . . . . 637

Debugging DiffGrams . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 638

SQLXML BulkLoad . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 639

Executing SQLXML BulkLoad from a .NET Application . . . . . . . . . . . . . . . . 639

Error Log File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 640

Executing BulkLoad from DTS (Using VBScript) . . . . . . . . . . . . . . . . . . . 641

Schema Generation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 642

BulkLoad Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 643

Data Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 643

Table Lock . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 644

Using SQLXML BulkLoad . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 644

Mapping Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 644

XML Web Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 647

SOAP Messages and XML Web Services Architecture . . . . . . . . . . . . . . . . . 647

Using SQLXML to Create XML Web Services . . . . . . . . . . . . . . . . . . . . . 648

Creating .NET SOAP Clients . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 650

Appendix T-SQL and XML Data Types in SQL Server 2000 . . . . . . . . . . . . . . 659

Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 669

xvi SQL Server 2000 Stored Procedure & XML Programming

D_Base / SQL Server 2000 Stored Procedure & XML Programming / Sunderic / 222896-2 / Front Matter

P:\010Comp\D_Base\896-2\fm.vp

Wednesday, April 30, 2003 12:13:55 PM

Color profile: Generic CMYK printer profile

Composite Default screen

elcome to SQL Server 2000 Stored Procedure & XML Programming.

This book identifies and describes the key concepts, tips and techniques,

and best practices the professional developer needs to master in order to

take full advantage of stored procedures in the SQL Server development environment.

Microsoft SQL Server is the relational database management system (RDBMS)

of choice for a growing number of business organizations and professional database

and application developers. The reasons for this growing popularity are quite simple:



Integration No other RDBMS integrates as fully and cleanly with applications

and integrated development environments (IDEs) designed to run on the ubiquitous

Microsoft Windows platform.



Ease of use SQL Server provides Enterprise Manager and Query Analyzer to

allow DBAs to design, develop, deploy, and manage database solutions. These

interfaces automate repetitive tasks and provide simple ways to perform complex

operations. SQL Server integrates seamlessly with development tools such as

Visual Basic and Visual Interdev to allow developers to design and develop

client/server or Internet solutions rapidly.



Flexibility You can use different features within SQL Server to achieve

similar results. (Of course, with flexibility comes choice, and choice means

that the developer is responsible for choosing the most appropriate means of

achieving an end. This book will help you make those choices.)



Power SQL Server makes large amounts of data available to large numbers

of concurrent users while maintaining the security and integrity of the data. At

the time of this writing, SQL Server holds the record in TPC-C benchmark

tests for performance and price/performance (see www.tpc.org).

When I began working with SQL Server, reference materials relating to the

development and deployment of stored procedures were rare and not particularly

helpful. These materials described basic concepts, but the examples presented were

often trivial and not complex enough to be applied to real-world situations in which

aspects such as error handling, debugging, naming conventions, and interfaces to other

applications are critical. As the legions of application developers and development

DBAs migrate from Microsoft Access to SQL Server, and as SQL Server becomes

the leading database for mission-critical application development, the need for more

advanced work on SQL Server stored procedures becomes even more critical.

2 SQL Server 2000 Stored Procedure & XML Programming

D_Base / SQL Server 2000 Stored Procedure & XML Programming / Sunderic / 222896-2 / Chapter 1

P:\010Comp\D_Base\896-2\ch01.vp

Monday, April 28, 2003 1:40:26 PM

Color profile: Generic CMYK printer profile

Composite Default screen

Chapter 1: Introduction 3

D_Base / SQL Server 2000 Stored Procedure & XML Programming / Sunderic / 222896-2 / Chapter 1

Who Should Read This Book

This book has been written to fill this gap, and thus it has been written with a wide

audience in mind. Ideally, it will be neither the first nor the last book you read on SQL

Server, but it may be the one you refer to and recommend the most. Above all, this book

has been written to help professional developers get the most out of SQL Server stored

procedures and SQLXML extensions and to produce quality work for their clients.

If you are an experienced SQL Server developer, you will find this book to be an

essential reference text full of tips and techniques to help you address the development

issues you encounter in the course of your day-to-day development activities.

If you have some experience with SQL Server development, but substantially more in

other programming environments such as Visual Basic, you will find this book useful as

a tool to orient yourself with the SQL Server environment and become proficient more

quickly with SQL Server stored procedure and SQLXML concepts and methods. You

will be able to incorporate effective, swift stored procedures into Visual Basic code and

SQLXML methods and queries into your client Windows or web applications.

If you are a novice SQL Server developer, the concepts, tips, and techniques you

will learn in reading this book and working through the exercises will help you attain

the knowledge, skills, and good habits that will help you become an accomplished

professional.

I hope that this book remains close to your workstation for a long time. Indeed, in the

course of this book’s useful life, you may in turn be all three of the users just described.

What You Will Find in This Book

Each chapter in this book (aside from the one you are reading, which is introductory

in nature) will provide conceptual grounding in a specific area of the SQL Server

development landscape. The first 12 chapters are dedicated to stored procedure

programming, and Chapters 13, 14, and 15 are focused on XML programming on

SQL Server 2000.

As you may have gathered, this chapter describes the content of this book, as well

as its intended audience, and describes a sample database that we will use throughout

the book to demonstrate stored procedure development.

Chapter 2, “The SQL Server Environment,” provides a 30,000-foot overview of

the Transact-SQL language, SQL Server tools, and stored procedure design.

Chapter 3, “Stored Procedure Design Concepts,” explores SQL Server stored

procedure design in greater detail, with particular attention paid to the different

types of stored procedures, their uses, and their functionality.

P:\010Comp\D_Base\896-2\ch01.vp

Monday, April 28, 2003 1:40:26 PM

Color profile: Generic CMYK printer profile

Composite Default screen

剩余696页未读，继续阅读

alexzhang010

粉丝: 0
资源: 10

SQL Server 2000存储过程与XML编程详解

SqlServer学习资料包：基础与问题解决指南

全面SQL学习资源包：教程、PPT、练习题及答案

全面覆盖SQL学习与应用的资料大全

SQL学习资料

sql学习资料worlddb

sql学习

CVI中SQL学习资料

SQL学习资料.rar

pl/sql 学习资料

oracle内部SQL学习资料

最新资源