Lecture 12: XQuery in SQL ServerAnnouncementsSorting in XQueryIf-Then-ElseExistential QuantifiersUniversal QuantifiersDuplicate EliminationFOR v.s. LETSlide 9XQueryCollections in XQuerySlide 12Other XML TopicsXML in SQL Server 2005PowerPoint PresentationXML Methods in SQLExamplesXML TypeInserting an XML ValueQuery( )Exists( )Value( )Nodes( )Slide 24Internal StorageSlide 26Slide 27Slide 28Slide 291Lecture 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 //holding RETURN <holding> { $h/title, IF $h/@type = "Journal" THEN $h/editor ELSE $h/author } </holding>FOR $h IN //holding RETURN <holding> { $h/title, IF $h/@type = "Journal" THEN $h/editor ELSE $h/author } </holding>5Existential QuantifiersFOR $b IN //bookWHERE SOME $p IN $b//para SATISFIES contains($p, "sailing") AND contains($p, "windsurfing")RETURN { $b/title }FOR $b IN //bookWHERE SOME $p IN $b//para SATISFIES contains($p, "sailing") AND contains($p, "windsurfing")RETURN { $b/title }6Universal QuantifiersFOR $b IN //bookWHERE EVERY $p IN $b//para SATISFIES contains($p, "sailing")RETURN { $b/title }FOR $b IN //bookWHERE EVERY $p IN $b//para SATISFIES contains($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”]//SECTION return <topic>{data($s/TITLE)} </topic>')FROM DOCSSELECT ID, XDOC.query(’ for $s in /BOOK[@ISBN= “1-55860-438-3”]//SECTION return <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
View Full Document
Unlocking...