Michael Rys

TechEd 2004 DAT327 Demos

The following are the T-SQL part of the DAT 327 “Inside XQuery“ demos. They are organized in 2 files:

1. Init: Initializes the database for 2 .
2. QueryXML: the query and DML statements

Click on the filepaths below to get the sample data.

[Updated Mar 2005: Fixed syntax to align with June 2004 WD. Needed for CTP 13 and beyond]

Init:

use master

-- Recreate DB
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'teched2004') 
   drop database teched2004

CREATE DATABASE teched2004
GO

USE teched2004
GO

declare @x XML

SELECT @x = s
FROM OPENROWSET (
   BULK '...insert your path...\Customer.xsd', SINGLE_BLOB) AS TEMP(s)

IF EXISTS(select * from sys.xml_schema_collections where name='Customer')
   DROP XML SCHEMA COLLECTION Customer

CREATE XML SCHEMA COLLECTION Customer AS @x
GO

-- Load Data
--Create simple table containing XML column
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'XMLdoc')
   DROP TABLE XMLdoc
GO

CREATE TABLE XMLdoc (doc XML(Customer) )
GO

-- Create ContactInfo
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'ContactInfo')
   DROP TABLE ContactInfo
GO

CREATE TABLE ContactInfo (contactID nvarchar(50) primary key, phone nvarchar(20) )
GO

-- Insert XML document
INSERT INTO XMLdoc (doc)
   SELECT *
   FROM OPENROWSET (
      BULK '...insert your path...\Customer1.xml', SINGLE_BLOB) AS TEMP

GO

INSERT INTO XMLdoc (doc)
   SELECT *
   FROM OPENROWSET (
      BULK '...insert your path...\Customer2.xml', SINGLE_BLOB) AS TEMP
GO

-- Insert ContactInfo
INSERT INTO ContactInfo VALUES (N'c1', N'+1 425 555 1234')
INSERT INTO ContactInfo VALUES (N'c2', N'+41 1 555 12 34')
INSERT INTO ContactInfo VALUES (N'c3', N'+49 4321 1234')
INSERT INTO ContactInfo VALUES (N'c4', N'+353 432 1234')
GO

QueryXML:

USE teched2004
GO

-- Data
select * from XMLdoc
GO

select * from ContactInfo
GO

-- XML Schema
SELECT XML_SCHEMA_NAMESPACE(N'dbo', name)
FROM sys.xml_schema_collections
WHERE name != 'sys'
GO

-- Simple Path expression with predicates in exist method
select doc
from XMLdoc
where 1 = doc.exist(
   'declare default element namespace "urn:example/customer";
    /doc/customer[order/@year>2000]
   ')
go

-- value expression
select doc.value(
   'declare default element namespace "urn:example/customer";
    count(/doc/customer/order)
   ', 'int')
from XMLdoc
go

-- query method: More complex query that reshapes result
select doc.query(
   'declare default element namespace "urn:example/customer";
    for $c in /doc/customer
    where $c//saleslead
    return
      <customer id="{$c/@id}">{
         $c/name,
         $c//saleslead
      }</customer>
   ')
from XMLdoc
go

-- Static error: Path expression does contain non-existent path
-- (used to just return empty result in XPath 1.0)
-- Fix: Replace note with notes
select doc.query(
   'declare default element namespace "urn:example/customer";
    /doc/customer/note
   ')
from XMLdoc
go

-- sql:column()/sql:variable() & nodes()
select N.cust.query(
   'declare default element namespace "urn:example/customer";
    <result server="{sql:variable("@@SERVERNAME")}" >{
       <customer id="{./@id}">{
          ./name,
          <phone>{ sql:column("CI.phone") }</phone>,
          .//saleslead
       }</customer>
    }</result>
   '),
   CI.*
from XMLdoc
cross apply doc.nodes(
   'declare default element namespace "urn:example/customer";
   /doc/customer') N(cust)
JOIN ContactInfo CI
ON N.cust.value('@id', 'nvarchar(50)') = CI.contactID
go

select N1.customer.query('.') as customer,
          N1.customer.value(
            'declare default element namespace "urn:example/customer";
             name[1]', 'nvarchar(20)') as CustomerName,
          N2."order".value('@id', 'int') as OrderID,
          N1.customer.value('../@id', 'nvarchar(5)') as DocID
from XMLdoc
cross apply XMLdoc.doc.nodes( 
   'declare default element namespace "urn:example/customer";
   /doc/customer') as N1(customer)
cross apply N1.customer.nodes( 
   'declare default element namespace "urn:example/customer";
    order') as N2("order")
go

-- Validation error: Invalid position according to content model
update XMLdoc set doc.modify(
   'declare namespace c = "urn:example/customer";
    insert <order xmlns="urn:example/customer" id="99" year="1999"/>
    into (/c:doc/c:customer)[1]')
where 1=doc.exist(
   'declare namespace c = "urn:example/customer"; /c:doc/c:customer[@id = "c1"]')
go

-- Correct position
update XMLdoc set doc.modify(
   'declare namespace c = "urn:example/customer";
    insert <order xmlns="urn:example/customer" id="99" year="1999"/>
    before (/c:doc/c:customer/c:notes)[1]')
where 1=doc.exist(
   'declare namespace c = "urn:example/customer"; /c:doc/c:customer[@id = "c1"]')
go

-- Show that insert worked
select doc.query('declare namespace c = "urn:example/customer"; count(//c:order)')
from XMLdoc
where 1=doc.exist(
   'declare namespace c = "urn:example/customer"; /c:doc/c:customer[@id = "c1"]')
go

-- Static type error: New value is not subtype of expected type as given in schema
update XMLdoc set doc.modify(
   'declare namespace c = "urn:example/customer";
    replace value of (/c:doc/c:customer/c:order[@id="99"]/@year)[1]
    with 2004')
where 1=doc.exist(
   'declare namespace c = "urn:example/customer"; /c:doc/c:customer[@id = "c1"]')
go

-- Fixed expression: insert cast
update XMLdoc set doc.modify(
   'declare namespace c = "urn:example/customer";
    replace value of (/c:doc/c:customer/c:order[@id="99"]/@year)[1]
    with xs:int(2004)')
where 1=doc.exist(
   'declare namespace c = "urn:example/customer"; /c:doc/c:customer[@id = "c1"]')
go

-- Retrieve inserted and changed order
select doc.query(
   'declare namespace c = "urn:example/customer";
    /c:doc/c:customer/c:order[@id="99"]')
from XMLdoc
where 1=doc.exist(
   'declare namespace c = "urn:example/customer"; /c:doc/c:customer[@id = "c1"]')
go

-- Remove inserted order
update XMLdoc set doc.modify(
   'declare namespace c = "urn:example/customer";
    delete /c:doc/c:customer/c:order[@id="99"]')
where 1=doc.exist(
   'declare namespace c = "urn:example/customer"; /c:doc/c:customer[@id = "c1"]')
go

posted on Monday, May 31, 2004 9:12 PM by mrys