342
Part III: Advanced T-SQL Data Types and Querying Techniques
■ (^) Elements in your XML need to follow a certain order (FirstName must proceed
LastName).
■ (^) Dealing with optional or mandatory elements.
■ (^) Validation of data types (for example, age is an integer).
■ (^) Enforcing specifi c formats of data (for example, Social Security numbers formatted
as 999-99-9999).
■ (^) Ensuring elements appear only once.
The ability to apply a schema to an XML document is called “typing” your XML. You learn
about typed versus untyped XML shortly. But enough blabbering. Let’s start working with
some data.
The examples throughout this chapter use the following code to help explain and illustrate
how to create, query, and validate XML. Open Microsoft SQL Server Management Studio and
create a new database. Open a new query window and execute the following code against
your new database.
IF EXISTS (SELECT FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[Customer]') AND type in (N'U'))
DROP TABLE [dbo].[Customer]
GO
IF EXISTS (SELECT FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[Item]') AND type in (N'U'))
DROP TABLE [dbo].[Item]
GO
IF EXISTS (SELECT FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[Orders]') AND type in (N'U'))
DROP TABLE [dbo].[Orders]
GO
IF EXISTS (SELECT FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[OrderDetail]') AND type in (N'U'))
DROP TABLE [dbo].[OrderDetail]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[ItemInfo]') AND type in (N'U'))
DROP TABLE [dbo].[ItemInfo]
GO
/** Object: Table [dbo].[Customer] **/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
c14.indd 342c14.indd 342 7/30/2012 4:49:00 PM7/30/2012 4:49:00 PM
http://www.it-ebooks.info