CCHAPTER 11MCTS SQL SERVER 2008 EXAM 432SQL SERVER XML SUPPORTEXAM OBJECTIVES IN THIS CHAPTER

20. C

Chapter 11

MCTS SQL Server 2008

Exam 432

SQL Server XML Support

Exam objectives in this chapter:

Working with XML Data

■

HTTP Endpoints

Web Services

XQuery Explained

XPath

Exam objectives review:

˛ Summary of Exam Objectives

˛ Exam Objectives Fast Track

˛ Exam Objectives Frequently Asked Questions

˛ Self Test

˛ Self Test Quick Answer Key

479480 Chapter 11 • SQL Server XML Support

Introduction

This chapter will serve as an overview of XML for the database administrator

(DBA). In this chapter, XML, XQuery, and XPath will all be covered in the context

of what a DBA needs to know.

This chapter will also cover why you would set up HTTP endpoints, as well as

aspects of HTTP endpoints and some of the uses for them.

When you are working with data that is semistructured, unstructured, or of an

unknown structure, XML can provide platform-independence and ensure

portability by using structural and semantic markup. SQL Server 2008 provides

native XML features such as interoperability between XML and relational data,

the ability to index XML data for efficient query processing and good scalability,

SOAP, ADO.NET, and OLE DB access, in addition to the ability to back up,

recover, and replicate XML data. We start reviewing these features by taking

a look at XML indexing.

Understanding XML Indexing

SQL Server 2008 allows for the creation of indices only on the XML data type.

Indices are used to optimize XQuery queries. They index all tags, values, and paths

over the XML instances in the XML data type column. SQL Server provides two

key types of indexing on the XML data type: CREATE PRIMARY XML INDEX

and CREATE XML INDEX.

E

xam

W

arning

The first index on the XML data type column must be the primary

XML index.

The CREATE PRIMARY XML INDEX removes the need for SQL Server to

shred your XML data during every query. It should be used when you store large

XML documents in each row of a table. You cannot create a primary XML index

on a non-XML column, and you can only create one primary XML index on a

given XML column. Figure 11.1 presents the syntax for creating a CREATE

PRIMARY XML INDEX.

SQL Server XML Support • Chapter 11 481

Figure 11.1 CREATE PRIMARY XML INDEX Syntax

CREATE PRIMARY XML INDEX [index_name]ON table_name (xml_column_name)

New & Noteworthy…

XML Data Type

XML instances are stored in XML type columns as large binary objects (BLOBS).

These XML instances can be large, and the stored binary representation

of XML data type instances can be up to 2 GB.

Figure 11.2 shows the syntax you can use to create a table and insert some

values into the table.

Figure 11.2 Sample XML Data

CREATE TABLE [dbo].[XML_Table]([pk] [int] IDENTITY(1,1) NOT NULL,[customerName] [varchar](255) NULL,[customerPhone] [varchar](255) NULL,[customerAddress] [xml] NULL,CONSTRAINT [PK_XML_Table] PRIMARY KEY CLUSTERED([pk] ASC))INSERT INTO XML_Table ([customerName],[customerPhone],[customerAddress]) VALUES ( /* customerName - VARCHAR(255) */ 'Monica Banning',