1 / 37

C09. SQL-S + XML

C09. SQL-S + XML. Date Semistructurate, 2012-2013. C 09 . DS. SQL-S + XML. C09 - DS. Tip de data: XML XML untyped / typed untyped = nu se cunoaste schema DECLARE @x xml CREATE TABLE T1( Col1 int, Col2 xml) typed = se cunoaste schema DECLARE @x xml (MySchemaCollection)

zamir
Download Presentation

C09. SQL-S + XML

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. C09. SQL-S + XML Date Semistructurate, 2012-2013

  2. C09. DS • SQL-S + XML

  3. C09 - DS • Tip de data: XML • XML untyped / typed • untyped = nu se cunoaste schema DECLARE @x xml CREATE TABLE T1( Col1 int, Col2 xml) • typed = se cunoaste schema DECLARE @x xml (MySchemaCollection) CREATE TABLE T1( Col1 int, Col2 xml (MySchemaCollection))

  4. C09 - 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(Col1 xml (DOCUMENT)) CREATE TABLE T(Col1 xml (CONTENT))- implicit

  5. C09 - 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

  6. C09 - DS • Exemplu creare schema XML: CREATE XML SCHEMA COLLECTION MySchemaCollection AS N'<?xml version="1.0" encoding="UTF-16"?> <xsd:schema targetNamespace="..." xmlns="..." elementFormDefault="qualified" xmlns:xsd="http://www.w3.org/2001/XMLSchema" > <xsd:complexType ... </xsd:complexType> <xsd:element> ... </xsd:element> </xsd:schema>' sau CREATE XML SCHEMA COLLECTION MySchemaCollection AS @xml unde @xml contine schema (ca XML sau nvarchar(max))

  7. C09 - DS • Exemplu alterare schema XML: ALTER XML SCHEMA COLLECTION MySchemaCollection ADD ' <schema xmlns="http://www.w3.org/2001/XMLSchema" targetNamespace="http://MySchema/test_xml_schema"> <element name="anotherElement" 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 MySchemaCollection

  8. C09 - 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

  9. C09 - 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

  10. C09 - 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>

  11. C09 - 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, ntextsausql_variant. Obs: Din motive de performanta – in loc sa se foloseasca value() pentru comparatii, e indicat de folosit exist() impreuna cu sql:column().

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

  13. C09 - 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

  14. C09 - DS • exist() Exemplu: declare @x xml declare @f bit set @x = '<catalog ziua = "2011-01-01Z"><ziua>2011-01-01Z</ziua></catalog>' set @f = @x.exist('/catalog[(@ziua cast as xs:date?) eq xs:date("2011-01-01Z")]') select @f -- => 1 set @f = @x.exist('/catalog/ziua[xs:date(text()[1]) = xs:date("2011-01-01Z")]') 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.

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

  16. C09 - 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.

  17. C09 - DS • insert (XML DML) • Insereaza unul sau mai multe noduri identificate prin Expression1 ca noduri fiu sau vecin ale nodului identificat prin Expression2. insert Expression1 {as first | as last} {into | after | before} Expression2

  18. C09 - DS • insert (XML DML) • Argumente: • Expression1 – 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 Expression2. 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 • Expression2 – 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).

  19. C09 - DS • replace value of (XML DML) • Actualizeaza valoarea unui nod intr-un document XML. replace value of Expression1 with Expression2 • Argumente: • Expression1 – 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. • Expression2 – identifica noua valoare a nodului destinatie; poate fi nod de tip simplu (se foloseste implicit data()); o lista de valori.

  20. C09 - 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.

  21. C09 - 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 <discipline> <codd>codnou</codd> <denumired>disciplina</denumired> </discipline> after (/facultate/discipline[position()=last()])[1]') set @x.modify('insert (<culoare>albastru</culoare>, <volum>25</volum>) into (//produs[@idp = "P4"])[1]')

  22. C09 - 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]')

  23. C09 - DS • Exemple – insert: declare @x xml set @x = N' <radacina> <nod><copil>AAA</copil><copil>BBB</copil></nod> <nod><copil>CCC</copil><copil>DDD</copil></nod> </radacina> ' <radacina> <nod> <copil>AAA</copil> <copil>BBB</copil> <i>1</i> <i>2</i> <i>3</i> </nod> <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> <nrcopii>4</nrcopii> <nod> <copil>AAA</copil> <copil>BBB</copil> </nod> <nod> <copil>CCC</copil> <copil>DDD</copil> </nod> </radacina>

  24. C09 - 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 "P11"') set @x.modify('delete //student//rezultat')

  25. C09 - DS • nodes() Se foloseste cand parti dintr-un document XML se doresc a fi “imprastiate” intr-o 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.

  26. C09 - 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)

  27. C09 - 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>

  28. C09 - 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 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>

  29. C09 - 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 No column name --------------------------------------------------------- (primul element <student>) (prima nota a studentului din col 1) (primul element <student>) (a doua nota a studentului din col 1) (al doilea element <student>) (prima nota a studentului din col 1)

  30. C09 - DS • Detalii rezultat exemplu anterior (prima inregistrare): No column name No column name --------------------------------------------------------- <student> <cods>2</cods> <nrmatricol>9181</nrmatricol> <nume>Andreica Marius</nume> <grupa>211</grupa> <rezultate> <rezultat> <disciplina>MI004</disciplina> <nota>7</nota> </rezultat> <rezultat> <disciplina>MI006</disciplina> <nota>9</nota> </rezultat> </rezultate> </student> <rezultat> <disciplina>MI004</disciplina> <nota>7</nota> </rezultat>

  31. C09 - 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 applyst.x.nodes('.//rezultat') as r(y) => nrmatr nume disc nota ---------- -------------------------------------- ---------- ---- 9179 Alexandrescu Ciprian MI074 5 8481 Alexandru Ionel MI074 7 8481 Alexandru Ionel MI003 8 601 Rogojan Loredana MI004 10 601 Rogojan Loredana MI006 7 9127 Duma Alexandru Mihai MI074 9 9127 Duma Alexandru Mihai MI004 5 9181 Andreica Marius MI004 7

  32. C09 - DS select txml.*, c.query('.') from txml cross apply doc.nodes('/facultate/sectie') as t(c) => iddoc doc No column name ---- ------------------------- 11 <facultate>...</facultate> <sectie><cods>1</cods>...</sectie> 11 <facultate>...</facultate> <sectie><cods>2</cods>...</sectie> 11 <facultate>...</facultate> <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.

  33. C09 - 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 dens ---- ------------------------- 1 Matematicã 2 Informaticã 3 Matematicã-Informaticã cods nume ---- ------------------------- 2 Andreica Marius 2 Alexandrescu Ciprian 3 Iacob Marian Dan 1 Alexandru Ionel 3 Mihalce Livia-Florina

  34. C09 - 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.se.nodes('./denumires') as tden(d) => se den cods denumires --------------------- ------------------- <sectie>... <denumires>... 1 Matematicã <sectie>... <denumires>... 2 Informaticã <sectie>... <denumires>... 3 Matematicã-Informaticã

  35. C09 - DS select se = c.query('.'), st = c.query('../student[.//rezultat]'), st2 = 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 st st2 nrst ------------------------- -------- ------------ <sectie><cods>1</cods>... <student>... <student>... 4 <sectie><cods>2</cods>... <student>... <student>... 12 <sectie><cods>3</cods>... <student>... <student>... 9 ... studentii din sectia inregistrarii curente care au rezultate toti studentii din xml care au rezultate

  36. C09 - 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 cods dens cods nume ---- ------------------------------------ ---- -------------------- 2 Informaticã 2 Andreica Marius 2 Informaticã 2 Alexandrescu Ciprian 3 Matematicã-Informaticã 3 Iacob Marian Dan 1 Matematicã 1 Alexandru Ionel 3 Matematicã-Informaticã 3 Mihalce Livia-Florina

  37. C09 - DS • Next 10 • FOR XML

More Related