TechEd 2004 DAT319 Demos
The following are the T-SQL statements that I demoed during my XML in SQL Server 2005 talk. They are organized in 4 files:
- Init: Initializes the database for 2 and 3.
- LoadXML: Creates the table and loads it
- TypeXML: Creates XML Schema Collection and shows how to use it
- FORXML: Shows the new FOR XML feature on the Northwind database (needs to be explicitly downloaded and installed)
Click on the filepaths below to get the sample data.
Init:
-- Initializiation
-- Execute every statement in the sample in order
-- (c) 2004 Microsoft Corp.
use
master
drop database teched2004
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'teched2004')
CREATE DATABASE teched2004
GO
LoadXML:
-- Loading
-- Execute every statement in the sample in order
-- Set filenames in loading steps to correct locations
-- (c) 2004 Microsoft Corp.
USE
teched2004
GO
--Create simple table containing XML column
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'XMLdoc')
DROP TABLE XMLdoc
GO
CREATE
TABLE XMLdoc (id INT identity primary key, doc XML )
GO
-- Insert XML document
INSERT INTO XMLdoc (doc)
SELECT *
FROM OPENROWSET (
BULK 'C:\...YourPathHere...\Customer1.xml',
SINGLE_BLOB) AS TEMP
GO
-- Insert XML fragment
INSERT INTO XMLdoc (doc)
SELECT *
FROM OPENROWSET (
BULK 'C:\...YourPathHere...\Customer2.xml',
SINGLE_BLOB) AS TEMP
GO
-- Insert a string
INSERT INTO XMLdoc(doc) VALUES (N'<doc/>')
go
-- Take a look at the XML
SELECT * FROM XMLdoc
GO
TypeXML:
-- Typing
-- Execute every statement in the sample in order
-- Set filename in loading to correct location
-- (c) 2004 Microsoft Corp.
USE
teched2004
GO
-- Load schema from file
declare @x XML
SELECT
@x = s
FROM OPENROWSET (
BULK 'C:\...YourPathHere...\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
select
* from sys.xml_namespaces -- in Beta2: sys.xml_schema_namespaces
select
* from sys.xml_components WHERE xml_collection_id in
(select xml_collection_id from sys.xml_schema_collections where name='Customer')
-- Retrieve Schema as XML using built-in intrinsic
SELECT
XML_SCHEMA_NAMESPACE(N'dbo', name)
FROM sys.xml_schema_collections
WHERE name != 'sys'
go
-- VALIDATION
-------------
-- Show that validation fails when invalid data is present
ALTER TABLE XMLdoc ALTER COLUMN doc XML(Customer)
go
-- find and remove invalid data
SELECT * FROM XMLdoc where doc.exist('//doc')=1
go
DELETE
FROM XMLdoc WHERE id = 3
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
-- Schema Management
--------------------
-- Changing schema: Extending
ALTER XML SCHEMA COLLECTION Customer ADD
N'<xsd:schema xmlns:e="urn:example/employee" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
targetNamespace="urn:example/employee" elementFormDefault="qualified">
<xsd:element name="employee" type="e:empT" />
<xsd:complexType name="empT">
<xsd:sequence>
<xsd:element name="name" type="xsd:string" />
<xsd:element name="phone" type="xsd:string" />
<xsd:element name="salary" type="xsd:decimal" />
</xsd:sequence>
<xsd:attribute name="id" type="xsd:string" />
</xsd:complexType>
</xsd:schema>'
-- Changing schema: Constraining -> fails
ALTER XML SCHEMA COLLECTION Customer ADD
N'<xsd:schema xmlns:e="urn:example/employee" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
targetNamespace="urn:example/employee" elementFormDefault="qualified">
<xsd:complexType name="empT">
<xsd:sequence>
<xsd:element name="name" type="xsd:string" />
<xsd:element name="phone" type="xsd:string" />
<xsd:element name="salary" type="xsd:decimal" />
</xsd:sequence>
<xsd:attribute name="id" type="xsd:string" />
</xsd:complexType>
</xsd:schema>'
-- Dropping schema: dependencies are managed -> fails
DROP XML SCHEMA COLLECTION Customer
-- Untype and try again
ALTER TABLE XMLdoc ALTER COLUMN doc XML
DROP XML SCHEMA COLLECTION Customer
go
FORXML:
-- FOR XML
-- Execute every statement in the sample in order
-- (c) 2004 Microsoft Corp.
USE
Northwind
GO
-- Raw mode
-----------
-- SQL Server 2000:
select * from Customers for xml raw
go
-- new: rename row
select * from Customers for xml raw('Customers')
go
-- new: element-centric
select
top 1 * from Customers
where Region IS NULL
for xm