Contents
Overview 1
Lesson: XML Support in SQL Server 2000 2
Lesson: Using HTTP to Access and Input
XML Data 10
Lab 9: Creating SQL Queries Using XML 17
Review 22

Module 9: SQL XML
Information in this document, including URL and other Internet Web site references, is subject to
change without notice. Unless otherwise noted, the example companies, organizations, products,
domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious,
and no association with any real company, organization, product, domain name, e-mail address,
logo, person, places or events is intended or should be inferred. Complying with all applicable
copyright laws is the responsibility of the user. Without limiting the rights under copyright, no
part of this document may be reproduced, stored in or introduced into a retrieval system, or
transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or
otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual

and might not be easily understood by all participants at first.
Make certain that participants know that this module covers a major topic and
that there is an entire course devoted to it. That course is Course 2091A,
Building XML-Enabled Applications Using Microsoft SQL Server 2000.
After completing this module, participants will be able to:
!
Describe how SQL Server 2000 uses XML.
!
Describe how to implement XML support in SQL Server 2000.
!
Write basic XML queries against SQL Server 2000 by using Hypertext
Transfer Protocol (HTTP).
!
Use a basic XML template against SQL Server 2000 to retrieve data.
!
Insert data into SQL Server 2000 by using XML.
!
Describe some of the XML features recently added to SQL Server 2000.

Presentation:
30 Minutes

Lab:
45 Minutes
iv Module 9: SQL XML Materials and Preparation
This section provides the materials and preparation tasks that you need to teach
this module.
According to the Manual Setup Guide, the SQL password is set to 2500.

Required materials
Preparation
tasks
Lab 9: Creating SQL
Queries Usin
g XML
Note
Module 9: SQL XML v Demonstration
This section provides demonstration procedures that will not fit in the margin
notes or are not appropriate for the student notes.
!
Prepare for the demonstration
1. Be sure that you have read and practiced the steps required to successfully
install XML support for IIS.
2. Be sure that you are familiar with the various properties pages for
configuring a virtual directory.
3. Test the setup and configuration, and make sure that it will properly process
a Uniform Resource Locator (URL) query and return the results to the
browser properly.
4. If you discover any problems with the configuration, be sure to fix them
before you give the demonstration, because it can be time consuming to
resolve them.


Explorer, and then navigate to the
\Inetpub\wwwroot\NwindSite folder.
2. Create a new folder named templates and another new folder named
schemas.
3. In the New Virtual Directory Properties dialog box, on the Virtual
Names tab, click New to create the virtual name for the template.
4. In the Virtual Name Configuration dialog box, in the Virtual name box,
type template09
5. In the Type list, click template.
6. Click the ellipsis button (…), browse to the
\Inetpub\wwroot\NwindSite\templates folder, and then click OK.
7. In the Virtual Name Configuration dialog box, click Save.
8. Repeat steps 3 through 7. Use schema09 for the virtual name, schema for
the type, and browse to the schemas folder.
9. Close the New Virtual Directory Properties dialog box.
10. Close the IIS Virtual Directory Management for SQL Server console.

!
Prepare for the demonstration
1. Read the documentation on the XML View Mapper and familiarize yourself
with its architecture, purpose, and functionality.
2. Practice the tutorial carefully more than once.
3. To present this demonstration, use the tutorial to show how the XML View
Mapper works. The steps are not reproduced here. You might want to print
out the tutorial section of the XML View Mapper documentation.

The XML View Mapper
Module 9: SQL XML vii
!
Manipulating XML Using Transact-SQL
This lesson shows participants how Transact-SQL and XML are useful and
powerful when used together. This can take up a lot of the module time, so
move through it briskly.
!
Retrieving XML Data with ADO and ADO.NET
This lesson is a quick overview. The topic is too large for this course, and
there is not time to cover the topic in more depth. If you have time and are
able to, elaborate on the evolution from ADO to ADO.NET. Be prepared for
questions, and be ready to provide examples. Remind participants that there
is an ADO.NET course.
!
Using Mapping Schemas
Explain why mapping is useful and how it fits in with other products and
technologies, particularly Microsoft BizTalk
™
Server 2000. Emphasize the
importance of graphical tools like BizTalk Server. Also, emphasize that
tools like BizTalk Server are new and will become more sophisticated and
friendly for the developer and user.
Appendix
viii Module 9: SQL XML !
Updategrams and Bulk Load
Updategrams and the Bulk Load utility are relatively straightforward, but be
sure to explain why they are needed. Because this topic can be slightly
confusing, be prepared to give additional examples or to elaborate on the

®
SQL Server
™
2000 plays a crucial role in the Microsoft .NET
strategy as a means of enabling the easy use of data with Extensible Markup
Language (XML) technologies.
This module presents a detailed overview of the implementation of XML in
SQL Server 2000 and add-ins to the product since its release in 2000. Key
points are covered here for your understanding. In-depth coverage of these
topics is available in related SQL Server and Microsoft Visual Studio
®
courses.
After completing this module, you will be able to:
!
Describe how SQL Server 2000 uses XML.
!
Describe how to implement XML support in SQL Server 2000.
!
Write basic XML queries against SQL Server 2000 by using Hypertext
Transfer Protocol (HTTP).
!
Use a basic XML template against SQL Server 2000 to retrieve data.
!
Insert data into SQL Server 2000 by using XML.
!
Describe some of the XML features recently added to SQL Server 2000. For in-depth coverage of the implementation of XML in
SQL Server 2000, see Course 2091A, Building XML-Enabled Applications

releases of new XML support in SQL Server are being provided through add-in
features. You will learn about some of these in this lesson.
After completing this lesson, you will be able to:
!
Describe key XML features supported in SQL Server 2000.
!
Describe the architectural features providing XML support in
SQL Server 2000.
!
Identify and describe features added to SQL Server 2000 for XML support.
!
Explain the range of ways in which SQL Server 2000 provides support for
XML in general and for Microsoft .NET in particular.

Introduction
Lesson objectives
Module 9: SQL XML 3 XML Features in SQL Server 2000
!
HTTP access to SQL Server
!
XML Views
!
XSD and XPath support for queries
!
Retrieve and write XML data
"
FOR XML clause

<ROOT+xmlns:sql="urn:schemas-microsoft-com:xml-
sql"><sql:query>SELECT+*+FROM+Employees+FOR
+XML+AUTO</sql:query></ROOT>
Query that references a
template file
http://bandicoot/nwind/MyTemplates/mytemplate.xml
Query that references an
XSD schema and includes
XPath
http://bandicoot/nwind/MySchemas/myschema.xml
/Employee[@EmployeeID="2"]
Query that references a
database object directly
http://bandicoot/nwind/MyObjects/XpathQuery

Introduction
HTTP access
Examples
4 Module 9: SQL XML An XML View allows a query to be processed by using an XML-Data Reduced
(XDR) schema. An XML View can be queried by using XML Path Language
(XPath). This is analogous to using the CREATE VIEW statement in SQL.
By using XPath and XSD schemas, which enable you to map database
information, you can directly query the database. This approach has an
important security advantage over allowing URL queries. Template files
enhance security by hiding details of the database schema from the end user.
Normally, SQL queries return data in rowset format. An SQL query that uses
the FOR XML clause returns data in XML format. You can further qualify the