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

I have seen a few scenarios in which a value is extracted from an XML data type instance in SQL Server 2005. The query performs some checks on the value such as its type (e.g. whether it is numeric) or compares it with a constant, and returns a value based on this check. The value extraction is done using the value() method of XML data type, and the method is re-evaluated in the SELECT list. I want to share a simple tip for better performance. Consider the example

DECLARE @x XML
SET   @x = '<book ISBN="0-7356-1588-2">
                  <title>Writing Secure Code</title>
                  <subject/>
                  <price>39.99</price>
            </book>'

The price of the book can be retrieved using the following query as nvarchar(32) if it is not known for sure that it is actually a numeric value in all <book> instances:

SELECT @x.value ('(/book/price)[1]', 'nvarchar(32)') 

Suppose the query then wants to convert it to a decimal value if it is numeric and return 0 if it is not. The full query then becomes:

SELECT case isnumeric(@x.value ('(/book/price)[1]', 'nvarchar(32)'))
       when 1 then @x.value ('(/book/price)[1]', 'decimal(5,2)')
              else 0
       end

This query computes the value() method twice. A simple trick avoids the recomputation by assigning the result of @x.value ('/book/price', 'nvarchar(32)') to a SQL variable of type nvarchar(32) and converting the variable to decimal:

DECLARE @v nvarchar(32)
SET   @v = (SELECT @x.value ('(/book/price)[1]', 'nvarchar(32)'))
SELECT case isnumeric(@v)
       when 1 then CAST (@v AS decimal(5,2))
              else 0
       end

The rewrite performs significantly faster than the earlier query since it manually optimizes the query to reuse computed values. It also simplifies the query plan quite a bit. There is an underlying assumption on its benefit– the majority of the <price> values are numeric, otherwise the workload does not perform much faster.

Often, there are many more values you want to extract for the XML instance, and breaking up the query into a separate one for each variable assignment is inconvenient. The query may also involve the nodes() method, so that breaking up the query increases the number of nodes() method invocations and requires table-valued variables - the benefits are then lost. A subquery comes to our rescue! Look at the following rewrite in which the value() method is computed in a subquery and aliased as T(Price) and reused in the outer SELECT:

SELECT case isnumeric(Price)
       when 1 then CAST (Price AS decimal(5,2))
              else 0
       end 
FROM  (SELECT @x.value ('(/book/price)[1]', 'nvarchar(32)') Price) T

You get the same benefits as before, ignoring the difference between the variable assignment and the subquery costs, which are much smaller than the cost of the value() method computation. The same optimization can be used in other places as well:

SELECT NULLIF (Subj, '')
FROM  (SELECT @x.value ('(/book/subject)[1]', 'nvarchar(64)') Subj) T
 
The value() method returns the empty string '' for subject, the NULLIF compares the returned value with its second argument '' and returns NULL since these two values are equal. Replacing Subj with the value() method in NULLIF (Subj, ''), as shown below, computes the value() method twice when the return value is other than the empty string. This double computation is much slower:

SELECT NULLIF (@x.value ('(/book/subject)[1]', 'nvarchar(64)'), '')

You get the idea. We can put the two queries together as follows:

SELECT case isnumeric(Price)
            when 1 then CAST (Price AS decimal(5,2))
              else 0
            end,
       NULLIF (Subj, '')
FROM  (SELECT @x.value ('(/book/price)[1]', 'nvarchar(32)') Price,
              @x.value ('(/book/subject)[1]', 'nvarchar(64)') Subj 
       ) T

You might wonder – why doesn’t the query optimizer do this trick? It certainly is an option and hey – if we did it all in one release, what would we do in the future? ;-)

Best wishes and thank you,

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

I made an MSDN webcast on Tue, Dec 7, 2004, on “Introducing XML in SQL Server 2005”. I showed a couple of T-SQL scripts that many of you found useful. This posting contains the demo script for working with untyped XML data. My apologies for the delay in posting!

I’ll follow this up over the next few days with postings of the script for typed (i.e. XML schema bound) XML data and the Q&A for the webcast.

The MSDN whitepaper at http://msdn.microsoft.com/xml/default.aspx?pull=/library/en-us/dnsql90/html/sql2k5xml.asp contains many code snippets with more explanations. You may find it useful as well.

Thanks and enjoy!

Shankar

Program Manager

Microsoft SQL Server

-----------------------------------------------------

-- First, a little formality: 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 untyped

-- (i.e. not XML schema-bound) XML data type on SQL

-- Server 2005.

-----------------------------------------------------

-- NOTE: Before we get started, save the following

-- XML content into a file C:\temp\xmlfile.xml. If

-- you choose a different path or file name, remember

-- to change the file path in the script below.

<book ISBN="0-1896-1899-3">

      <chapter num="1">

            <title>XML Schema</title>

      </chapter>

      <chapter num="3">

            <title>Benefits</title>

      </chapter>

      <chapter num="4">

            <title>Features</title>

      </chapter>

</book>

---------------------------

--- Off we go - Cleanup

---------------------------

USE   master

GO

if    EXISTS (SELECT * FROM sys.databases WHERE name = 'TSqlDemo')

      DROP  DATABASE TSqlDemo

GO

------------------------------------------

--- Initialization - create a new database

------------------------------------------

CREATE  DATABASE TSqlDemo

GO

USE    TSqlDemo

GO

----------------------------------------------

--- Create table containing untyped XML column

----------------------------------------------

CREATE TABLE docs (

      id    INT PRIMARY KEY,   

      xbook XML)

GO

-------------------------------

--- Insertion of data

-------------------------------

--- Insert a literal value for XML

INSERT  INTO docs VALUES (1,

      '<book ISBN="0-7356-1588-2">

            <chapter num="1">

                  <title>Background</title>

            </chapter>

            <chapter num="5">

                  <title>Epilogue</title>

            </chapter>

      </book>')

GO

---------------------------

--- Loading XML from file

---------------------------

--- Add a row to the table

INSERT  INTO docs

SELECT  2, xbook

FROM    (SELECT *

         FROM   OPENROWSET (BULK 'C:\temp\xmlfile.xml',SINGLE_BLOB)

  AS xbook) AS R(xbook)

GO

--- Check content of table – there are two rows

SELECT  * FROM docs

GO

--------------------------

--- QUERYING XML DATA TYPE

--------------------------

--- Find chapter title of chapter number 3 and later

--- Illustrates the use of query() method

--- Notice how a new element <topic> is constructed from the persistent

--- data

SELECT id, xbook.query('

            for $c in

                  /book[@ISBN = "0-7356-1588-2"]//chapter

            where $c/@num >= 3

            return <topic>{data($c/title)}</topic>

       ')  

FROM  docs

GO

--- Eliminate empty XML result using exist() method

SELECT  id, xbook.query('

            for $c in

                  /book[@ISBN = ''0-7356-1588-2'']//chapter

            where $c/@num >= 3

            return <topic>{data($c/title)}</topic>

        ')  

FROM    docs

WHERE   xbook.exist ('/book[@ISBN = ''0-7356-1588-2'']') = 1

GO

--- Extract scalar value (title of Chapter 3)

--- Uses value() method

SELECT  id, xbook.value(

'data((/book//chapter[@num = 3]/title)[1])',

                  'nvarchar(1024)') AS Title

FROM    docs

GO

--- List all chapter titles

--- Notice how the nodes() method explodes an XML instance into a

--- rowset of XML nodes matching an XQuery expression

SELECT  id, nref.query('.') AS Chapter,

            nref.value('.', 'nvarchar(1024)') AS Title

FROM   docs

        CROSS APPLY xbook.nodes ('/book//chapter') AS R(nref)

WHERE   nref.exist('.[@num >= 2]')=1

GO

----------------------------------------

--- Cross domain query:

--- Query across relational and XML data

----------------------------------------

--- Using value from a T-SQL variable

DECLARE     @num int

SET         @num = 1

SELECT      id,

            @num AS ChapterNum,

            xbook.query(

                  'for $s in /book//chapter[@num=sql:variable("@num")]

                   return <topic>{data($s/title)}</topic>')

FROM        docs

GO

------------------------------

--- XML DATA TYPE MODIFICATION

------------------------------

--- Change title of chapter 3 to "Algorithms"

UPDATE  docs

SET     xbook.modify (

   'replace value of

    (/book[@ISBN = "0-7356-1588-2"]//chapter[@num=1]/title/text())[1]

    with "Algorithms"')

GO

--- Verify update

SELECT  id, xbook

FROM    docs

GO

--- Insert new subtree (chapter)

UPDATE  docs

SET     xbook.modify(

      'insert <chapter num="2">

                  <title>Introduction</title>

              </chapter>      

      after (/book//chapter[@num=1])[1]')

GO

--- Verify insertion

SELECT  xbook

FROM    docs

GO

--- Delete chapter #1

UPDATE  docs

SET     xbook.modify(

            'delete /book//chapter[@num=1]')

GO

--- Verify deletion

SELECT  xbook

FROM    docs

GO

This posting is provided "AS IS" with no warranties, and confers no rights.