MSDN WebCast Demo: Typing XML
-- Typing
-- Execute every statement in the sample in order
-- Set filename in loading to correct location
-- (c) 2004, 2005 Microsoft Corp.
use master
USE msdn2005
GO
-- Load schema from file
declare @x XML
SELECT @x = s
FROM OPENROWSET (BULK 'Insert your path here\Customer.xsd',
SINGLE_BLOB) AS TEMP(s)
select
@x
IF
EXISTS(select * from sys.xml_schema_collections where name='Customer')
DROP XML SCHEMA COLLECTION Customer
CREATE
XML SCHEMA COLLECTION Customer AS @x
GO
-- Look at stored metadata
select * from sys.xml_schema_collections
-- VALIDATION
---------------
-- Show that validation fails
ALTER TABLE XMLdoc ALTER COLUMN doc XML(Customer)
go
-- find and remove invalid data
DELETE FROM XMLdoc WHERE doc.exist('/doc')=1
go
-- Try again: Success!
ALTER TABLE XMLdoc ALTER COLUMN doc XML(Customer)
go
-- Constrain to an XML document
ALTER TABLE XMLdoc ALTER COLUMN doc XML(DOCUMENT Customer)
go
-- Insert invalid data
INSERT INTO XMLdoc(doc) VALUES (N'<doc/>')
go