-- jako ścieżki do plików należy wskazać lokalizacje, w których zainstalowano przykładowe pliki

USE MASTER
GO


-- utworzenie nowej przykładowej bazy danych
DROP DATABASE xmldb

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'xmldb') 
	CREATE DATABASE xmldb
GO

USE xmldb
GO

-- deklaracja zmiennej dla danych XML
declare @x XML
-- otwarcie schematu XSD; należy wskazać rzeczywistą lokalizację pliku
SELECT @x = s
FROM OPENROWSET (
 BULK 'C:\Customer.xsd',
 SINGLE_BLOB) AS TEMP(s)

select @x
-- sprawdzenie, czy schemat już istnieje
IF EXISTS(select * from sys.xml_schema_collections where name='Customer')
  DROP XML SCHEMA COLLECTION Customer
-- utworzenie schematu w kolekcji schematów bazy danych
CREATE XML SCHEMA COLLECTION Customer AS @x
GO
-- utworzenie tabeli, która używa schematu względem typu danych XML
CREATE TABLE xmltbl2 (pk INT IDENTITY PRIMARY KEY, xmlColWithSchema XML (CONTENT Customer))
GO

-- użycie dynamicznych widoków zarządczych do wyświetlenia schematu i przestrzeni nazw
SELECT * FROM sys.xml_schema_collections
go

SELECT * FROM sys.xml_schema_namespaces
go


-- powrót do schematu
USE xmldb
go
SELECT xml_schema_namespace(N'dbo',N'Customer')
Go


-- wstawienie danych do nowej tabeli

-- instrukcja zakończy się powodzeniem
INSERT INTO xmltbl2 (xmlColWithSchema)
SELECT *
FROM OPENROWSET (
 BULK 'C:\Customer1.xml',
 SINGLE_BLOB) AS TEMP
GO

-- instrukcja zakończy się niepowodzeniem z powodu weryfikacji poprawności schematu
INSERT INTO xmltbl2 (xmlColWithSchema)
SELECT *
FROM OPENROWSET (
 BULK 'C:\Customer2.xml',
 SINGLE_BLOB) AS TEMP
GO

-- bezpośrednie wstawienie danych XML
INSERT INTO xmltbl2(xmlColWithSchema) VALUES(
	'<doc id="d1" xmlns="urn:example/customer">
       <customer id="c7">
         <name>Tomek</name>
         <order id="1" year="2002"></order>
         <order id="2" year="2003"></order>
         <notes>
           <buys>gizmo</buys>
           <saleslead>Daniel</saleslead>
           <competitor>Acme</competitor>
         </notes>
       </customer>
     </doc>
    ')
Go

-- przykład z uprawnieniami schematu
-- nadanie uprawnień do schematu relacyjnego w bazie danych
GRANT ALTER ON SCHEMA::dbo TO User1
go
-- nadanie uprawnień do tworzenia kolekcji schematów xml w bazie danych
GRANT CREATE XML SCHEMA COLLECTION 
TO User1
go


-- utworzenie ograniczenia
--CREATE TABLE xmltbl3 (pk INT IDENTITY PRIMARY KEY, xmlColWithConstraint XML CHECK(xmlColWithConstraint.exist('declare namespace cust="urn:example/customer";
--     /cust:doc/cust:customer/cust:name') = 1 
--	))
--Go

-- utworzenie UDF dla ograniczenia
create function CheckCustomerName(@x xml)
returns bit
as
begin
  return @x.exist('declare namespace
cust="urn:example/customer";
/cust:doc/cust:customer/cust:name')
end;
Go

-- utworzenie ograniczenia
CREATE TABLE xmltbl3 (pk INT IDENTITY PRIMARY KEY,
xmlColWithConstraint XML CHECK(dbo.CheckCustomerName(xmlColWithConstraint) = 1))
Go


-- instrukcja nie powiedzie się ze względu na ograniczenie
INSERT INTO xmltbl3 (xmlColWithConstraint)
SELECT *
FROM OPENROWSET (
 BULK 'C:\Customer3.xml',
 SINGLE_BLOB) AS TEMP
GO

-- instrukcja zadziała!
INSERT INTO xmltbl3 (xmlColWithConstraint)
SELECT *
FROM OPENROWSET (
 BULK 'C:\Customer4.xml',
 SINGLE_BLOB) AS TEMP
GO


-- PRZYKŁADY XQUERY

-- utworzenie nowej tabeli
CREATE TABLE xmltblnew (pk INT IDENTITY PRIMARY KEY, people XML)
GO

-- wstawienie danych do nowej tabeli
INSERT INTO xmltblnew (people)
SELECT *
FROM OPENROWSET (
 BULK 'C:\peopleXML.xml',
 SINGLE_BLOB) AS TEMP
GO


-- uruchomienie zapytania XQuery dotyczącego elementu Age wszystkich osób
SELECT people.query(
'for $p in //people
where $p//age
return
  <person>
    <name>{$p//givenName}</name>
  </person>
'
)
FROM xmltblnew

-- użycie funkcji Count w XQuery
SELECT people.query(
'count(//person)
')
FROM XMLtblnew
go


-- użycie innych funkcji, takich jak round i avg
SELECT people.query(
'round(avg(//age))
')
FROM XMLtblnew
go


-- użycie istniejącej funkcji

SELECT pk, people FROM xmltblnew
  WHERE (people.exist('/people/person/name/givenName[.="Tom"]') = 1)

-- użycie funkcji Value
SELECT people.value('/people[1]/person[2]/age[1][text()]', 'integer') as age FROM XMLtblnew
Go

-- instrukcja wygeneruje błąd
SELECT people.value('/people/person[2]/age[1][text()]', 'integer') as age FROM XMLtblnew
Go

-- użycie bardziej zaawansowanych funkcji ciągów znaków
SELECT people.query(
'for $c in (/people)
where $c//age
return
  <customers>
	<name>
     {$c//givenName}
    </name>
    <match>{contains($c,"Daniel")}</match>
    <maxage>{max($c//age)}</maxage>
  </customers>
')
FROM xmltblnew
Go

-- ZAPYTANIA MIĘDZYDOMENOWE


-- utworzenie tabeli i wstawienie wartości

CREATE TABLE xmlTable (id int IDENTITY PRIMARY KEY,
                       CustomerID char(5),
                       Name varchar(50),
                       Address varchar(100),
                       xmlCustomer XML);
GO
INSERT INTO xmlTable
VALUES ('HN', 'Helion SA', 'Gliwice', '<Customer />');

-- użycie wartości w zapytaniu XQuery

GO
DECLARE @numemployees int;
SET @numemployees=500;
SELECT id, xmlCustomer.query(' 
declare namespace pd="urn:example/customer"; 
       <Customer
           CustomerID=       "{ sql:column("T.CustomerID") }" 
           CustomerName=     "{ sql:column("T.Name") }"
           CustomerAddress=  "{ sql:column("T.Address") }"
           NumEmployees=     "{ sql:variable("@numemployees") }">
        </Customer> 
') as Result FROM xmlTable T;
GO


-- DML z użyciem XQuery

-- najpierw wstawienie nowej wartości
UPDATE xmltblnew SET people.modify(
'insert <favoriteColor>Czerwony</favoriteColor> 
  as last into (/people/person[3])[1]')
where pk=1
go

-- wyświetlenie danych, aby zaprezentować zmianę
SELECT * FROM xmltblnew
go

-- modyfikacja wartości
UPDATE xmltblnew SET people.modify(
'replace value of (/people/person[3]/favoriteColor[1]/text())[1]
  with "Niebieski"')
where pk=1
go

-- wyświetlenie danych, aby zaprezentować zmianę
SELECT * FROM xmltblnew
go

-- usunięcie wartości
UPDATE xmltblnew SET people.modify(
'delete /people/person[3]/favoriteColor')
where pk=1
go

-- wyświetlenie danych, aby zaprezentować zmianę
SELECT * FROM xmltblnew
go


-- SEKCJA DOTYCZĄCA INDEKSOWANIA

-- utworzenie głównego indeksu XML
CREATE PRIMARY XML INDEX idx_xmlCol on xmltblnew(people)
go

-- utworzenie drugorzędnego indeksu PATH
CREATE XML INDEX idx_xmlCol_PATH on xmltblnew(people)
  USING XML INDEX idx_xmlCol FOR PATH
go
-- zapytanie, które użyje indeksu
SELECT people FROM xmltblnew
  WHERE (people.exist('/people/person/name/givenName[.="Tom"]') = 1)


-- utworzenie drugorzędnego indeksu PROPERTY
CREATE XML INDEX idx_xmlCol_PROPERTY on xmltblnew(people)
  USING XML INDEX idx_xmlCol FOR PROPERTY
go
-- zapytanie, które użyje indeksu
SELECT people.value('(/people/person/age)[1]', 'int') FROM xmltblnew
go

-- tworzenie drugorzędnego indeksu VALUE
CREATE XML INDEX idx_xmlCol_VALUE on xmltblnew(people)
  USING XML INDEX idx_xmlCol FOR VALUE
go
-- zapytanie, które użyje indeksu
SELECT people FROM xmltblnew WHERE people.exist('//age') = 1
go

-- PRZYKŁADY ZWIĄZANE Z DYNAMICZNYMI WIDOKAMI ZARZĄDCZYMI

SELECT * FROM sys.xml_schema_collections
SELECT * FROM sys.xml_schema_elements
SELECT * FROM sys.xml_schema_attributes
SELECT * FROM sys.xml_schema_namespaces
SELECT * FROM sys.xml_indexes
go

-- użycie niektórych widoków
SELECT *
FROM sys.xml_schema_collections xmlSchemaCollection 
       JOIN sys.xml_schema_namespaces xmlSchemaName
       ON (xmlSchemaName.xml_collection_id = xmlSchemaName.xml_collection_id)
WHERE xmlSchemaCollection.name = 'Customer'   
go


-- PRZYKŁAD Z WĘZŁEM KOŃCOWYM

-- poniższy kod trzeba uruchomić przed uruchomieniem aplikacji usługi sieciowej

-- utworzenie procedury składowanej
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE usp_SQLWS
(@msg nvarchar(256))
AS BEGIN
   select @msg as 'message'
END 

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO



-- utworzenie węzła końcowego
CREATE ENDPOINT SQLWS_endpoint
STATE = STARTED
  AS HTTP(
	PATH = '/sql/sample',
	AUTHENTICATION= ( INTEGRATED ),
    PORTS = ( CLEAR )
  )
FOR SOAP (
  WEBMETHOD
    'http://tempuri.org/'.'SQLWS' 
    (NAME = 'xmldb.dbo.usp_SQLWS'),
    BATCHES = ENABLED,
    WSDL = DEFAULT
  )
go

