Hello!

 

This posting contains T-SQL script for working with typed (i.e. XML schema bound) XML. I used it during the MSDN webcast on Tue, Dec 7, 2004, on “Introducing XML in SQL Server 2005”. Yesterday’s posting (http://weblogs.asp.net/spal/archive/2004/12/20/327635.aspx) contained the script for untyped XML. Thanks for your patience!

 

I have added some more annotation to the script to make it more readable – so please don’t skip the comments. I have the habit of repeating key phrases – this makes the text a little verbose but avoids ambiguity.

 

Here is a list of the MSDN whitepapers on XML support in SQL Server 2005 that you might find useful:

Of course, SQL Server 2005 books-online is an invaluable resource.

 

I hope these scripts help you get started with the new XML features.

 

Thanks and enjoy!

 

Shankar

Program Manager

Microsoft SQL Server

 

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

-- First, the 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 typed (i.e. XML schema-bound) XML data type on SQL Server 2005.

 

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

-- NOTE: Before we get started, save the following data literally into a file C:\temp\TypedPropTable.txt.

-- If you choose a different path or file name, remember to change the file path in the script below. The

-- script uses this file to illustrate bcp’ing data into an XML column

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

12    <p:book xmlns:p="http://www.microsoft.com/book" subject="drama" releasedate="2002" ISBN="0-1234-1588-2"><title>Battle of Troy</title><author><first-name>Matthew</first-name><last-name>Robson</last-name></author><price>19.99</price></p:book>      0

24    <q:dvd xmlns:q="http://www.microsoft.com/DVD" subject="drama" releasedate="1977"><title>The Godfather</title><price>12.99</price></q:dvd>   0

 

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

--- We are good to go now.

--- We’ll start with a clean database

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

USE   master

GO

 

if    EXISTS (SELECT * FROM sys.databases

              WHERE name = 'TypedProperty')

      DROP DATABASE TypedProperty

GO

 

CREATE  DATABASE TypedProperty

GO

 

USE     TypedProperty

GO

 

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

--- Create an XML schema collection containing BOOK schema

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

CREATE XML SCHEMA COLLECTION myCollection

AS

'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"

  xmlns="http://www.microsoft.com/book"

  targetNamespace="http://www.microsoft.com/book">

  <xsd:element name="book" type="bookType" />

  <xsd:complexType name="bookType">

      <xsd:sequence>

        <xsd:element name="title" type="xsd:string" />

        <xsd:element name="author" type="authorName" maxOccurs="unbounded"/>

        <xsd:element name="price" type="xsd:decimal" />

      </xsd:sequence>

      <xsd:attribute name="subject" type="xsd:string" />

      <xsd:attribute name="releasedate" type="xsd:integer" />

      <xsd:attribute name="ISBN" type="xsd:string" />

  </xsd:complexType>

  <xsd:complexType name="authorName">

      <xsd:sequence>

        <xsd:element name="first-name" type="xsd:string" />

        <xsd:element name="last-name" type="xsd:string" />

      </xsd:sequence>

  </xsd:complexType>

</xsd:schema>'

GO

 

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

--- Create a table containing a typed XML column

--- NOTE: the typed XML column as declared below

--- allows XML “content” to be stored therein (i.e.

--- multiple top-level elements. This is the default.

--- You can restrict it to one top-level element by declaring

--- the XML column as XML (DOCUMENT dbo.myCollection)

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

CREATE TABLE PropTable (

      assetID  INT PRIMARY KEY,

      xProp    XML (dbo.myCollection))

GO

 

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

--- Alter the XML schema collection by adding the XML

--- schema for DVD. Altering an XML schema collection is

--- allowed even after the XML column has been populated

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

ALTER XML SCHEMA COLLECTION myCollection

ADD

'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"

  xmlns="http://www.microsoft.com/DVD"

  targetNamespace="http://www.microsoft.com/DVD">

  <xsd:element name="dvd" type="dvdType" />

  <xsd:complexType name="dvdType">

      <xsd:sequence>

        <xsd:element name="title" type="xsd:string" />

        <xsd:element name="price" type="xsd:decimal" />

      </xsd:sequence>

      <xsd:attribute name="subject" type="xsd:string" />

      <xsd:attribute name="releasedate" type="xsd:integer" />

  </xsd:complexType>

</xsd:schema>'

GO

 

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

--- Insertion of data into the typed XML column

--- The XML schema namespace specified in the XML instance

--- identifies the XML schema in the XML schema collection

--- to validate against. If the data does not validate

--- against the schema, it will be rejected with appropriate

--- error message

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

--- Insert a literal value for XML

INSERT INTO PropTable VALUES (1,

'<p:book xmlns:p="http://www.microsoft.com/book"

      subject="security" releasedate="2002" ISBN="0-7356-1588-2">

      <title>Writing Secure Code</title>

      <author>

            <first-name>Michael</first-name>

            <last-name>Howard</last-name>

      </author>

      <author>

            <first-name>David</first-name>

            <last-name>LeBlanc</last-name>

      </author>

      <price>39.99</price>

</p:book>')

GO

 

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

--- Promote price property into a separate column.

--- The <price> value will also be retained in XML instances.

--- The query processor will maintain the consistency between the two columns.

--- Property promotion is useful when a significant percentage of the queries are based on price.

---

--- For promoting multi-valued properties, you need to create a “property table” and maintain it

--- using triggers. This is discussed in the XML Best Practices whitepaper on MSDN (link above)

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

--- Create user-defined function for Price. This computes

--- the price of the asset irrespective of its asset class.

--- You can promote properties per asset type by specifying

--- the appropriate XML schema namespace in the query below

CREATE  FUNCTION udf_get_price (@xData xml(dbo.myCollection))

RETURNS real

WITH SCHEMABINDING

BEGIN

   RETURN (SELECT @xData.value ('(//price)[1]', 'real'))

END

GO

 

----- Add a computed column to the table for Price

----- Computed columns are read-only

ALTER  TABLE PropTable

       ADD Price AS dbo.udf_get_price(xProp) PERSISTED

GO

 

----- Check the contents of the table: one row with the price

----- value promoted into Price column

SELECT  * FROM PropTable

GO

 

--- Index the computed column

CREATE  NONCLUSTERED INDEX idx_Price ON PropTable (Price)

GO

 

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

--- Create Primary XML index – this can be created before the

--- non-clustered index on Price

--- XML index is a new type of index because it retains the

--- structure of the XML instance (e.g. document order,

--- parent-child hierarchy) in addition to values within

--- the XML instance. It is internally implemented as a B+ tree

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

CREATE PRIMARY XML INDEX idx_xProp ON PropTable(xProp)

GO

 

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

--- Create 'PATH' Secondary XML index

--- This helps with path-based queries, which are predominat in practice

--- Secondary XML indexes can be created after the primary XML index on an XML column

--- Other types of secondary XML indexes – VALUE and PROPERTY

--- See the MSDN whitepapers for more description and usage guidance of these indexes

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

CREATE  XML INDEX idx_xProp_PATH ON PropTable(xProp)

USING   XML INDEX idx_xProp

FOR     PATH

GO

 

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

--- Loading XML from file using bcp

--- This uses the data file that was created upfront

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

--- Load 2 more rows into the table

EXEC  master.dbo.xp_cmdshell

'bcp TypedProperty.dbo.PropTable in C:\temp\TypedPropTable.txt -c -q -T'

GO

 

--- Check contents of table: 3 rows with the price value promoted

SELECT  * FROM PropTable

GO

 

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

--- QUERYING XML DATA TYPE

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

--- Find assets that are priced between $9.99 and $29.99

SELECT  assetID, xProp, Price

FROM    PropTable

WHERE   xProp.exist ('//price[. > 9.99 and . < 29.99]') = 1

GO

 

-- Wait a minute! The price value has been promoted, so let’s use that!!

-- The query plan is simpler using the promoted column since the value is

-- pre-computed. Also, the non-clustered index on the promoted column helps performance

SELECT  assetID, xProp, Price

FROM    PropTable

WHERE   Price > 9.99 AND Price < 29.99

GO

 

--- What if you want to restrict the search to a specific asset type?

--- The Price column is oblivious of the asset type

--- Let’s try: Find books (not DVDs) priced between $9.99 and $29.9

--- The query needs to specify the XML schema namespace for "book" and

--- query the XML column

SELECT  assetID, xProp, Price

FROM    PropTable

WHERE   xProp.exist ('

            declare namespace p= "http://www.microsoft.com/book"

            /p:book/price[. > 9.99 and . < 29.99]') = 1

GO

 

--- Let’s try grouping based on values within the XML column, such as

--- the number of assets per release year

--- You have to retrieve the release year in a sub-query for grouping

SELECT  Year, count(AssetID) Asset_Count

FROM    (

         SELECT  xProp.value ('(//@releasedate)[1]', 'int'), assetID

         FROM    PropTable

        ) R(Year, AssetID)

GROUP BY Year

GO

 

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

--- XML DATA TYPE MODIFICATION

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

--- Lower an asset's price by 10% - it's holiday season and some discount is appreciated!

UPDATE  PropTable

SET     xProp.modify ('

                replace value of (//price)[1]

                with 0.9*(//price)[1]

    ')

 

--- Verify update

--- Notice that the Price column is kept in sync with the

--- value in the XML column – you didn’t need to maintain it!

SELECT  *

FROM    PropTable

GO

 

--- Delete an author of a book given the book's ISBN

--- and the author's name.

--- This doesn’t happen often in practice but is good enough

--- to illustrate the idea

UPDATE  PropTable

SET     xProp.modify(

'declare namespace p= "http://www.microsoft.com/book"

 delete /p:book[@ISBN = "0-7356-1588-2"]/author[first-name = "David"]')

GO

 

--- Add an author (new subtree) to a book given its ISBN

--- Notice that the point of insertion is specified using "before"

--- Besides “before”, other possibilites are “after” and “into”

UPDATE  PropTable

SET     xProp.modify(

            'declare namespace p= "http://www.microsoft.com/book"

             insert  <author>

                        <first-name>David</first-name>

                        <last-name>LeBlanc</last-name>

                     </author>

            before   (/p:book[@ISBN = "0-7356-1588-2"]/author)[1]')

WHERE   xProp.exist ('

            declare namespace p= "http://www.microsoft.com/book"

            /p:book[@ISBN = "0-7356-1588-2"]') = 1

GO

 

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