Professional SQL Server 2005 XML ppt

Professional

SQL Server™ 2005 XML

Scott Klein

01_597922 ffirs.qxp 12/3/05 12:24 AM Page i

Professional

SQL Server™ 2005 XML

Scott Klein

01_597922 ffirs.qxp 12/3/05 12:24 AM Page i

Professional SQL Server™ 2005 XML

Published by

Wiley Publishing, Inc.

10475 Crosspoint Boulevard

Indianapolis, IN 46256

www.wiley.com

Copyright © 2006 by Wiley Publishing, Inc., Indianapolis, Indiana

Published simultaneously in Canada

ISBN-13: 978-0-7645-9792-3

ISBN-10: 0-7645-9792-2

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

1MA/SR/RS/QV/IN

Library of Congress Cataloging-in-Publication Data:

Klein, Scott, 1966-

Professional SQL Server 2005 XML / Scott Klein.

p. cm.

Includes index.

ISBN-13: 978-0-7645-9792-3 (paper/website)

ISBN-10: 0-7645-9792-2 (paper/website)

1. SQL server. 2. Client/server computing. 3. XML (Document markup language) I. Title.

QA76.9.C55K545 2005

005.2’768--dc22

2005029721

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any

means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections

107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or

authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood

Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should

be addressed to the Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256,

(317) 572-3447, fax (317) 572-4355, or online at http://www.wiley.com/go/permissions.

LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REP￾RESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CON￾TENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT

LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED

OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED

HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING

THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PROFES￾SIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PRO￾FESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE

FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO

IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT

MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR

WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE

AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED

BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ.

For general information on our other products and services please contact our Customer Care Department within

the United States at (800) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002.

Trademarks: Wiley, the Wiley logo, Wrox, the Wrox logo, Programmer to Programmer, and related trade dress are

trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other

countries, and may not be used without written permission. All other trademarks are the property of their respective

owners. Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this book.

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be

available in electronic books.

01_597922 ffirs.qxp 12/3/05 12:24 AM Page ii

About the Author

Scott Klein is a software developer and architect, and his passion for SQL Server, .NET, and all things

XML led him to Greenville, South Carolina, where he currently works as a SQL/.NET developer for CSI,

a software solutions company. He has written several articles for TopXML (www.TopXLM.com) and is a

frequent speaker at SQL Server and .NET user groups around Greenville and the surrounding areas.

When he is not sitting in front of a computer or spending time with his family, he can usually be found

aboard his Yamaha at the local motocross track.

Acknowledgments

Writing a book is a daunting task. Writing your first book is just downright intimidating. The better the

support people you have assisting and guiding you, the easier the task becomes. Therefore, it is only

appropriate to thank those individuals who made this project much easier than it could have been.

First and foremost, Clay Andres for sticking with the book idea when it seemed like the idea wasn’t

going anywhere.

A huge thanks to the folks at Wiley for making this book happen. Brian Herrmann, my awesome devel￾opment editor, was truly that. With my being a first time book author, Brian was a tremendous help and

a sheer delight to work with. Thanks, Brian.

Thanks also to Jim Minatel, for accepting the book idea and letting me write it, and to Derek Comingore,

for technically reviewing this book and providing priceless feedback and help. Thank you, Derek.

I would be remiss if I didn’t mention the following individuals for their assistance in providing informa￾tion. Primarily, I must thank Irwin Dolobowsky, my main contact at Microsoft. Irwin was my go-to guy,

a life saver on many occasions. If he didn’t know the answer, he knew who did or would find out who

did. Also included in the list of Microsoft people to thank are Michael Rys, Arpan Desai, Srik Raghavan,

Mark Fussell, Vineet Rao, and Beysim Sezgin. Thank you, to all of you.

Enough cannot be said about the love and support of my family. For my wife, Lynelle, who held the

house together for the 8+ months I spent upstairs. And to my children, who were patient with their

father knowing that they soon would get their dad back. I love you all.

I can only hope the next book is less daunting.

01_597922 ffirs.qxp 12/3/05 12:24 AM Page iii

Credits

Senior Acquisitions Editor

Jim Minatel

Development Editor

Brian Herrmann

Technical Editor

Derek Comingore

Production Editors

Jonathan Coppola

Tim Tate

Copy Editor

Kathryn Duggan

Editorial Manager

Mary Beth Wakefield

Production Manager

Tim Tate

Vice President and Executive Group Publisher

Richard Swadley

Vice President and Executive Publisher

Joseph B. Wikert

Project Coordinator

Kristie Rees

Graphics and Production Specialists

Carrie A. Foster

Lauren Goddard

Denny Hager

Joyce Haughey

Jennifer Heleine

Alicia B. South

Quality Control Technicians

Laura Albert

John Greenough

Proofreading and Indexing

TECHBOOKS Production Services

01_597922 ffirs.qxp 12/3/05 12:24 AM Page iv

Contents

Introduction xv

Part I: Introduction to SQL Server 2005 XML 1

Chapter 1: What’s New in Version 2.0 of the .NET Framework for XML 3

System.xml Version 2.0 Enhancements and New Features 4

Performance 5

XMLTextWriter and XMLTextReader 5

XMLReader and XMLWriter 5

XSLT Processing 5

XML Schema Validation 6

Type Support 6

XPathDocument 8

XPathNavigator 9

XML Query Architecture 10

XmlReader, XmlReaderSettings, XmlWriter, and XmlWriterSettings 11

Summary 13

Chapter 2: What’s New in SQL Server 2005 XML 15

xml data type 16

xml data type Column 17

xml Variable 17

XML Parameter 18

Function Return 18

Indexes on the xml data type 19

Primary Index 19

Secondary Index 19

XQuery 21

XQuery Structure 21

Additional Concepts 22

XML Data Modification Language 26

Insert 27

Delete 28

Update 28

Transact-SQL Enhancements 29

02_597922 ftoc.qxp 12/3/05 12:23 AM Page v

vi

Contents

FOR XML 29

xml data type Integration 30

Nesting FOR XML Expressions 30

PATH Mode 31

Assigning FOR XML Results 32

HTTP SOAP Access 33

Summary 33

Chapter 3: Installing SQL Server 2005 35

Where to Get SQL Server 2005 Beta 2 Express Edition 35

Installing SQL Server 2005 36

Summary 50

Part II: Server-Side XML Processing in SQL Server 2005 51

Chapter 4: xml data type 53

untyped versus typed XML 54

untyped XML 54

typed XML 56

Making Changes to the xml data type Column 58

Converting from untyped to typed 59

xml data type Methods 59

query() 60

value() 61

exist() 64

nodes() 66

modify 68

Combining Methods 69

Using Operators with Methods 71

Defaults, Constraints, and Computed Columns 73

Defaults 73

Constraints 74

Computed Columns 76

Creating Views 79

XML Settings Options 80

xml data type Best Practices 81

Why and Where 81

typed versus untyped 81

Constraints 82

Limitations 82

Summary 82

02_597922 ftoc.qxp 12/3/05 12:23 AM Page vi

vii

Contents

Chapter 5: Querying and Modifying XML Data in SQL Server 2005 85

XQuery 85

XQuery Structure and Concepts Review 86

XQuery Prolog 89

XQuery Path Expressions 91

XQuery XML Construction 93

FLWOR Statement 95

XQuery Sorting 97

XML Data Modification Language 99

insert 99

delete 105

replace value of 106

Summary 110

Chapter 6: Indexing XML Data in SQL Server 2005 111

Primary XML Index 112

Secondary XML Index 114

PATH 115

VALUE 116

PROPERTY 117

Content Indexing 118

CONTAINS() 120

Altering XML Index 122

Setting Options for XML Indexing 124

Best Practices 124

Ten Commandments of XML Index Creation 125

Summary 125

Chapter 7: XML Schemas in SQL Server 2005 127

Managing XML Schema Collections 128

Creating XML Schema Collections 128

Dropping XML Schema Collections 135

Altering XML Schema Collections 136

Viewing XML Schema Collections 138

XML Schema Collection Permissions 140

Granting Permissions 140

Denying Permissions 142

Revoking Permissions 144

Guidelines and Limitations 146

Summary 146

02_597922 ftoc.qxp 12/3/05 12:23 AM Page vii

viii

Contents

Chapter 8: Transact-SQL Enhancements to FOR XML and OPENXML 147

FOR XML 148

The TYPE Directive 148

RAW Mode Enhancements 151

AUTO Mode Enhancements 153

EXPLICIT Mode Enhancements 155

PATH Mode 157

Nesting FOR XML 162

XSD Schema Generation 165

Things to Watch Out For 166

OPENXML 167

Summary 169

Chapter 9: CLR Support in SQL Server 2005 171

The Great Debate 172

Integration Overview 173

Limitations of T-SQL 174

Introduction to Managed Code 176

Advantages of CLR Integration 180

Choosing Between T-SQL and Managed Code 181

Security 182

Invocation 182

Table-Access 182

Gated 183

CLR Security Integration Goals 183

Summary 183

Part III: Client-Side XML Processing in SQL Server 2005 185

Chapter 10: Client-Side Support for the xml data type 187

SqlXml Class 187

Introducing the CreateReader Method 188

Using the SqlXml Class 188

Updating Data with the SqlXml Class 195

Inserting Data with the SqlXml Class 197

Summary 198

02_597922 ftoc.qxp 12/3/05 12:23 AM Page viii

ix

Contents

Chapter 11: Client-Side XML Processing with SQLXML 4.0 199

SQL Native Client 200

SQL Native Client and MDAC Differences 201

Deployment Considerations 201

xml Data Type Support 201

CreateReader() 202

SQLXML 4.0 Queries with ADO 202

Client-Side Formatting with FOR XML 206

SQLXML Architecture 207

Choosing Between Client-Side and Server-Side XML Formatting 207

FOR XML Modes 208

Summary 210

Chapter 12: Creating and Querying XML Views 211

XML Views and XSD Schemas 211

sql:field 212

sql:relation 213

sql:relationship 214

sql:key-fields 218

Querying XML Views 220

Best Practices 228

Summary 228

Chapter 13: Updating the XML View Using Updategrams 231

Overview and Structure 232

Mapping Schemas and Updategrams 233

Implicit Mapping 233

Explicit Mapping 233

Modifying Data 234

Inserting Data 235

Deleting Data 242

Updating Data 245

Passing Parameters 249

Updategram Concurrency 251

Low-Level Concurrency Protection 251

Medium-Level Concurrency Protection 252

High-Level Concurrency Protection 252

NULL Handling 253

Updategram Security 255

Guidelines and Limitations 255

Summary 255

02_597922 ftoc.qxp 12/3/05 12:23 AM Page ix

x

Contents

Chapter 14: Bulk Loading XML Data Through the XML View 257

Bulk Load Overview 257

XML Data Streaming 258

Bulk Load Operation Modes 258

Bulk Load Object Model 259

Execute Method 259

BulkLoad Property 260

CheckConstraint Property 260

ConnectionCommand Property 260

ConnectionString Property 261

ErrorLogFile Property 261

ForceTableLock Property 261

IgnoreDuplicateKeys Property 262

KeepIdentity Property 262

SchemaGen Property 262

SGDropTables Property 263

SGUseID Property 263

TempFilePath Property 263

Transaction Property 264

XMLFragment Property 264

Bulk Load in a .NET Environment 265

Security Issues 270

Guidelines and Limitations 270

Summary 271

Chapter 15: SQLXML Data Access Methods 273

SQL Native Client 274

SQL Native Client versus MDAC 274

SQLXMLOLEDB Provider 275

SQLXML Managed Classes 289

Web Services 292

Summary 293

Chapter 16: Using XSLT in SQL Server 2005 295

XSLT Overview 296

Nodes 299

Templates 300

XSLT Changes 301

XslCompiledTransform 301

XsltSettings 306

02_597922 ftoc.qxp 12/3/05 12:23 AM Page x

xi

Contents

Moving to the New 307

Guidelines 308

Summary 308

Part IV: SQL Server 2005, SqlXml, and SOAP 309

Chapter 17: Web Service (SOAP) Support in SQL Server 2005 311

SOAP Overview 311

SOAP in SQL Server 2005 312

How Native XML Works 312

Native XML Access Versus SQLXML 314

Native XML Access Benefits 314

Native XML Support Requirements 314

SOAP Configuration 315

Web Methods 315

Endpoints 316

Guidelines and Limitations 329

Best Practices 330

Performance 330

Security 333

Deployment Scenarios 334

Summary 335

Chapter 18: SOAP at the Client 337

Consuming and Using an Endpoint 337

Granting Permissions 339

Building the Client Application 341

Securing an Endpoint 351

Summary 352

Chapter 19: Web Service Description Language (WSDL) 355

WSDL File Overview 356

WSDL File Contents 356

The WSDL Namespace 357

The <types> Element 357

The <message> Element 358

portType 360

Binding 362

Services 363

02_597922 ftoc.qxp 12/3/05 12:23 AM Page xi

xii

Contents

Default WSDL File 364

Mapping SQL Server to XSD Types 364

Mapping SQL Server to CLR Types 365

xml data type 366

Simple WSDL File 366

Custom WSDL File 367

Summary 373

Part V: SQL Server 2005 and Visual Studio 2005 375

Chapter 20: SQL Server 2005 SQLXML Managed Classes 377

SQLXML Managed Classes Object Model 378

SqlXmlCommand Object 378

SqlXmlParameter Object 383

SQLXMLAdapter Object 384

Examples 385

Summary 395

Chapter 21: Working with Assemblies 397

Assemblies 397

Enabling CLR Integration 398

Managed Code 400

Assembly Security 414

SAFE 414

EXTERNAL_ACCESS 414

UNSAFE 414

Summary 415

Chapter 22: Creating .NET Routines 417

Overview 417

Data Access 418

Namespace Requirements 418

SqlContext Object 418

SqlPipe Class 419

SqlDataRecord Class 419

User-Defined Procedures 420

ByRef Output Parameter 420

Returning Results via SqlDataRecord 422

02_597922 ftoc.qxp 12/3/05 12:23 AM Page xii

xiii

Contents

User-Defined Triggers 423

SqlTriggerContext Class 424

INSERT 425

DELETE 428

UPDATE 430

Scalar-Valued UDFs 433

The Easy Way 435

Summary 438

Chapter 23: ADO.NET 441

xml data type 441

Asynchronous Operations 446

Multiple Active Result Sets 449

Query Notifications 451

Summary 453

Chapter 24: ADO.NET 2.0 Guidelines and Best Practices 455

xml data type 455

GetSqlXml 455

ExecuteXmlReader 457

MARS 457

Asynchronous Operations 458

Blocking 458

Error Handling 459

Canceling Commands 459

Query Notification 459

Summary 460

Chapter 25: Case Study — Putting It All Together 461

Existing Application and Infrastructure 462

Current Database Design 462

Current XML Use 463

Partnership Information 463

Current Web Service Use 464

Shortcomings 464

Selecting SQL Server 2005 Features 464

02_597922 ftoc.qxp 12/3/05 12:23 AM Page xiii