-- XPathFunctions_ID-IDREF_BookAuthor_Complete_Script -- Step 1 -- Switch to tempdb to prevent any "accidents" in your current database. use tempdb go -- Step 2 -- Drop the BookAuthor XML Schema Collection if it already exists, -- and then re-create it. if (exists ( select * from sys.xml_schema_collections where (xml_schema_collections.[schema_id] = schema_id('dbo')) and (xml_schema_collections.name = 'BookAuthor') )) begin drop xml schema collection dbo.BookAuthor end go create xml schema collection dbo.BookAuthor as N' ' go -- Step 3 -- Assign the XML document to a variable and execute the queries. -- The XML document is typed, making it possible to utilize the ID/IDREF XML Schema data types. declare @pubs xml(dbo.BookAuthor) set @pubs = N' Green Marjorie O''Leary Michael Straight Dean Bennet Abraham MacFeather Stearns The Busy Executive''s Database Guide business 19.99 Cooking with Computers: Surreptitious Balance Sheets business 11.95 You Can Combat Computer Stress! business 2.99 Straight Talk About Computers business 19.99 ' -- The XML document: select @pubs as [BookAuthor] -- Authors whose books cost less than $12: -- (Using the ID XPath function.) begin with xmlnamespaces ( 'http://www.w3.org/2001/XMLSchema' as xs ,'http://schemas.milambda.net/pubs' as ba ) select Pubs.Author.query('ba:lastName').value('.', 'varchar(40)') as LastName ,Pubs.Author.query('ba:firstName').value('.', 'varchar(20)') as FirstName from @pubs.nodes (' id(data(/ba:pubs/ba:titleCollection/ba:title[ba:price < 12]/@ba:authorIds)) ') Pubs (Author) end -- Books by authors with the last name of Green: -- (Using XQuery, because the IDREF XPath function has not been implemented in SQL Server.) begin with xmlnamespaces ( 'http://www.w3.org/2001/XMLSchema' as xs ,'http://schemas.milambda.net/pubs' as ba ) select Titles.Title.query('ba:title').value('.', 'varchar(80)') as Title from @pubs.nodes (' for $a in /ba:pubs/ba:authorCollection/ba:author[ba:lastName = "Green"]/@ba:authorId return /ba:pubs/ba:titleCollection/ba:title[contains(string(@ba:authorIds), $a)] ') Titles (Title) end go