C 09 SQLS XML Date Semistructurate 2012 2013

C 09. SQL-S + XML Date Semistructurate, 2012 -2013

C 09. DS • SQL-S + XML

C 09 - DS • Tip de data: XML • XML untyped / typed – untyped = nu se cunoaste schema DECLARE @x xml CREATE TABLE T 1( Col 1 int, Col 2 xml) – typed = se cunoaste schema DECLARE @x xml (My. Schema. Collection) CREATE TABLE T 1( Col 1 int, Col 2 xml (My. Schema. Collection))

C 09 - DS • Observatie: implicit, tipul XML accepta portiuni dintr-un document XML (sunt respectate regulile XML, dar nu exista element radacina) • Se poate specifica o constrangere prin care o variabila / coloana sa contina documente XML valide (cu element radacina) CREATE TABLE T(Col 1 xml (DOCUMENT)) CREATE TABLE T(Col 1 xml (CONTENT)) - implicit

C 09 - DS • Colectii de scheme – Se pot crea colectii de scheme – care sa contina una sau mai multe scheme. – O schema existenta se poate modifica sau se poate sterge. – => Comenzi de gestiune a schemelor XML: – CREATE XML SCHEMA COLLECTION – ALTER XML SCHEMA COLLECTION – DROP XML SCHEMA COLLECTION

C 09 - DS • Exemplu creare schema XML: CREATE XML SCHEMA COLLECTION My. Schema. Collection AS N'<? xml version="1. 0" encoding="UTF-16"? > <xsd: schema target. Namespace=". . . " xmlns=". . . " element. Form. Default="qualified" xmlns: xsd="http: //www. w 3. org/2001/XMLSchema" > <xsd: complex. Type. . . </xsd: complex. Type> <xsd: element>. . . </xsd: element> </xsd: schema>' sau CREATE XML SCHEMA COLLECTION My. Schema. Collection AS @xml unde @xml contine schema (ca XML sau nvarchar(max))

C 09 - DS • Exemplu alterare schema XML: ALTER XML SCHEMA COLLECTION My. Schema. Collection ADD ' <schema xmlns="http: //www. w 3. org/2001/XMLSchema" target. Namespace="http: //My. Schema/test_xml_schema"> <element name="another. Element" type="byte"/> </schema>‘ Observatie: se pot adauga elemente la o schema existenta sau se poate adauga o noua schema. ---------------------- • Exemplu stergere schema XML: DROP XML SCHEMA COLLECTION My. Schema. Collection

C 09 - DS • Metode pentru XML – query() – interogheaza o instanta XML – value() – obtine o valoare de un tip SQL dintr-o instanta XML – exist() – verifica daca o interogare returneaza un rezultat ne-vid sau vid – modify() – permite modificarea instantelor XML (inserare, actualizare, stergere) – nodes() – ofera o viziune (mai) relationala asupra unu document XML

C 09 - DS • query() Interogheaza o instanta XML. Rezultatul e de tip XML (fara tip). query ('XQuery') Argumente: XQuery – este un string, o expresie XQuery. Exemplu: declare @x xml set @x = ‘<xml>. . . ’ select @x. query(‘XQuery expression’) Sau create table T (x xml) select x. query(‘XQuery expression’) from T

C 09 - DS • query() Exemplu: declare @x xml set @x = ‘<facultate>. . . ’ select @x. query(‘ <studenti> { for $st in //student order by $st/nume return <student> {$st/nume/text()} </student> } </studenti>’) <studenti> <student> Popescu </student> <student> Mimi </student>. . . </studenti>

C 09 - DS • value() Efectueaza o interogare XQuery pe o instanta XML si returneaza o valoare de tip SQL (valoare scalara). Este practic de utilizat cand se doreste compararea directa a unor valori scalare (native SQL) cu valori ale unor noduri XML. value (XQuery, SQLType) Argumente: XQuery – expresie XQuery (sir de caractere); trebuie sa returneze exact o valoare (altfel – eroare) SQLType – tipul SQL care se doreste a-l avea valoarea returnata; nu poate fi XML, image, text, ntext sau sql_variant. Obs: Din motive de performanta – in loc sa se foloseasca value() pentru comparatii, e indicat de folosit exist() impreuna cu sql: column().

C 09 - DS • value() Exemplu: declare @x xml declare @i int set @x = ‘<facultate>. . . </facultate>’ set @i = @x. value(‘(/facultate/sectie/cods)[1]’, ‘int’) print @i

C 09 - DS • exist() Returneaza un bit: 1 daca expresia XQuery returneaza cel putin un nod XML 0 daca expresia XQuery nu returneaza nimic NULL daca instanta XML pe care s-a executat interogarea contine NULL exist (XQuery) Argumente: XQuery – expresie XQuery

C 09 - DS • exist() Exemplu: declare @x xml declare @f bit set @x = '<catalog ziua = "2011 -01 -01 Z"><ziua>2011 -01 -01 Z</ziua></catalog>' set @f = @x. exist('/catalog[(@ziua cast as xs: date? ) eq xs: date("2011 -01 -01 Z")]') select @f -- => 1 set @f = @x. exist('/catalog/ziua[xs: date(text()[1]) = xs: date("2011 -01 -01 Z")]') select @f • • -- => 0 Obs: cast as xs: date? este folosit pentru a converti valoarea la tipul xs: date (pentru a fi ulterior comparata). Valoarea atributului @ziua este fara tip. Pentru ca aceasta valoare sa fie comparata, este implicit convertita la tipul din partea dreapta a comparatiei – tipul xs: date. Se poate folosi functia constructor xs: date() pentru conversie.

C 09 - DS • exist() Exemplu: select iddoc, doc. query(' <id_document doc="{sql: column("iddoc")}"/> ') from txml 1 where doc. exist('//Rezultate[not(Rezultat)]') = 1 10, <id_document doc="10" />

C 09 - DS • modify() Modifica continutul unui document XML (variabila, coloana). Primeste o comanda XML DML – pentru insert, update sau detele de noduri. MODIFY() poate fi folosita doar in clauza SET a intructiunilor UPDATE sau in instructiunile SET. modify (XML_DML) Argumente: XML_DML - string care reprezinta o comanda XML DML. XML Data Modification Language (XML DML) XML DML este o extensie a XQuery. XML DML (case sensitive): • insert • delete • replace value of Observatie: o comanda XML DML primeste o instanta XML valida, si, in urma modificarilor, trebuie sa ramana valida.

C 09 - DS • insert (XML DML) • Insereaza unul sau mai multe noduri identificate prin Expression 1 ca noduri fiu sau vecin ale nodului identificat prin Expression 2. insert Expression 1 {as first | as last} {into | after | before} Expression 2

C 09 - DS • insert (XML DML) • • Argumente: Expression 1 – identifica unul sau mai multe noduri care sa fie inserate; poate fi constanta XML sau o expresie XQuery. Poate avea ca rezultat un nod, un nod text, sau o secventa ordonata de noduri (sau multimea vida). into – inserarea are loc ca descendenti directi (elemente fiu) ai nodului identificat de Expression 2. Daca nodul destinatie are deja unul sau mai multi fii, trebuie folosit as first sau as last (default) pentru a specifica noile noduri unde sa fie adaugate. Aceste optiuni sunt ignorate cand se insereaza atribute. after – inserarea are loc imediat dupa nodul destinatie (ca vecin); nu se poate folosi cand se insereaza atribute. before – inserarea are loc imediat inainte de nodul destinatie (ca vecin); nu se poate folosi cand se insereaza atribute Expression 2 – identifica nodul destinatie”” (inserarea are loc relativ la acest nod). Poate fi o expresie XQuery care returneaza exact un nod din documentul XML (daca mai multe – eroare; poate fi multimea vida). • •

C 09 - DS • replace value of (XML DML) • Actualizeaza valoarea unui nod intr-un document XML. replace value of Expression 1 with Expression 2 • Argumente: • Expression 1 – identifica nodul a carui valoare va fi actualizata (trebuie sa identifice un singur nod; daca mai multe – eroare; poate sa fie multimea vida); returneaza un nod (tip simplu – lista sau tipuri atomice), nod text sau atribut. • Expression 2 – identifica noua valoare a nodului destinatie; poate fi nod de tip simplu (se foloseste implicit data()); o lista de valori.

C 09 - DS • delete (XML DML) • Sterge noduri dintr-o instanta XML. delete Expression • Argumente: • Expression – expresie XQuery care identifica nodurile care sa fie sterse; stergerea este recursiva.

C 09 - DS • Exemple – inserare elemente: set @x. modify(' insert <shef>boian</shef> into (/facultate/sectie)[1]') set @x. modify(' insert <shef>boian</shef> as first into (/facultate/sectie)[1]') set @x. modify(' insert <discipline> <codd>codnou</codd> <denumired>disciplina</denumired> </discipline> before (/facultate/student)[1]') set @x. modify(' insert (<culoare>albastru</culoare>, <volum>25</volum>) into (//produs[@idp = "P 4"])[1]') <discipline> <codd>codnou</codd> <denumired>disciplina</denumired> </discipline> after (/facultate/discipline[position()=last()])[1]')

C 09 - DS • Exemple – inserare atribut, text, comentariu: declare @i int set @i = 10 set @x. modify('insert attribute nrshefi {sql: variable("@i")} into (/facultate/sectie)[1]') set @x. modify('insert attribute nrshefi {"1"} into (/facultate/sectie)[1]') set @x. modify('insert text {"date sectie"} as first into (//sectie)[1] ') set @x. modify('insert <!--datele facultatii de mate info--> as first into (/facultate)[1]')

C 09 - DS • Exemple – insert: declare @x xml set @x = N' <radacina> <nod> <copil>AAA</copil> <copil>BBB</copil> </nod> <copil>CCC</copil> <copil>DDD</copil> </nod> </radacina> ' set @x. modify(' insert for $i in (1, 2, 3) return <i>{$i}</i> into (//nod)[1]') set @x. modify(' insert if (count(//copil) > 3) then (<nrcopii>{count(//copil)}</nrcopii>) else () as first into (/radacina)[1]') <radacina> <nod> <copil>AAA</copil> <copil>BBB</copil> <i>1</i> <i>2</i> <i>3</i> </nod> <copil>CCC</copil> <copil>DDD</copil> </nod> </radacina> <nrcopii>4</nrcopii> <nod> <copil>AAA</copil> <copil>BBB</copil> </nod> <copil>CCC</copil> <copil>DDD</copil> </nod> </radacina>

C 09 - DS • Exemple – replace, delete: set @x. modify(' replace value of (//cods/text())[1] with 11') declare @dens varchar(10) set @dens = 'sectie noua' set @x. modify(' replace value of (//denumires/text())[1] with sql: variable("@dens")') set @x. modify('replace value of (//produs[1]/denumire/text())[1] with ( if (//produs[1]/pret > 1 ) then "aaa" else "bbb" )') set @x. modify(' replace value of (//produs[1]/@idp)[1] with "P 11"') set @x. modify('delete //student//rezultat')

C 09 - DS • nodes() Se foloseste cand parti dintr-un document XML se doresc a fi “imprastiate” intro multime de inregistrari intr-un tabel relational – identifica nodurile care vor fi mapate ca noi inregistrari. Fiecare instanta XML are un nod de context implicit. Pentru o instanta dintr-o coloana sau variabila – nodul document (radacina cea mai radacina posibila ). Rezultatul este o multime de inregistrari care contine copii logice ale instantei XML originale (parti din ea). In aceste copii logice, nodul de context al fiecarei inregistrari este setat ca fiind unul din nodurile identificare de expresia XQuery (interogari ulterioare pot naviga relativ la aceste noduri de context). nodes (XQuery) as Table(Column) XQuery – expresie XQuery care indica ceea ce se expune ca rowset rezultat. Daca construieste noduri – acestea vor face parte din rowset; daca rezulta multimea vida – rowset-ul va fi gol; daca rezulta intr-o secventa de valori atomice – eroare. Table(Column) – tabelul si coloana pentru rowset rezultat.

C 09 - DS declare @x xml set @x = (select doc from txml where iddoc = 11) select * from @x. nodes('/facultate/sectie/denumires') as t(c) => Eroare (nu se poate folosi coloana “c” in mod direct; se poate folosi doar cu metode XML - exist(), nodes(), query(), value() – sau in verificari IS [NOT] NULL)

C 09 - DS declare @x xml set @x = (select doc from txml where iddoc = 11) select c. query('. ') from @x. nodes('/facultate/sectie/denumires') as t(c) => (No column name) ----------------------<denumires>Matematicã</denumires> <denumires>Informaticã</denumires> <denumires>Matematicã-Informaticã</denumires> <denumires>Matematicã economicã</denumires> <denumires>Matematici aplicate</denumires> <denumires>Tehnologie Informaticã</denumires>

C 09 - DS declare @x xml set @x = (select doc from txml where iddoc = 11) select denumire = c. value('. /text()[1]', 'varchar(100)'), denumire_xml = c. query('. ') from @x. nodes('/facultate/sectie/denumires') as t(c) => denumire_xml ------------------------Matematicã <denumires>Matematicã</denumires> Informaticã <denumires>Informaticã</denumires> Matematicã-Informaticã <denumires>Matematicã-Informaticã</denumires> Matematicã economicã <denumires>Matematicã economicã</denumires> Matematici aplicate <denumires>Matematici aplicate</denumires> Tehnologie Informaticã <denumires>Tehnologie Informaticã</denumires>

C 09 - DS declare @x xml set @x = (select doc from txml where iddoc = 11) select x. query('. '), y. query('. ') from @x. nodes('//student') as st(x) cross apply st. x. nodes('. //rezultat') as r(y) => No column name --------------(primul element <student>) (al doilea element <student>) No column name --------------(prima nota a studentului din col 1) (a doua nota a studentului din col 1) (prima nota a studentului din col 1)

C 09 - DS • Detalii rezultat exemplu anterior (prima inregistrare): No column name ----------------------------<student> <rezultat> <cods>2</cods> <disciplina>MI 004</disciplina> <nrmatricol>9181</nrmatricol> <nota>7</nota> <nume>Andreica Marius</nume> </rezultat> <grupa>211</grupa> <rezultate> <rezultat> <disciplina>MI 004</disciplina> <nota>7</nota> </rezultat> <disciplina>MI 006</disciplina> <nota>9</nota> </rezultate> </student>

C 09 - DS select nrmatr = x. value('(. /nrmatricol)[1]', 'varchar(10)'), nume = x. value('(. /nume)[1]', 'varchar(50)'), disc = y. value('(. /disciplina)[1]', 'varchar(10)'), nota = y. value('(. /nota)[1]', 'tinyint') from @x. nodes('//student') as st(x) cross apply st. x. nodes('. //rezultat') as r(y) => nrmatr -----9179 8481 601 9127 9181 nume -------------------Alexandrescu Ciprian Alexandru Ionel Rogojan Loredana Duma Alexandru Mihai Andreica Marius disc -----MI 074 MI 003 MI 004 MI 006 MI 074 MI 004 nota ---5 7 8 10 7 9 5 7

C 09 - DS select txml. *, c. query('. ') from txml cross apply doc. nodes('/facultate/sectie') as t(c) => iddoc -------- No column name ----- 11 11 11. . . <facultate>. . . </facultate> <sectie><cods>1</cods>. . . </sectie> <sectie><cods>2</cods>. . . </sectie> <sectie><cods>3</cods>. . . </sectie> Observatie: Deoarece o singura inregistrare din txml contine un XML pentru care expresia XQuery “/facultate/sectie” intoarce un rowset not null, doar aceasta se multiplica prin cross apply pentru fiecare sectie din documentul XML de pe coloana doc.

C 09 - DS select cods = c. value('(. /cods/text())[1]', 'tinyint'), dens = c. value('(. /denumires/text())[1]', 'varchar(100)') from txml cross apply doc. nodes('/facultate/sectie') as t(c) select cods = c. value('(. /cods/text())[1]', 'tinyint'), nume = c. value('(. /nume/text())[1]', 'varchar(100)') from txml cross apply doc. nodes('//student') as t(c) => cods ---1 2 3 dens ------------Matematicã Informaticã Matematicã-Informaticã cods ---2 2 3 1 3 nume ------------Andreica Marius Alexandrescu Ciprian Iacob Marian Dan Alexandru Ionel Mihalce Livia-Florina

C 09 - DS select se = se. query('. '), den = d. query('. '), cods = se. value('(. //cods/text())[1]', 'int'), denumires = d. value('(. /text())[1]', 'varchar(100)') from txml cross apply doc. nodes('/facultate/sectie') as tse(se) cross apply tse. nodes('. /denumires') as tden(d) => se ------<sectie>. . . den -------<denumires>. . . cods -----1 2 3 denumires ---------Matematicã Informaticã Matematicã-Informaticã

C 09 - DS select se = c. query('. '), st = c. query('. . /student[. //rezultat]'), st 2 = c. query('let $s : =. /cods return. . /student[cods = $s and. //rezultat]'), nrst = c. query('for $s in. return count(. . /student[cods = $s/cods and. //rezultat])') from txml cross apply doc. nodes('/facultate/sectie') as t(c) => se ------------<sectie><cods>1</cods>. . . <sectie><cods>2</cods>. . . <sectie><cods>3</cods>. . . st -------<student>. . . st 2 -----<student>. . . nrst -----4 12 9 . . . toti studentii din xml care au rezultate studentii din sectia inregistrarii curente care au rezultate

C 09 - DS select * from (select cods = c. value('(. /cods/text())[1]', 'tinyint'), dens = c. value('(. /denumires/text())[1]', 'varchar(100)') from txml cross apply doc. nodes('/facultate/sectie') as t(c) ) as se inner join (select cods = c. value('(. /cods/text())[1]', 'tinyint'), nume = c. value('(. /nume/text())[1]', 'varchar(100)') from txml cross apply doc. nodes('//student') as t(c) ) as st on st. cods = se. cods ---2 2 3 1 3 dens ------------------Informaticã Matematicã-Informaticã cods ---2 2 3 1 3 nume ----------Andreica Marius Alexandrescu Ciprian Iacob Marian Dan Alexandru Ionel Mihalce Livia-Florina

C 09 - DS • Next 10 – FOR XML