First of all, I'd like to thank all those who took the time to attend my presentation today (Nov 8, 2005). I hope you found it useful. The launch of SQL Server 2005, Visual Studio 2005 and BizTalk Server 2006 is a matter of great pride and honor for us all.
The website with links to the XML schemas published by SQL Server is http://schemas.microsoft.com/sqlserver. Look at the one called SqlTypes for the XML schema types corresponding to the built-in SQL types. This was one of the questions during the presentation.
The Transact-SQL script I presented is below. I hope you get a chance to run it! Play with it to discover the power of the native XML support. For more information about the XML support, check out the REFERENCES in the left pane.
Enjoy! Let me know if you have questions or would like me to address any issues in the blog.
Shankar
Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm
-----------------------------------------------------------------------
-- This T-SQL script illustrates the use of typed (i.e. XML schema-bound) XML data type on SQL Server 2005.
----------------------------------------------------
--- Cleanup
----------------------------------------------------
USE master
GO
--- Drop the existing database
if EXISTS (SELECT * FROM sys.databases WHERE name = 'MSDN')
DROP DATABASE MSDN
GO
---------------------------
--- Initialization
---------------------------
--- Create a new database called MSDN
CREATE DATABASE MSDN;
GO
USE MSDN;
GO
-------------------------------------------------
---- Drop the meeting notes XML schema collection
IF EXISTS (select name from sys.xml_schema_collections where name='meetingNotesCollection')
DROP XML SCHEMA COLLECTION meetingNotesCollection;
go
CREATE XML SCHEMA COLLECTION meetingNotesCollection
AS
'<xsd:schema
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns="http://www.microsoft.com/meetingNotes"
targetNamespace="http://www.microsoft.com/meetingNotes"
elementFormDefault="qualified">
<xsd:element name="meetingNotes" type="meetingNotesType"/>
<xsd:complexType name="meetingNotesType" mixed="true">
<xsd:sequence>
<xsd:element name="title" type="xsd:string"/>
<xsd:element name="meetingDate" type="xsd:date"/>
<xsd:element name="participants"
type="participantType" />
<xsd:element name="notes" type="noteType" />
<xsd:element name="actionItem" type="actionItemType"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="participantType" mixed="true">
<xsd:sequence>
<xsd:element name="participant" type="xsd:string"
minOccurs="1" maxOccurs="unbounded" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="noteType" mixed="true">
<xsd:sequence>
<xsd:element name="note" type="xsd:string"
minOccurs="0" maxOccurs="unbounded"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="actionItemType" mixed="true">
<xsd:sequence>
<xsd:element name="description" type="xsd:string"
minOccurs="0" maxOccurs="unbounded"/>
</xsd:sequence>
</xsd:complexType>
</xsd:schema>';
GO
--- Create a table to store MeetingNotes
CREATE TABLE meetingData (
id int PRIMARY KEY,
meetingNotes XML (meetingNotesCollection))
GO
-------------------------------
--- Insertion of data
-------------------------------
--- Insert a literal value for XML
INSERT INTO meetingData VALUES (1,
'<p:meetingNotes
xmlns:p="http://www.microsoft.com/meetingNotes">
<p:title>Discussion of XML presentation</p:title>
<p:meetingDate>2005-10-03Z</p:meetingDate>
<p:participants>
<p:participant>Shankar</p:participant>
<p:participant>Brandon</p:participant>
</p:participants>
<p:notes>
<p:note>Equal amount of slides and demos</p:note>
<p:note>VS-focused slides</p:note>
<p:note>SQL/CLR project for XML processing</p:note>
</p:notes>
<p:actionItem>
<p:description>Work on slides - Shankar</p:description>
<p:description>Work on demos - Shankar</p:description>
</p:actionItem>
</p:meetingNotes>');
GO
--- Insert more data into the table
INSERT INTO meetingData VALUES (2,
'<meetingNotes xmlns="http://www.microsoft.com/meetingNotes"><title>Discussion of CDP and Office11</title><meetingDate>2005-09-29Z</meetingDate><participants><participant>Anilnori</participant><participant>Soumitras</participant> <participant>shankarp</participant></participants><notes><note>Format of Word document and storage</note></notes><actionItem><description>Need to put all ideas into a document</description></actionItem></meetingNotes>');
INSERT INTO meetingData VALUES (3,
'<meetingNotes xmlns="http://www.microsoft.com/meetingNotes"><title>Discussion of TechReady presentation</title><meetingDate>2005-08-02Z</meetingDate><participants><participant>shankarp</participant><participant>heraino</participant></participants><notes> <note>Outline the features</note><note>Show architecture diagram</note></notes><actionItem><description>Update the presentation - shankarp</description></actionItem></meetingNotes>');
GO
--- Table contents
SELECT * FROM meetingData;
GO
--- Index the XML column in the table
CREATE PRIMARY XML INDEX idx_xml ON meetingData(meetingNotes);
GO
---- Create PATH secondary XML index
CREATE XML INDEX idx_xml_PATH
ON meetingData(meetingNotes)
USING XML INDEX idx_xml
FOR PATH;
GO
--------------------------
--- QUERYING XML DATA TYPE
--------------------------
--- Find the action items
SELECT meetingNotes.query ('
declare default element namespace "http://www.microsoft.com/meetingNotes";
/meetingNotes/actionItem/description')
FROM meetingData;
GO
--- Find the action items, one per row
--- With on-the-fly element construction
SELECT dref.query ('<workitem>{data(.)}</workitem>')
FROM meetingData CROSS APPLY
meetingNotes.nodes ('
declare default element namespace "http://www.microsoft.com/meetingNotes";
/meetingNotes/actionItem/description') R(dref);
GO
--- Find the action items, one per row
--- With no element tags
SELECT dref.value ('.', 'nvarchar(256)')
FROM meetingData CROSS APPLY
meetingNotes.nodes ('
declare default element namespace "http://www.microsoft.com/meetingNotes";
/meetingNotes/actionItem/description') R(dref);
GO
--- Find meetings with at least 3 participants
SELECT *
FROM meetingData
WHERE meetingNotes.exist ('
declare default element namespace "http://www.microsoft.com/meetingNotes";
/meetingNotes[count (//participant) > 2]') = 1;
GO
-------------------------------------------
--- Promote meeting #participants property
-------------------------------------------
--- Create user-defined function for #participants
CREATE FUNCTION udf_get_pCount (
@xData xml(dbo.meetingNotesCollection))
RETURNS int
WITH SCHEMABINDING
BEGIN
RETURN (
SELECT @xData.value ('
declare default element namespace "http://www.microsoft.com/meetingNotes";
count(//participant)', 'int'))
END
GO
----- Add a computed column to the table for meetingDate
ALTER TABLE meetingData
ADD PCount AS dbo.udf_get_pCount(meetingNotes);
GO
----- Check content of table
SELECT * FROM meetingData;
GO
--- Index the computed column
CREATE NONCLUSTERED INDEX idx_pCount
ON meetingData(pCount);
GO
-- Simpler alternative to the previous XQuery:
SELECT *
FROM meetingData
WHERE PCount > 2;
GO
------------------------------
--- XML DATA TYPE MODIFICATION
------------------------------
--- Add a new participant to a meeting
UPDATE meetingData
SET meetingNotes.modify ('
declare default element namespace "http://www.microsoft.com/meetingNotes";
insert <participant>Jinghao</participant>
into (/meetingNotes/participants)[1]')
WHERE id = 1;
GO
--- Verify update
SELECT * FROM meetingData;
GO
--- Modify the title of a meeting
UPDATE meetingData
SET meetingNotes.modify ('
declare default element namespace "http://www.microsoft.com/meetingNotes";
replace value of (/meetingNotes/title)[1]
with "Discussion of XML presentation for MSDN"')
WHERE id = 1;
GO
--- Verify update
SELECT * FROM meetingData
GO
