/************************************
Chapter 5 Scripts
For MCITP SQL Server 2005 Database Developer All-in-One Exam Guide
By Darril Gibson

The scripts available here are the same ones used in each of the chapters. Ideally, you would type in the script and learn from any errors you might make. Occasionally, however, you may want a copy of the script to paste into the query window or into a batch file. If so, feel free to use these script files. 

I strongly encourage you not to rely on these pre-typed scripts, but instead to get used to typing the T-SQL yourself. The more you do it (even if you’re just typing what you see in the book), the more comfortable you’ll get with T-SQL.

Some scripts in the book are intended to be run from the command line. They've been included where appropriate so that you can copy and paste them into Notepad and save them as a batch file (.bat). You can then run the batch file from the command prompt.

Be aware that some of the scripts fail on purpose. For example, the book may be showing how a script works, and then after a constraint is added, the book may show how the same script fails.  The failure is not an error. In other words, just running the scripts without the benefit of the book won't be much help.

*************************************/

CREATE DATABASE XMLTest

/************************************/

USE XMLTest;
GO
CREATE TABLE Project
(
	ProjectID int IDENTITY(1,1) NOT NULL,
	ProjectData xml NOT NULL
   	CONSTRAINT [PK_Project_ProjectID] PRIMARY KEY CLUSTERED
		(ProjectID)
 )

/************************************/

--Insert untyped data
DECLARE @xmlInsert XML
SET @xmlInsert =
'<project>
  <projectname>Employee Database</projectname>
  <startdate>1994-06-10abc</startdate>
  <enddate>2008-05-05def</enddate>
  <manager>Darril Gibson</manager>
  <scope>Create database for employees</scope>
</project>'
INSERT INTO Project
VALUES (@xmlInsert)

/************************************/

--Insert untyped data
DECLARE @xmlInsert XML
SET @xmlInsert =
'<project>
  <projectname>Employee Database</projectname>
  <startdate>1994-06-10abc</startdate>
  <enddate>2008-05-05def</enddate>
  <manager>Darril Gibson<manager>
  <scope>Create database for employees</scope>
</project>'
INSERT INTO Project
VALUES (@xmlInsert)

/************************************/

DELETE FROM Project

/************************************/

USE XMLTest;
GO
CREATE XML SCHEMA COLLECTION dbo.xmlProjectSchemaCollection AS
'<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="project">
    <xs:complexType>
      <xs:sequence>
	   <xs:element name="projectnumber" type="xs:int"/>
	   <xs:element name="projectname" type="xs:string"/>
	   <xs:element name="startdate" type="xs:date"/>
	   <xs:element name="enddate" type="xs:date"/>
	   <xs:element name="manager" type="xs:string"/>
	   <xs:element name="scope" type="xs:string"/>
      </xs:sequence>
    </xs:complexType>
</xs:element>
</xs:schema> '

/************************************/

SELECT xml_schema_namespace('dbo','xmlProjectSchemaCollection')

/************************************/

USE XMLTest;
GO
ALTER TABLE Project
ALTER COLUMN ProjectData XML (dbo.xmlProjectSchemaCollection)

/************************************/

USE XMLTest;
GO
DECLARE @xmlInsert XML
SET @xmlInsert =
'<project>
  <projectnumber>101</projectnumber>
  <projectname>Employee Database</projectname>
  <startdate>2007-06-30Z</startdate>
  <enddate>2008-05-29Z</enddate>
  <manager>Darril Gibson</manager>
  <scope>Create database for employees</scope>
</project>'
INSERT INTO Project
VALUES (@xmlInsert)

/************************************/

USE XMLTest;
GO
DECLARE @xmlInsert XML
SET @xmlInsert =
'<project>
  <projectnumber>101</projectnumber>
  <projectname>Employee Database</projectname>
  <startdate>2007-06-30abc</startdate>
  <enddate>2008-05-29Z</enddate>
  <manager>Darril Gibson</manager>
  <scope>Create database for employees</scope>
</project>'
INSERT INTO Project
VALUES (@xmlInsert)

/************************************/

SELECT * FROM Project

/************************************/

USE XMLTest;
GO
SELECT ProjectData.query('project/projectname')
FROM Project
Where ProjectID=2

/************************************/

USE AdventureWorks;
GO
SELECT ContactID, FirstName, LastName 
FROM Person.Contact
WHERE ContactID=6 or ContactID=8;

/************************************/

SELECT ContactID, FirstName, LastName
FROM Person.Contact
WHERE ContactID=6 or ContactID=8
FOR XML RAW;

/************************************/

SELECT ContactID, FirstName, LastName
FROM Person.Contact
WHERE ContactID=6 or ContactID=8
FOR XML RAW, ELEMENTS;
<row>
  <ContactID>6</ContactID>
  <FirstName>Frances</FirstName>
  <LastName>Adams</LastName>
</row>
<row>
  <ContactID>8</ContactID>
  <FirstName>Carla</FirstName>
  <LastName>Adams</LastName>
</row>

/************************************/

SELECT Person.Contact.ContactID, Person.Contact.FirstName, 
       Person.Contact.LastName, HumanResources.Employee.EmployeeID
FROM Person.Contact INNER JOIN
HumanResources.Employee
ON Person.Contact.ContactID = HumanResources.Employee.ContactID
WHERE Person.Contact.ContactID=1209 or Person.Contact.ContactID=1030
FOR XML AUTO;

/************************************/

SELECT 1    as Tag,
       NULL as Parent,
       EmployeeID as [Employee!1!EmpID],
       NULL       as [Name!2!FName],
       NULL       as [Name!2!LName]
FROM   HumanResources.Employee E, Person.Contact C
WHERE  E.ContactID = C.ContactID
UNION ALL
SELECT 2 as Tag,
      1 as Parent,
       EmployeeID,
       FirstName, 
       LastName 
FROM   HumanResources.Employee E, Person.Contact C
WHERE  E.ContactID = C.ContactID
ORDER BY [Employee!1!EmpID],[Name!2!FName]
FOR XML EXPLICIT

/************************************/

USE AdventureWorks;
GO
SELECT ProductModelID, Name, Instructions
FROM Production.ProductModel
WHERE ProductModelID=47

/************************************/

SELECT ProductModelID,Name,
       Instructions.query('declare namespace 
       ModelID="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
      /ModelID:root/ModelID:Location ') 
FROM Production.ProductModel
WHERE ProductModelID=47
FOR XML PATH

/************************************/

USE AdventureWorks;
GO
SELECT ProductID, Name, Color
FROM Production.Product
FOR XML RAW, ELEMENTS;

/************************************/

USE AdventureWorks;
GO
SELECT ProductID, Name, Color
FROM Production.Product
FOR XML RAW, ELEMENTS XSINIL

/************************************/

DECLARE @myXML xml
SET @myXML =
'<Root>
<Project ProjectNumber="101" ProjectName = "Employee Database"
  StartDate ="2008-06-30Z" EndDate = "2008-07-29Z"
  Manager = "Darril Gibson">
</Project>
<Project ProjectNumber="102" ProjectName = "Build Parts Database"
  StartDate ="2008-08-30Z" EndDate = "2008-09-29Z"
  Manager = "Justin Case">
</Project>
</Root>'
DECLARE @ProjName varchar(50)
SET @ProjName = @myXML.value('(/Root/Project/@ProjectName)[2]', 'nvarchar(50)' )
SELECT @ProjName AS [Project Name]


/************************************/

USE XMLTest;
GO
SELECT ProjectData.query('project/projectname')
FROM Project
Where ProjectID=2

/************************************/

DECLARE @myXML xml
SET @myXML =
'<Root>
  <Project ProjectNumber="101">
    <ProjectName>Employee Database</ProjectName>
    <StartDate>2008-06-30Z</StartDate> 
    <EndDate>2008-07-29Z</EndDate>
    <Manager>Darril Gibson</Manager>
  </Project>
  <Project ProjectNumber="102">
    <ProjectName>Build Parts Database</ProjectName>
    <StartDate>2008-08-30Z</StartDate> 
    <EndDate>2008-09-29Z</EndDate>
    <Manager>Justin Case</Manager>
  </Project>
</Root>'
SELECT 
   MyTable.cols.value('@ProjectNumber', 'int') AS ProjectNumber,
   MyTable.cols.query('StartDate') AS StartDate,
   MyTable.cols.query('EndDate') AS EndDate
FROM @myXML.nodes('/Root/Project') MyTable (cols)

/************************************/

DECLARE @myXML xml
DECLARE @ExistResult bit
--The following line creates the XML 
SET @myXML = '<root ProjDate = "2008-01-01Z"/>'
--The Exist method checks the XML path against a given value 
--In this case we're comparing the XML path against a given date
SET @ExistResult = 
            @myXML.exist('/root[(@ProjDate cast as xs:date?) eq xs:date("2008-01-01Z")]')
--It populates the @ExistResult with a 1 if the node exists, or a 0 if not
SELECT @ExistResult

/************************************/

USE AdventureWorks;
GO
SELECT Demographics
FROM Sales.Store
WHERE CustomerID = 10

/************************************/

SELECT Name, Demographics.value
	('declare namespace ss=
	"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
	(/ss:StoreSurvey/ss:AnnualRevenue)[1]','money') AS AnnualRevenue 
FROM Sales.Store
WHERE CustomerID = 10;

/************************************/

SELECT CustomerID, Name
FROM Sales.Store
WHERE Demographics.exist('declare namespace 
	ss="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
 	/ss:StoreSurvey/ss:AnnualSales[. > 1000000]') = 1
ORDER BY Name;

/************************************/

--First we create the XML document in memory as a string
DECLARE @XmlDoc nvarchar(1000)
SET @XmlDoc = N'<ROOT>
<Customer CustomerID="HPOTTE" ContactName="Harry Potter">
   <Order OrderID="10248" CustomerID="HPOTTE" EmployeeID="17" 
          OrderDate="2008-09-01T00:00:00">
      <OrderDetail ProductID="211" Quantity="1"/>
      <OrderDetail ProductID="342" Quantity="3"/>
   </Order>
</Customer>
<Customer CustomerID="RWEASE" ContactName="Ron Weasely">
   <Order OrderID="10283" CustomerID="RWEASE" EmployeeID="19" 
          OrderDate="2008-03-30T00:00:00">
      <OrderDetail ProductID="172" Quantity="5"/>
   </Order>
</Customer>
</ROOT>'

-- Next we parse the document with sp_xml_preparedocument 
DECLARE @DocHandle int
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDoc
-- Now we shred it with OPENXML
SELECT *
FROM OPENXML (@DocHandle, '/ROOT/Customer/Order')
      WITH (CustomerID  varchar(10),
            OrderID varchar(20),
	     OrderDate datetime)
--Last we clean up
EXEC sp_xml_removedocument @DocHandle

/************************************/

USE AdventureWorks;
GO
WITH XMLNAMESPACES 
('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")
SELECT CatalogDescription.query('
  /PD:ProductDescription/PD:Summary
') as Result
FROM Production.ProductModel
WHERE CatalogDescription.exist ('/PD:ProductDescription/PD:Features') = 1

/************************************/

USE AdventureWorks;
GO
WITH XMLNAMESPACES 
('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")
SELECT CatalogDescription.query('
  /PD:ProductDescription/PD:Summary
') as Result
FROM Production.ProductModel
WHERE CatalogDescription.exist ('/PD:ProductDescription/@ProductModelID[.="19"]') = 1

/************************************/

USE AdventureWorks;
GO
SELECT AdditionalContactInfo 
FROM PERSON.CONTACT 
WHERE ContactID = 2 

/************************************/

USE AdventureWorks;
GO
WITH XMLNAMESPACES (
  'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo' AS CI,
  'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes' AS ACT)
SELECT ContactID 
FROM   Person.Contact
WHERE  AdditionalContactInfo.exist
('//ACT:telephoneNumber/ACT:number[.="206-555-1234"]') = 1

/************************************/

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")
SELECT CatalogDescription.value('(/PD:ProductDescription/@ProductModelID)[1]', 'int') as ModelID,       CatalogDescription.value('(/PD:ProductDescription/@ProductModelName)[1]', 'varchar(30)') as ModelName        
FROM Production.ProductModel   
WHERE ProductModelID = 35

/************************************/