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.
