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.
