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