SQL Server

Building XML-Enabled Applications Using Microsoft SQL Server 2000
(Microsoft Training Course: 2091) - 3 days - £1700 exc VAT

> Target Audience
The goal of this course is to provide students with the knowledge and skills they need to build applications that exchange data with Microsoft SQL Server in Extensible Markup Language (XML) format.
This course is intended for developers of database client applications, including Internet and N-tiered application clients.
It is also intended for SQL Server database developers who need to query, insert, update, or delete data from a database by using XML, and for SQL Server database administrators who need to publish SQL data as XML.
> Course outline
  1. Using XML and Microsoft SQL Server 2000
  2. Retrieving XML Data
  3. Manipulating XSL Using Transact SQL
  4. Using ADO to Access XML Data
  5. Using HTTP to Access XML Data
  6. Using Mapping Schemas
  7. XML for SQL Tools

Supplementary InformationThis course has now retired and is no longer available. We suggest you look at Courses [[{http://www.F1Comp.Co.UK/computer_training/Microsoft_Training_Courses/SQL-Server-2005/2788.aspx}2788: Designing High Availability Database Solutions Using Microsoft SQL Server 2005]] and [[{http://www.F1Comp.Co.UK/computer_training/Microsoft_Training_Courses/SQL-Server-2008/6232.aspx}6232: Implementing a Microsoft SQL Server 2008 Database]].
Module 1: Using XML and Microsoft SQL Server 2000
  • Overview of XML
  • Manipulating XML Data with SQL Server 2000
  • Lab: Working with XML
  • Representing business data by using XML.
  • Referencing an existing XSL style sheet to format or transform an XML document.
  • Using basic Document Object Model (DOM) methods to process an XML document.
  • Using XPath expressions in XSL and DOM programming.
  • Listing the XML features in SQL Server 2000.
  • Describing the SQL Server XML architecture.
Module 2: Retrieving XML Data
  • Using the FOR XML Clause to Retrieve Data
  • Controlling XML Output
  • Generating Custom XML Formats
  • Lab 2.1: Retrieving XML Data
  • Lab 2.2: Explicit Queries
  • Using XPath expressions within the DOM methods selectNodes() and selectSingleNode().
  • Writing XPath location paths to select specific nodes from an XML document.
  • Writing conditional expressions in Xpath.
  • Using XPath operators and functions to write more sophisticated expressions.
Module 3: Manipulating XSL Using Transact SQL
  • Creating a Rowset from an XML Document
  • Processing Nested Documents
  • Working with Meta-Properties
  • Lab: Using OpenXML
  • Using OpenXML statements to create rowsets from single-level XML documents.
  • Producing rowsets from complex XML documents by using OpenXML.
  • Retrieving elements, attributes, or both from an XML document by using the Flags parameter.
  • Using XPath expressions to create rowsets from nested XML documents.
Module 4: Using ADO to Access XML Data
  • Retrieving XML Data with ADO
  • Formatting Query Results
  • Lab 4.1: Creating an ADO Client Application
  • Lab 4.2: Using ADO in a Web Site
  • Constructing an XML Query from Visual Basic.
  • Using a Command object and Stream objects to submit an XML query.
  • Applying a style sheet to the results of an XML query by using ADO.
  • Returning XML query results to the ASP Response object
  • Specifying the Content-Type and Output Encoding options for an XML query.
Module 5: Using HTTP to Access XML Data
  • Enabling HTTP Access to SQL Databases
  • Publishing XML Templates on the Server
  • Posting Query Templates from the Client
  • Publishing a Database Object
  • Lab 5.1: Publishing a SQL Server Database
  • Lab 5.2: Using XML Templates
  • Creating an IIS virtual directory that references a SQL Server 2000 database.
  • Testing the HTTP connectivity to a SQL Server 2000 database.
  • Creating an XML template to retrieve XML data over HTTP.
  • Creating a client that uses the POST method to publish templates to SQL Server 2000.
  • Using HTTP to gain access to database objects.
Module 6: Using Mapping Schemas
  • Using Mapping Schemas
  • Using Schemas to Retrieve Data
  • Making Multiple Tables
  • Annotating an Existing Schema
  • Lab 6.1: Using Mapping Schemas
  • Lab 6.2: Mapping Multiple Tables
  • Lab 6.3: Annotating an Existing Schema
  • Describing the uses and structure of annotated schemas.
  • Creating annotated schemas that map business documents to database entities.
  • Using XPath to access data via an annotated schema.
  • Using ADO to request data by using an annotated schema.
  • Accessing data by using a schema over HTTP.
Module 7: XML for SQL Tools
  • Modifying Data with Updategrams
  • Inserting Data with the Bulk Load Component
  • Lab 7.1: Using Updategrams
  • Lab 7.2: Bulk-Loading XML Data
  • Inserting, updating, and deleting data by using updategrams
  • Loading XML data into SQL Server tables by using the Bulk Load component.
> Pre-Requisites
Before attending this course, students must:
  • Have written basic Transact SQL statements to query, insert, update, and delete information from a database, and used joins to retrieve information from multiple tables.
  • Have knowledge of relational database design, including the use of primary and foreign keys. Students should know how to create databases, tables, and stored procedures. Students should also be familiar with the SQL Server Query Analyzer and SQL Profiler tools.
  • Have knowledge of basic XML formats and techniques, including attributes and elements, schemas, the Extensible Stylesheet Language (XSL) and Extensible Stylesheet Language Transformations (XSLT), and the XML Path Language (XPath).
  • Be able to access a data source by using ADO.
  • Be able to write VBScript code.
  • Be able to read standard Hypertext Markup Language (HTML), and build a form to gather and present data.
  • Be able to create Active Server Pages (ASP pages) by using the ASP Programming model. Skills include using the POST and GET methods, using the Response object, and using the Session object.
  • Taking one or more of the following courses will help the students meet the prerequisites and get more out of the course:
  • Course 1905, Building XML-Based Web Applications
  • Course 2073, Programming a Microsoft SQL Server Database
> Purpose
After completing this course, students will be able to describe the XML features provided by SQL Server 2000 and the SQL Server-XML architecture; generate and specify the structure of an XML document from SQL Server data by using Transact-SQL; insert XML data into SQL Server and use XML to modify existing data; use ADO to access SQL Server data and generate XML; publish a SQL Server database on the Internet/intranet over HTTP and query the data by using a Uniform Resource Locator (URL); create and use XML templates to query the database; create and use annotated schemas to query and transform SQL Server data; and describe how SQL Server and XML are used in real-world Internet applications
> Supplementary Information
This course has now retired and is no longer available. We suggest you look at Courses [[{http://www.F1Comp.Co.UK/computer_training/Microsoft_Training_Courses/SQL-Server-2005/2788.aspx}2788: Designing High Availability Database Solutions Using Microsoft SQL Server 2005]] and [[{http://www.F1Comp.Co.UK/computer_training/Microsoft_Training_Courses/SQL-Server-2008/6232.aspx}6232: Implementing a Microsoft SQL Server 2008 Database]].