USE [AdventureWorks2008]
GO
----zad1
SELECT xml_schema_namespace(N'production',N'ProductDescriptionSchemaCollection');
GO
--zad2
SELECT TABLE_SCHEMA FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'xml'
GO
--zad3
CREATE XML SCHEMA COLLECTION ProductSchema AS '
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.microsoft.com/schemas/adventure-works/products"
xmlns:prod="http://www.microsoft.com/schemas/adventure-works/products">
<xs:element name="Product">
<xs:complexType>
<xs:sequence>
<xs:element ref="prod:ProductID" />
<xs:element ref="prod:ProductName" />
<xs:element ref="prod:SupplierID" />
<xs:element ref="prod:CategoryID" />
<xs:element ref="prod:QuantityPerUnit" />
<xs:element ref="prod:UnitPrice" />
<xs:element ref="prod:UnitsInStock" />
<xs:element ref="prod:UnitsOnOrder" />
<xs:element ref="prod:ReorderLevel" />
<xs:element ref="prod:Discontinued" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="ProductID" type="xs:integer" />
<xs:element name="ProductName" type="xs:string" />
<xs:element name="SupplierID" type="xs:integer" />
<xs:element name="CategoryID" type="xs:integer" />
<xs:element name="QuantityPerUnit" type="xs:string" />
<xs:element name="UnitPrice" type="xs:double" />
<xs:element name="UnitsInStock" type="xs:integer" />
<xs:element name="UnitsOnOrder" type="xs:integer" />
<xs:element name="ReorderLevel" type="xs:integer" />
<xs:element name="Discontinued" type="xs:boolean" />
</xs:schema>
'
GO

ALTER XML SCHEMA COLLECTION ProductSchema ADD
'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.microsoft.com/schemas/adventure-works/products"
xmlns:prod="http://www.microsoft.com/schemas/adventure-works/products">
<xs:element name="NewField" type="xs:boolean" />
</xs:schema>'


GO
--zad5
select tot.TotalPurchaseYTD from
(SELECT Demographics.value('declare default element namespace
"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
(/IndividualSurvey//TotalPurchaseYTD)[1][. > 5000][. < 10000]', 'float') AS TotalPurchaseYTD
FROM Person.Person) AS
tot where tot.TotalPurchaseYTD is not null
GO

--zad6
SELECT Sum(Demographics.value('declare default element namespace
"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
(/IndividualSurvey/TotalChildren)[1]','int')) AS Children
FROM Person.Person
GO

----zad7
SELECT
Resume.query('declare namespace
ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
data(/ns:Resume[1]/ns:Address[1]/ns:Addr.Street)') FROM HumanResources.JobCandidate
GO

-- zad4

CREATE TABLE ProductDoc (ID INT IDENTITY PRIMARY KEY, ProductDoc XML(ProductSchema) NOT NULL)
GO
drop table dbo.ProductDoc
go
DROP XML SCHEMA COLLECTION ProductSchema;