New version page

UW CSE 444 - XQuery in SQL Server

Documents in this Course
XML

XML

48 pages

SQL

SQL

25 pages

SQL

SQL

42 pages

Recovery

Recovery

30 pages

SQL

SQL

36 pages

Indexes

Indexes

35 pages

Security

Security

36 pages

Wrap-up

Wrap-up

6 pages

SQL

SQL

37 pages

More SQL

More SQL

48 pages

SQL

SQL

35 pages

XML

XML

46 pages

Triggers

Triggers

26 pages

Load more
Upgrade to remove ads

This preview shows page 1-2-3-27-28-29 out of 29 pages.

Save
View Full Document
Premium Document
Do you want full access? Go Premium and unlock all 29 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 29 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 29 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 29 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 29 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 29 pages.
Access to all documents
Download any document
Ad free experience

Upgrade to remove ads
Unformatted text preview:

1Lecture 12:XQuery in SQL ServerMonday, October 23, 20062Announcements• Homework 2 due on Wednesday• Midterm on Friday. To study:– SQL– E/R diagrams– Functional dependencies and BCNF• Project phase 2 due next Wednesday3Sorting in XQuery<publisher_list>{ FOR $b IN document("bib.xml")//book[year = “97”] ORDER BY $b/price/text()RETURN <book> { $b/title , $b/price}</book>}</publisher_list><publisher_list>{ FOR $b IN document("bib.xml")//book[year = “97”] ORDER BY $b/price/text()RETURN <book> { $b/title , $b/price}</book>}</publisher_list>4If-Then-ElseFOR $h IN //holdingRETURN <holding> { $h/title, IF $h/@type = "Journal" THEN $h/editorELSE $h/author}</holding>FOR $h IN //holdingRETURN <holding> { $h/title, IF $h/@type = "Journal" THEN $h/editorELSE $h/author}</holding>5Existential QuantifiersFOR $b IN //bookWHERE SOME $p IN $b//para SATISFIEScontains($p, "sailing") AND contains($p, "windsurfing")RETURN { $b/title }FOR $b IN //bookWHERE SOME $p IN $b//para SATISFIEScontains($p, "sailing") AND contains($p, "windsurfing")RETURN { $b/title }6Universal QuantifiersFOR $b IN //bookWHERE EVERY $p IN $b//para SATISFIEScontains($p, "sailing")RETURN { $b/title }FOR $b IN //bookWHERE EVERY $p IN $b//para SATISFIEScontains($p, "sailing")RETURN { $b/title }7Duplicate Elimination• distinct-values(list-of-text-values)• How do we eliminate duplicate “tuples” ?<row> <a>3</a> <b>100</b> </row><row> <a>8</a> <b>500</b> </row><row> <a>3</a> <b>100</b> </row><row> <a>3</a> <b>200</b> </row><row> <a>8</a> <b>500</b> </row> <row> <a>3</a> <b>100</b> </row><row> <a>8</a> <b>500</b> </row><row> <a>3</a> <b>100</b> </row><row> <a>3</a> <b>200</b> </row><row> <a>8</a> <b>500</b> </row> <row> <a>3</a> <b>100</b> </row><row> <a>8</a> <b>500</b> </row><row> <a>3</a> <b>200</b> </row><row> <a>3</a> <b>100</b> </row><row> <a>8</a> <b>500</b> </row><row> <a>3</a> <b>200</b> </row>8FOR v.s. LETFOR• Binds node variables  iterationLET• Binds collection variables  one value9FOR v.s. LETFOR $x IN /bib/bookRETURN <result> { $x } </result>FOR $x IN /bib/bookRETURN <result> { $x } </result>LET $x := /bib/bookRETURN <result> { $x } </result>LET $x := /bib/bookRETURN <result> { $x } </result>Returns:<result> <book>...</book></result><result> <book>...</book></result><result> <book>...</book></result>...Returns:<result> <book>...</book><book>...</book><book>...</book>...</result>10XQuerySummary:• FOR-LET-WHERE-RETURN = FLWRFOR/LET ClausesWHERE ClauseRETURN ClauseList of tuplesList of tuplesInstance of Xquery data model11Collections in XQuery• Ordered and unordered collections– /bib/book/author/text() = an ordered collection: result is in document order– distinct-values(/bib/book/author/text()) = an unordered collection: the output order is implementation dependent• LET $a := /bib/book  $a is a collection• $b/author  a collection (several authors...)RETURN <result> { $b/author } </result>RETURN <result> { $b/author } </result>Returns:<result> <author>...</author><author>...</author><author>...</author>...</result>12Collections in XQueryWhat about collections in expressions ?• $b/price  list of n prices• $b/price * 0.7  list of n numbers• $b/price * $b/quantity  list of n x m numbers ??• $b/price * ($b/quant1 + $b/quant2) ≠$b/price * $b/quant1 + $b/price * $b/quant2 !!13Other XML Topics• Name spaces• XML API:– DOM = “Document Object Model”• XML languages:– XSLT• XML Schema• Xlink, XPointer• SOAPAvailable from www.w3.org(but don’t spend rest of your lifereading those standards !)14XML in SQL Server 2005• Create tables with attributes of type XML• Use Xquery in SQL queries• Rest of the slides are from:Shankar Pal et al., Indexing XML data stored in a relational database, VLDB’200415CREATE TABLE DOCS (ID int primary key, XDOC xml)CREATE TABLE DOCS (ID int primary key, XDOC xml)SELECT ID, XDOC.query(’for $s in /BOOK[@ISBN= “1-55860-438-3”]//SECTIONreturn <topic>{data($s/TITLE)} </topic>')FROM DOCSSELECT ID, XDOC.query(’for $s in /BOOK[@ISBN= “1-55860-438-3”]//SECTIONreturn <topic>{data($s/TITLE)} </topic>')FROM DOCS16XML Methods in SQL• Query() = returns XML data type• Value() = extracts scalar values• Exist() = checks conditions on XML nodes• Nodes() = returns a rowset of XML nodes that the Xquery expression evaluates to17Examples• From here:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5xml.asp18XML TypeCREATE TABLE docs (pk INT PRIMARY KEY, xCol XML not null)CREATE TABLE docs (pk INT PRIMARY KEY, xCol XML not null)19Inserting an XML ValueINSERT INTO docs VALUES (2, '<doc id="123"><sections><section num="1"><title>XML Schema</title></section><section num="3"><title>Benefits</title></section><section num="4"><title>Features</title></section></sections></doc>')INSERT INTO docs VALUES (2, '<doc id="123"><sections><section num="1"><title>XML Schema</title></section><section num="3"><title>Benefits</title></section><section num="4"><title>Features</title></section></sections></doc>')20Query( )SELECT pk, xCol.query('/doc[@id = 123]//section')FROM docsSELECT pk, xCol.query('/doc[@id = 123]//section')FROM docs21Exists( )SELECT xCol.query('/doc[@id = 123]//section') FROM docsWHERE xCol.exist ('/doc[@id = 123]') = 1SELECT xCol.query('/doc[@id = 123]//section') FROM docsWHERE xCol.exist ('/doc[@id = 123]') = 122Value( )SELECT xCol.value('data((/doc//section[@num = 3]/title)[1])', 'nvarchar(max)')FROM docsSELECT xCol.value('data((/doc//section[@num = 3]/title)[1])', 'nvarchar(max)')FROM docs23Nodes( )SELECT nref.value('first-name[1]', 'nvarchar(50)') AS FirstName,nref.value('last-name[1]', 'nvarchar(50)')AS LastNameFROM @xVar.nodes('//author') AS R(nref)WHERE nref.exist('.[first-name != "David"]') = 1SELECT nref.value('first-name[1]', 'nvarchar(50)') AS FirstName,nref.value('last-name[1]', 'nvarchar(50)')AS LastNameFROM @xVar.nodes('//author') AS R(nref)WHERE nref.exist('.[first-name != "David"]') = 124Nodes( )SELECT nref.value('@genre', 'varchar(max)') LastNameFROM docs CROSS APPLY xCol.nodes('//book') AS R(nref)SELECT nref.value('@genre', 'varchar(max)') LastNameFROM docs CROSS APPLY xCol.nodes('//book') AS R(nref)25Internal Storage• XML is “shredded” as a table• A few important ideas:– Dewey decimal numbering of nodes; store in clustered B-tree indes– Use only odd numbers to allow insertions–


View Full Document
Download XQuery in SQL Server
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view XQuery in SQL Server and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view XQuery in SQL Server 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?