/*============================================================================
  File:     AccessingXMLData.sql

  Summary:  This script quickly shows a few queries that return XML so that
			the new XML features in SSMS are obvious!

  Date:     April 2005

  Tested on SQL Server Version: 9.00.1116.08 (IDW14 - April CTP)
------------------------------------------------------------------------------
  Copyright (C) 2005 Kimberly L. Tripp, SYSolutions, Inc.
  All rights reserved.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  This script is intended only as a supplement to demos and lectures
  given by Kimberly L. Tripp.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

USE AdventureWorks 
GO 
-- Select the Mountain-100 record 
-- Note the Catalog Description column (click to see the full document) 

SELECT pm.* 
FROM Production.ProductModel AS pm
WHERE pm.ProductModelID = 19 
GO 

-- Extract the maintenance details from the catalog description column 
-- (click to see the fragment returned) 

-- Take a look at the schema of the result set (the second one: 
-- "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain" 

-- This allows companies/internal groups/anyone to share common definitions.
-- Can also aid in the sharing of structured data. A catalog is a classic means of interaction, 
-- having it as a structured document allows it to be kept up to date (and delivered to the app) 
-- without repeated shredding, the schema means that "<Maintenance><NoOfYears>" is described 
-- much as you'd describe an api.... 

-- In the query body, the FLWOR (FOR LET RETURN) statement returns the <Features> element 
-- children containing the word "maintenance". 
-- The string() function retrieves the string value in the element. 
-- The contains() function is then used to search the text for the specific word.

SELECT CatalogDescription.query(' 
     declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
     declare namespace p2="http://www.w3.org/1999/xhtml"; 
     for $f in /p1:ProductDescription/p1:Features/* 
     where contains(string($f), "maintenance") 
     return $f') as Result 
FROM Production.ProductModel AS pm
WHERE pm.ProductModelID = 19 
