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.