Programming Microsoft SQL Server 2000 with XML

Graeme Malcolm is a principal technologist for Content Master Ltd., and apart from the first edition of this title, he has also written several Microsoft training courses and presented at numerous conferences. Graeme is being presented to us from Content Master, a rich source for Microsoft for developing sample solutions, white papers, and training materials for other groups.

Acknowledgments xiii Introduction xv 1 SQL, XML, and the Business Internet 1 Relational Data and SQL 1 The Challenge of Integration 3 XML to the Rescue 4 What Is XML? 4 Namespaces 10 Building Solutions with XML 13 Representing Business Entities with XML 13 Navigating XML Documents with XPath 16 Specifying a Location Path 17 Using Criteria in Location Paths 19 XSL Style Sheets 19 XSL Style Sheet Documents 19 Using Multiple Templates in a Style Sheet 26 Applying Style Sheets 28 The Document Object Model (DOM) 30 XML Schemas 31 Schema Languages 32 Validating an XML Document 37 Web-Enabling a Business Using XML 41 Using XML in Business to Consumer (B2C) Solutions 41 Using XML in Business to Enterprise (B2E) Solutions 41 Using XML in Business to Business (B2B) Solutions 42 Web-Enabling Northwind Traders with XML 42 2 Retrieving XML Data Using Transact-SQL 45 The SELECT FOR XML Statement 46 Using RAW Mode 47 Using Joins in RAW Mode Queries 47 Using Column Aliases to Specify Attribute Names 48 Using AUTO Mode 48 Using Aliases in AUTO Mode Queries 49 Joins in AUTO Mode 50 Using the ELEMENTS Option 50 Using EXPLICIT Mode 52 Identifying the Required Universal Table 52 Directives in EXPLICIT Mode Queries 54 Retrieving Subelements with the element and xml Directives 55 Using EXPLICIT Mode to Retrieve Related Data 56 Sorting Data with the hide Directive 61 Using the xmltext Directive to Retrieve XML Values 62 Retrieving CDATA with the cdata Directive 64 Using the ID, IDREF, and IDREFS Directives and the XMLDATA Option 64 Retrieving Binary Fields with the BINARY BASE64 Option 66 3 Inserting XML Data Using OpenXML 69 A Model for Receiving XML Documents 69 Passing XML Data to a Stored Procedure 70 Parsing and Shredding an XML Document 70 Cleaning Up 71 Generating Rowsets with the OpenXML Function 72 Specifying the Row Pattern 73 Using Flags to Retrieve Attributes and Elements 76 Defining the Rowset Schema 77 Using OpenXML to Insert Data 79 Inserting Data into an Existing Table 80 Creating a New Table 82 Using OpenXML to Retrieve XML Metadata 83 Inserting Overflow Data 84 Creating an Edge Table 86 4 Publishing Databases with IIS 89 SQL Server HTTP Publishing Architecture 89 Publishing a Database in an IIS Virtual Directory 91 Specifying a Name and a Path for the Virtual Directory 92 Securing the Virtual Directory 93 Specifying the Data Source 97 Specifying Data Access Settings 98 Creating Virtual Names 100 Specifying Advanced Options 102 Scripting Virtual Directory Configuration 103 The SQLVDirControl Object 104 The SQLVDirs Collection Object 105 The SQLVDir Object 106 The VirtualNames Collection Object 107 The VirtualName Object 108 Using URL Queries to Test a Virtual Directory 108 Retrieving XML Documents Using a URL Query 108 Encoding Special Characters in a URL Query 109 Specifying a Style Sheet in a URL Query 110 Specifying a Content Type 111 Specifying a Character Set 113 Executing Stored Procedures in a URL Query 113 5 Retrieving Data with XML Templates 115 What Is a Template? 116 Creating Templates 116 Including Multiple Queries in a Template 117 Calling a Stored Procedure in a Template 119 Creating Parameterized Templates 119 Applying Style Sheets with Templates 121 Applying a Style Sheet on the Server 123 Applying a Style Sheet on the Client Side 126 Posting a Template 127 Posting a Template from an HTML Form 128 Posting Templates from Non-HTML Clients 129 6 Accessing XML Data Using ADO 133 An ADO Refresher 133 OLE DB Providers for SQLXML 134 Using the SQLOLEDB Provider 135 Using the SQLXMLOLEDB Provider 136 Retrieving XML with ADO 137 Command Dialects 137 Retrieving Results in a Stream 139 Executing FOR XML Statements 140 Calling a Stored Procedure That Returns an XML Fragment 141 Adding a Root Element to the Results 142 Executing Templates with ADO 144 Applying a Style Sheet with the xsl Property 146 Managing the Output Format with the Output Encoding Property 150 Retrieving XML into a DOMDocument Object 150 7 Accessing XML Data from .NET Applications 153 Introducing the .NET Framework 154 Building .NET Applications 155 Using ADO.NET to Retrieve XML from SQL Server 156 Retrieving XML Using the SqlCommand Class 157 Retrieving XML from a DataSet Class 160 Using the SqlXml Managed Classes 163 Retrieving XML with the SqlXmlCommand Class 164 Using the SqlXmlParameter Class 178 Using the SqlXmlAdapter Class to Retrieve a DataSet 182 8 Retrieving Data with Mapping Schemas 187 Using Default Mappings 188 Using Annotations to Map Data 190 Mapping an XML Document to a Single Table 190 Mapping XML Data to Multiple Tables 192 Defining Constants in a Schema 201 Using ID, IDREF, and IDREFS Annotations 204 Excluding Fields 211 Filtering Values 213 Specifying a Target Namespace 215 Retrieving Binary Data 219 Retrieving CDATA Sections 224 Retrieving Overflow Columns 225 Hiding Fields with the hide Annotation 226 Using a Mapping Schema to Retrieve Data 227 Using an XPath Expression with a Schema 227 Using Mapping Schemas Through IIS 229 Using Mapping Schemas with ADO 234 Using Mapping Schemas in .NET Framework Programs 239 9 Client-Side XML Processing 247 Client-Side XML Architecture 248 Client-Side Query Syntax 249 Base Table Names in NESTED Mode 249 Using GROUP BY in NESTED Mode 250 Using Additional FOR XML Keywords in NESTED Mode 250 Using NESTED Mode with Timestamp Columns 251 Retrieving XML from Stored Procedures That Return Rowsets 252 Single Rowset Limitation of Client-Side XML Processing 253 Using Templates to Execute Client-Side FOR XML Queries 253 Executing a Client-Side Template in a SQLISAPI Virtual Directory 254 Executing a Client-Side Template with ADO 255 Executing a Client-Side Template with the SqlXmlCommand Class 255 Using an XPath Query in a Client-Side Template 257 Configuring a SQLISAPI Virtual Directory for Client-Side XML Processing 258 Using the ClientSideXml Property of the ADO Command Object 260 Using the ClientSideXml Property of the SqlXmlCommand Managed Class 261 10 Modifying Data with Updategrams 265 Anatomy of an Updategram 266 Mapping Data in an Updategram 267 Using the Default Mapping 267 Using a Mapping Schema 268 Handling NULLs in an Updategram 269 Using Parameters in an Updategram 270 Passing NULL as a Parameter 271 Updating Multiple Rows 271 Using the id Attribute 271 Using Multiple Before and After Elements 272 Updategram Results 273 Updategram Error Messages 273 Applying a Style Sheet 273 Inserting Rows with an Updategram 274 Inserting Multiple Rows 275 Using an IDENTITY Column Value 275 Generating a Globally Unique Identifier (GUID) 278 Inserting Relational Data with a Mapping Schema 280 Deleting Data with an Updategram 283 Updategrams and Concurrency 284 Low Concurrency Protection 284 Intermediate Concurrency Protection 285 High Concurrency Protection 286 Combining INSERT, UPDATE, and DELETE Operations in an Updategram 287 Executing Updategrams 288 Executing Updategrams in a SQLISAPI Virtual Directory 288 Executing Updategrams with ADO 289 Executing Updategrams with the SqlXml Managed Classes 291 11 Modifying Data with DiffGrams 297 Anatomy of a DiffGram 297 DiffGrams and Mapping Schemas 298 Updating Data with a DiffGram 299 Inserting Data with a DiffGram 300 Deleting Data with a DiffGram 303 Handling Relationships with the parentId Attribute 304 Combining INSERT, UPDATE, and DELETE Operations in a DiffGram 306 Executing DiffGrams 307 Executing DiffGrams in a SQLISAPI Virtual Directory 307 Executing DiffGrams with ADO 308 Executing DiffGrams with the SqlXml Managed Classes 309 12 Bulk Loading XML Data 315 The XML Bulk Load Component 315 How the XML Bulk Load Component Works 315 Bulk Loading XML Data 318 Maintaining Referential Integrity 320 Generating the Database Schema 328 13 Accessing Data Using SOAP 335 Web Services and SOAP 335 SOAP Request Messages 336 SOAP Response Messages 337 Web Services Description Language (WSDL) 338 Creating a Web Service with SQLXML 3.0 343 Creating a Web Service with the Configure IIS Support Tool 343 Configuring a Web Service 344 Creating a Web Service Programmatically 346 SOAP Virtual Name Files 349 SOAP Responses from SQLXML Web Services 357 Returning Rows as an Array of XML Elements 357 Returning Rows as an Array of DataSets 360 Returning Rows as a Single DataSet 362 Returning Output Parameters 363 Retrieving Error Information 364 Posting SOAP Messages to a SQLXML Web Service 367 Building a Client with the SOAP Toolkit 368 Calling an XML Web Service Method with the SOAP Toolkit 369 Retrieving Parameters and Return Values with the SOAP Toolkit 370 Building a SOAP Client with the .NET Framework 371 Generating a Proxy Class with the .NET Framework 371 Calling a Web Service Method with the .NET Framework 372 Retrieving Parameters and Return Values with the .NET Framework 376 14 Case Study: An E-Commerce Solution 379 Installing the Sample Application 379 Sample Application Scenario 380 Using the Sample Application 381 Implementation Details of the Sample Application 383 The NorthwindTraders Virtual Directory 383 The Bs Beverages Web Site 385 The Order Management Application 397 The Catalog Import Utility 400 INDEX 403