Uploaded bytorp42
2,142 views

XML on SQL Server

The document discusses the integration of XML with SQL Server, detailing methods for creating and manipulating XML data using SQL queries. It covers schema creation, data insertion, querying techniques, and specifies issues related to XML formatting and the use of functions like exist() and value(). The content provides examples and insights into how XML data types are handled in SQL Server, particularly in the context of educational course information.

Downloaded 28 times
XML on SQL Server Kristian Torp Department of Computer Science Aalborg University people.cs.aau.dk/˜torp torp@cs.aau.dk November 5, 2015 daisy.aau.dk Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 1 / 69
Outline 1 Create and Insert 2 XML Queries Saying Hello exist() query() value() nodes() 3 Generating XML from Select Statements Raw Auto Path 4 Summary Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 2 / 69
Learning Goals Goals Overview of XML support on SQL Server Storing XML directly SQL Server Querying XML on SQL Server Standard Concepts are (fairly) general Code is DBMS specific DBMS Versions Tested SQL Server 2008 SQL Server 2012 Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 3 / 69
Create the Relational Schema Example (Create the Table) create table course ( id i n t primary key , dsc xml not n u l l ) Note A create table statement as we know it The XML data type named xml A mixture of “old” data types int and new data types xml The not-null constraint, just because we love them! Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 5 / 69
Insert Data Example (Insert a Row) i n s e r t i n t o course values (1 , ’<courses> <course id =”22” ects =”5” > <name>XML</name> <description >XPath and XQuery f o r querying XML documents </ description > </course> <course id =”11” ects =”5” > <name>DB</name> <description >Querying a r e l a t i o n a l database </ description > </course> </courses> ’ ) Note A plain insert statement The XML data type coersed from the text string Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 6 / 69
Insert Additional Row Example (Insert another Row) i n s e r t i n t o course values (2 , ’<courses> <course id =”44” ects =”10”> <name>Imperative Programming </name> <description >C and Pointers </ description > </course> <course id =”55” ects =”5” > <name>OOP</name> <description >Working with classes and objects </ description > </course> </courses> ’ ) Note Nothing new conceptually, simply an insert statement We now have a small database! Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 7 / 69
Database Content Example id dsc 1 <courses> <course id=” 22 ” ects=” 5 ”> <name>XML</name> <description>XPath . . . </ description> </ course> <course id=” 11 ” ects=” 5 ”> <name>DB</name> <description>Querying . . . </ description> </ course> </ courses> 2 <courses> <course id=” 44 ” ects=” 10 ”> <name>Imperative Programming</name> <description>C . . . </ description> </ course> <course id=” 55 ” ects=” 5 ”> <name>OOP</name> <description>Working . . . </ description> </ course> </ courses> Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 8 / 69
Not Well-formed XML Example (Insert a Row) i n s e r t i n t o course values (3 , ’<course id =”77” ects =”10”> <name>Object−Oriented Analysis and Design </name> <description >Modeling with classes and objects </ description > </course> <course id =”88” ects =”5” > <name>Software Architecture </name> <description >The big picture </ description > </course> ’ ) Note Has no root element This works fine! Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 9 / 69
Not Well-formed XML, cont. Example (End tag is missing) i n s e r t i n t o course values (4 , ’<course id =”99” ects =”10”> <name>Operating Systems </name> <description >Linux , Windows , and so on</ description > ’ ) Note XML parsing: line 3, character 61, unexpected end of input Example (Fix the missing end tag) i n s e r t i n t o course values (4 , ’<course id =”99” ects =”10”> <name>Operating Systems </name> <description >Linux , Windows , and so on</ description > </course> ’ ) Note Works! Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 10 / 69
Not Well-formed XML, cont. Example (Nesting Wrong) i n s e r t i n t o course values (5 , ’<course id =”13” ects =”5” > <name><more>Compiler Construction </name></more> <description >Lex and Yacc</ description > </course> ’ ) Note XML parsing: line 2, character 41, end tag does not match start tag Example (“Weird” Cases) i n s e r t i n t o course values (6 , ’ ’ ) i n s e r t i n t o course values (7 , ’NULL ’ ) Note Works! Example (Does this work?) i n s e r t i n t o course values (8 ,NULL) Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 11 / 69
Summary: Create and Insert Main Points Create table has new XML data type Very similar to the SQL/XML XML data type Insert statement works as expected XML documents, without root possible, tags must be matching Delete works as expected Not shown here There is special support for updating XML columns Updating only a part of the XML document in a cell Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 12 / 69
Recall the Database Content Example id dsc 1 <courses> <course id=” 22 ” ects=” 5 ”> <name>XML</name> <description>XPath . . . </ description> </ course> <course id=” 11 ” ects=” 5 ”> <name>DB</name> <description>Querying . . . </ description> </ course> </ courses> 2 <courses> <course id=” 44 ” ects=” 10 ”> <name>Imperative Programming</name> <description>C . . . </ description> </ course> <course id=” 55 ” ects=” 5 ”> <name>OOP</name> <description>Working . . . </ description> </ course> </ courses> Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 15 / 69
Say Hello Example (Use the Course Table) select dsc . query ( ” l e t $s := ’ Hello , World ! ’ return $s ” ) from course Note This does not work! And I used a long time to understand why not :-) Example (Try Again (almost the same)) select dsc . query ( ’ l e t $s := ” Hello , World ! ” return $s ’ ) from course Note Works! ’ and “ are different! dsc is a column on table course Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 16 / 69
Say Hello, cont Example (Say Hello from XQuery) select dsc . query ( ’ l e t $s := ” Hello , World ! ” return $s ’ ) from course Example (Result) (No column name) Hello, World! Hello, World! Note A hello for each row in the table This is a simple XQuery SQL Server generally follows the XQuery standard well Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 17 / 69
Say Hello, the T-SQL Way Example (Use a Variable) declare @x xml select @x. query ( ’ l e t $s := ” Hello , World ! ” return $s ’ ) Example (Result) (No column name) null Example (Initialize the Variable) declare @x xml = ’ ’ select @x. query ( ’ l e t $s := ” Hello , World ! ” return $s ’ ) Example (Result) (No column name) Hello, World! Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 18 / 69
Document IDs with more than 5 ECTS Courses Example (Use exist() in where ) select id from course where dsc . e x i s t ( ’ / / course [ @ects > 5] ’ ) Note An expression of non-boolean type specified in a context where a condition is expected, near ’)’ Example (Use exist() in select ) select id , dsc . e x i s t ( ’ / / course [ @ects > 5] ’ ) from course Example (Result) id (No column name) 1 0 2 1 Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 20 / 69
Retrying Document IDs with more than 5 ECTS Courses Example (Use exist() in where ) select id from course where dsc . e x i s t ( ’ / / course [ @ects > 5] ’ ) = 1 Example (Result) id 2 Note exist() returns 0, 1 or null This is a surprise! (when used to other DBMSs) Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 21 / 69
Recall the Database Content Example id dsc 1 <courses> <course id=” 22 ” ects=” 5 ”> <name>XML</name> <description>XPath . . . </ description> </ course> <course id=” 11 ” ects=” 5 ”> <name>DB</name> <description>Querying . . . </ description> </ course> </ courses> 2 <courses> <course id=” 44 ” ects=” 10 ”> <name>Imperative Programming</name> <description>C . . . </ description> </ course> <course id=” 55 ” ects=” 5 ”> <name>OOP</name> <description>Working . . . </ description> </ course> </ courses> Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 23 / 69
XPath for finding Course Names Example (Use XPath) select dsc . query ( ’ / courses / course /name ’ ) as res from course Example (Result) res <name>XML</name> <name>DB</name> <name>Imperative Programming</name> <name>OOP</name> null Note What was expected, it is really XPath! A null value for each row where the XPath has no match Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 24 / 69
XPath for finding Course Names in Text Format Example (Use text()) select dsc . query ( ’ / courses / course /name/ t e x t ( ) ’ ) as res from course Example (Result) res XMLDB Imperative ProgrammingOOP Note What was expected! Not really useful in an SQL context Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 25 / 69
Accessing Attributes Example (Look at Attributes) select dsc . query ( ’ / courses / course / @ects ’ ) as res from course Note XQuery [course.dsc.query()]: Attribute may not appear outside of an element Unexpected! Example (Look at Attributes) select dsc . query ( ’sum ( / courses / course / @ects ) ’ ) as res from course Example (Result) res 10 15 Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 26 / 69
More XPath Example (Find 5 ECTS Courses) select dsc . query ( ’ / courses / course [ @ects =5]/name/ t e x t ( ) ’ ) as res from course Example (Result) res XMLDB OOP Example (Filtering using where ) select dsc . query ( ’ / / course /name/ t e x t ( ) ’ ) as res from course where id = 1 Example (Result) res XMLDB Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 27 / 69
Multi-Column XPath Example (More than One Column) select dsc . query ( ’ / / course /name ’ ) as name, dsc . query ( ’ / / course / description ’ ) as descr from course Example (Result) name descr <name>XML</name> <name>DB</name> <description>XPath . . . </ description> <description>Querying . . . </ description> <name>Imperative . . . </name> <name>OOP</name> <description>C and Pointers</ description> <description>Working . . . </ description> Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 28 / 69
Multi-Column XPath with text() Example (Multiple Columns and text()) select dsc . query ( ’ / / course /name/ t e x t ( ) ’ ) as name, dsc . query ( ’ / / course / description / t e x t ( ) ’ ) as descr from course Example (Result) name descr XMLDB XPath ... Querying ... Imperative Programming OOP C and Pointers Working ... Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 29 / 69
Full FLWOR Example (FLWOR Query) select dsc . query ( ’ f o r $c in / / course where $c / @ects >= 5 return ( $c /name, $c / description ) ’ ) as res from course Example (Result) res <name>XML</name> <description>XPath and XQuery f o r querying XML documents</ description> <name>DB</name> <description>Querying a r e l a t i o n a l database</ description> <name>Imperative Programming</name> <description>C and Pointers</ description> <name>OOP</name> <description>Working with classes and objects</ description> Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 30 / 69
Recall the Database Content Example id dsc 1 <courses> <course id=” 22 ” ects=” 5 ”> <name>XML</name> <description>XPath . . . </ description> </ course> <course id=” 11 ” ects=” 5 ”> <name>DB</name> <description>Querying . . . </ description> </ course> </ courses> 2 <courses> <course id=” 44 ” ects=” 10 ”> <name>Imperative Programming</name> <description>C . . . </ description> </ course> <course id=” 55 ” ects=” 5 ”> <name>OOP</name> <description>Working . . . </ description> </ course> </ courses> Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 32 / 69
Get Course Names in a Relational Fashion Example (Using the value() Function) select dsc . value ( ’ / / course /name ’ , ’ nvarchar (30) ’ ) from course Note XQuery [course.dsc.value()]: ’value()’ requires a singleton (or empty sequence), found operand of type ’xdt:untypedAtomic *’ Example (Singleton’ing) select dsc . value ( ’ ( / / course /name ) [ 1 ] ’ , ’ nvarchar (30) ’ ) from course Example (Result) (No column name) XML Imperative Programming Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 33 / 69
Retrying Get Course Names in a Relational Fashion Example (First) select dsc . value ( ’ ( / / course [ 1 ] / name ) [ 1 ] ’ , ’ nvarchar (30) ’ ) from course Example (Result) (No column name) XML Imperative Programming Example (Last) select dsc . value ( ’ ( / / course /name ) [ l a s t ( ) ] ’ , ’ nvarchar (30) ’ ) from course Example (Result) (No column name) DB OOP Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 34 / 69
Value and Wrong SQL Data Type Example (First) select dsc . value ( ’ ( / / course /name ) [ 1 ] ’ , ’ nvarchar (30) ’ ) from course Example (Result) (No column name) XML Imperative Programming Example (Last) select dsc . value ( ’ ( / / course /name ) [ 1 ] ’ , ’ nvarchar (3) ’ ) from course Example (Result) (No column name) XML Imp Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 35 / 69
value() and Wrong SQL Data Type, cont. Example (String as an Integer) select dsc . value ( ’ ( / / course /name ) [ 1 ] ’ , ’ i n t ’ ) from course Note Conversion failed when converting the nvarchar value ’XML’ to data type int. Example (Integer Attribute) select dsc . value ( ’ ( / / course / @ects ) [ 1 ] ’ , ’ i n t ’ ) from course Example (Result) (No column name) 5 10 Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 36 / 69
Flattening using value() Example (String as an Integer) select id , dsc . value ( ’ ( / / course / @ects ) [ 1 ] ’ , ’ i n t ’ ) as ects from course where dsc . e x i s t ( ’ / / course [ @ects > 5] ’ ) = 1 Example (Result) id ects 2 10 Note Using value() and exist() functions in same query Still too few rows Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 37 / 69
Flattening using value(), cont Example (String as an Integer) select dsc . value ( ’ ( / / course / @id ) [ l a s t ( ) ] ’ , ’ i n t ’ ) as cid , dsc . value ( ’ ( / / course /name ) [ l a s t ( ) ] ’ , ’ nvarchar (30) ’ ) as cname , dsc . value ( ’ ( / / course / description ) [ l a s t ( ) ] ’ , ’ nvarchar (50) ’ ) as dsc . value ( ’ ( / / course / @ects ) [ l a s t ( ) ] ’ , ’ i n t ’ ) as ects from course Example (Result) cid cname dsc ects 11 DB Querying... 5 55 OOP Working... 5 Note Pure relational world result Result only has two rows (should have four) value() cannot be used must use nodes() Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 38 / 69
Flattening using nodes() Example (Flatten XML) select f l a t . course . value ( ’ ( . / @id ) [ 1 ] ’ , ’ i n t ’ ) as cid , f l a t . course . value ( ’ ( . / name ) [ 1 ] ’ , ’ varchar (50) ’ ) as cname , f l a t . course . value ( ’ ( . / description ) [ 1 ] ’ , ’ varchar (50) ’ ) as dsc , f l a t . course . value ( ’ ( . / @ects ) [ 1 ] ’ , ’ i n t ’ ) as ects from course cross apply dsc . nodes ( ’ / / course ’ ) f l a t ( course ) order by cid Example (Result) cid cname dsc ects 11 DB Querying... 5 22 XML XPath... 5 44 Imp. C and ... 10 55 OOP Working... 5 Note Relational view on XML data!!! Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 40 / 69
Hide XML Example (Create a View on XML Data) create view coursexml flat as select f l a t . course . value ( ’ ( . / @id ) [ 1 ] ’ , ’ i n t ’ ) as cid , f l a t . course . value ( ’ ( . / name ) [ 1 ] ’ , ’ varchar (50) ’ ) as cname , f l a t . course . value ( ’ ( . / description ) [ 1 ] ’ , ’ varchar (50) ’ ) as dsc , f l a t . course . value ( ’ ( . / @ects ) [ 1 ] ’ , ’ i n t ’ ) as ects from course cross apply dsc . nodes ( ’ / / course ’ ) f l a t ( course ) Example (Query the View) select ∗ from coursexml flat where ects = 5 and cid > 20 order by cid Example (Result) cid cname dsc ects 22 XML XPath... 5 55 OOP Working... 5 Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 41 / 69
Summary: Queries Functions Function Descrition exist() Check for existence query() XPath and XQuery value() Extract single value nodes() For flatting nodes modify() For (fine grained) updates of XML Note SQL Server only SQL/XML via programming languages Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 42 / 69
Master-Detail Example I Order id customer name amount OrderLine lineno price per unit quan. desc. has Assumptions An order cannot exist without order lines Amount is the sum of the amount of order lines for a give order Must be larger than zero Derived from order lines but stored because often queried Order line is a weak entity Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 44 / 69
Master-Detail Example II Example create table oorder ( oid i n t primary key , cname varchar (30) not n u l l ) Example create table oorder line ( oid i n t not null , line no i n t not n u l l check ( line no > 0) , dsc varchar (50) not null , quantity i n t not n u l l check ( quantity > 0) , price each numeric (6 ,2) not n u l l check ( price each > 0.0) , c o n s t r a i n t ol pk primary key ( oid , line no ) , c o n s t r a i n t o l o f k foreign key ( oid ) references oorder ( oid ) ) Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 45 / 69
Get XML Out Example select ∗ from oorder f o r xml raw Example (Result) <row oid=” 11 ” cname=”Anna” /> <row oid=” 22 ” cname=” Benny ” /> <row oid=” 33 ” cname=” Curt ” /> Note A select statement The for xml raw is SQL Server specific Pretty ugly format ;-) Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 47 / 69
Get Elements Out Example select ∗ from oorder f o r xml raw , elements Example (Result) <row> <oid>11</ oid> <cname>Anna</ cname> </ row> <row> <oid>22</ oid> <cname>Benny</ cname> </ row> <row> <oid>33</ oid> <cname>Curt</ cname> </ row> Note Much nicer Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 48 / 69
Rename Element Names Example select oid as ” order −id ” , cname as ” customer−name” from oorder f o r xml raw , elements Example (Result) <row> <order −id>11</ order −id> <customer−name>Anna</ customer−name> </ row> <row> <order −id>22</ order −id> <customer−name>Benny</ customer−name> </ row> snip Note A select statement The for xml raw is SQL Server specific Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 49 / 69
Rename Enclosed Element Example select oid as ” order −id ” , cname as ” customer−name” from oorder f o r xml raw ( ’ customer ’ ) , elements Example (Result) <customer> <order −id>11</ order −id> <customer−name>Anna</ customer−name> </ customer> <customer> <order −id>33</ order −id> <customer−name>Curt</ customer−name> </ customer> Note All columns are elements Missing a root element Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 50 / 69
Adding a Root Element Example select oid as ” order −id ” , cname as ” customer−name” from oorder f o r xml raw ( ’ customer ’ ) , elements , root ( ’ customers ’ ) Example (Result) <customers> <customer> <order −id>11</ order −id> <customer−name>Anna</ customer−name> </ customer> <customer> <order −id>33</ order −id> <customer−name>Curt</ customer−name> </ customer> </ customers> Note Now nice XML document Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 51 / 69
Joining Tables Example select ∗ from oorder , oorder line where oorder . oid = oorder line . oid f o r xml raw , elements Example (Result (snip)) <row> <oid>11</ oid> <cname>Anna</ cname> <oid>11</ oid> <line no>1</ line no> <dsc>Cola</ dsc> <quantity>1</ quantity> <price each>10.00</ price each> </ row> Note No nesting Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 52 / 69
Summary: Raw Overview Fast to get XML fragments out Nice simple extension to select statement Default output in attributes Either all attributes or all elements Mixture of attributes and elements hard Is a bit “raw” Additional options, type, xmldata, xmlschema Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 53 / 69
Get XML Out Example select ∗ from oorder f o r xml auto Example (Result) <row oid=” 11 ” cname=”Anna” /> <row oid=” 22 ” cname=” Benny ” /> <row oid=” 33 ” cname=” Curt ” /> Note At first glanse looks like for xml raw But wait, there is more ... Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 55 / 69
The Power of Auto Example select ∗ from oorder , oorder line where oorder . oid = oorder line . oid and oid < 30 f o r xml auto Example (Result) <oorder oid=” 11 ” cname=”Anna”> <o order line oid=” 11 ” line no =” 1 ” dsc=” Cola ” quantity =” 1 ” price each=” 10.00 ” <o order line oid=” 11 ” line no =” 2 ” dsc=” Chips ” quantity =” 1 ” price each=” 15.00 </ oorder> <oorder oid=” 22 ” cname=” Benny ”> <o order line oid=” 22 ” line no =” 1 ” dsc=” Cola ” quantity =” 1 ” price each=” 10.00 ” <o order line oid=” 22 ” line no =” 2 ” dsc=” Burger ” quantity =” 2 ” price each=” 20.0 <o order line oid=” 22 ” line no =” 3 ” dsc=” Chips ” quantity =” 1 ” price each=” 15.00 </ oorder> Note Does automatic nesting at “natural” levels Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 56 / 69
Rename Elements Example select oorder . oid as ” order ” , oorder . cname as ” customer ” , oorder line . dsc as ” description ” , oorder line . quantity , oorder line . price each as ” price ” from oorder , oorder line where oorder . oid = oorder line . oid f o r xml auto Example (Result (snip)) <oorder order=” 11 ” customer=”Anna”> <o order line description =” Cola ” quantity =” 1 ” price=” 10.00 ” /> <o order line description =” Chips ” quantity =” 1 ” price=” 15.00 ” /> </ oorder> <oorder order=” 22 ” customer=” Benny ”> <o order line description =” Cola ” quantity =” 1 ” price=” 10.00 ” /> <o order line description =” Burger ” quantity =” 2 ” price=” 20.00 ” /> <o order line description =” Chips ” quantity =” 1 ” price=” 15.00 ” /> </ oorder> Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 57 / 69
Add Root Element + Rename Example select ” order ” . oid as ” id ” , ” order ” . cname as ” customer ” , ” l i n e ” . dsc as ” description ” , ” l i n e ” . quantity , ” l i n e ” . price each as ” price ” from oorder as ” order ” , oorder line as ” l i n e ” where ” order ” . oid = ” l i n e ” . oid f o r xml auto , root ( ’ orders ’ ) Example (Result (snip)) <orders> <order id=” 11 ” customer=”Anna”> <l i n e description =” Cola ” quantity =” 1 ” price=” 10.00 ” /> <l i n e description =” Chips ” quantity =” 1 ” price=” 15.00 ” /> </ order> <order id=” 22 ” customer=” Benny ”> <l i n e description =” Cola ” quantity =” 1 ” price=” 10.00 ” /> <l i n e description =” Burger ” quantity =” 2 ” price=” 20.00 ” /> <l i n e description =” Chips ” quantity =” 1 ” price=” 15.00 ” /> </ order> </ orders> Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 58 / 69
Make it All Elements Example select ” order ” . oid as ” id ” , ” order ” . cname as ” customer ” , ” l i n e ” . dsc as ” description ” , ” l i n e ” . quantity , ” l i n e ” . price each as ” price ” from oorder as ” order ” , oorder line as ” l i n e ” where ” order ” . oid = ” l i n e ” . oid f o r xml auto , elements , root ( ’ orders ’ ) Example (Result (snip)) <orders> <order> <id>11</ id> <customer>Anna</ customer> <l i n e> <description>Cola</ description> <quantity>1</ quantity> <price>10.00</ price> </ l i n e> snip Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 59 / 69
Summary: Auto Overview Fast to get XML out even for joins Row tag can only be renamed by table alias Okay at getting nesting correct Either attributes or elements Options similar as for the xml raw output Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 60 / 69
Get XML Out Example select oorder . oid as ” id ” , oorder . cname as ” customer ” , oorder line . line no as ” lineno ” , oorder line . dsc as ” description ” , oorder line . quantity , oorder line . price each as ” price ” from oorder , oorder line where oorder . oid = oorder line . oid f o r xml path Example (Result (snip)) <row> <id>11</ id> <customer>Anna</ customer> <lineno>1</ lineno> <description>Cola</ description> <quantity>1</ quantity> <price>10.00</ price> </ row> Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 62 / 69
Adding Structure Example select oorder . oid as ” id ” , oorder . cname as ” customer ” , oorder line . line no as ” lineno ” , oorder line . dsc as ” description ” , oorder line . quantity , oorder line . price each as ” price ” from oorder , oorder line where oorder . oid = oorder line . oid f o r xml path ( ’ order ’ ) , root ( ’ orders ’ ) Example (Result (snip)) <orders> <order> <id>11</ id> <customer>Anna</ customer> <lineno>1</ lineno> <description>Cola</ description> <quantity>1</ quantity> <price>10.00</ price> </ order> <order> Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 63 / 69
Adding Attributes and Nesting Line Items Example select oorder . oid as ”@id” , oorder . cname as ” customer ” , oorder line . line no as ” l i n e / lineno ” , oorder line . dsc as ” l i n e / description ” , oorder line . quantity as ” l i n e / quantity ” , oorder line . price each as ” l i n e / price ” from oorder , oorder line where oorder . oid = oorder line . oid f o r xml path ( ’ order ’ ) , root ( ’ orders ’ ) Example (Result (snip)) <orders> <order id=” 11 ”> <customer>Anna</ customer> <l i n e> <lineno>1</ lineno> <description>Cola</ description> <quantity>1</ quantity> <price>10.00</ price> </ l i n e> </ order> Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 64 / 69
Nesting Correctly Example select oorder . oid as ”@id” , oorder . cname as ” customer ” , ( select oorder line . line no as ” @lineno ” , oorder line . dsc as ” description ” , oorder line . quantity as ” quantity ” , oorder line . price each as ” price ” from oorder line where oorder . oid = oorder line . oid f o r xml path ( ’ l i n e ’ ) , type ) from oorder f o r xml path ( ’ order ’ ) , root ( ’ orders ’ ) Example (Result (snip)) <orders> <order id=” 11 ”> <customer>Anna</ customer> <l i n e lineno=” 1 ”> <description>Cola</ description> <quantity>1</ quantity> <price>10.00</ price> </ l i n e> Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 65 / 69
Summary: Auto Overview More complicated than raw and auto mode Nesting “correctly” by subqueries Cast to XML type otherwise a string! Relies on naming conventions for attributes versus elements Options similar as for the raw and auto modes Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 66 / 69
Summary Summary XPath follows the standard XQuery follows the standard XML support on SQL Server far from standard! Not Looked At (RTFM :-)) Indexing XML columns Updates Querying XML from C# SQLXML (Microsoft specific) Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 68 / 69
Additional Information Web Sites Introduction to XQuery in SQL Server 2005 Good overview, cannot find a newer msdn.microsoft.com/en-us/library/ms345122(v=sql.90).aspx XML Options in Microsoft SQL Server 2005 More on the XML data type, cannot find a newer msdn.microsoft.com/en-us/library/ms345110%28v=sql.90%29. aspx SQLXML 4.0 Programming Concepts Good tutorial Must be installed separately on SQL Server 2008 and above msdn.microsoft.com/en-us/library/ms171779.aspx FOR XML (SQL Server) SQL Server 2012 http://msdn.microsoft.com/en-us/library/ms178107.aspx Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 69 / 69

More Related Content

PDF
SQL/XML on Oracle
bytorp42
 
PDF
Querying XML: XPath and XQuery
byKatrien Verbert
 
PDF
Introduction to XPath
bytorp42
 
KEY
XQuery - a technical overview
byLoren Cahlander
 
PPTX
Xml query language and navigation
byRaghu nath
 
PPT
Xpath presentation
byAlfonso Gabriel López Ceballos
 
PDF
Xpath1
byDr.Saranya K.G
 
PDF
XSLT and XPath - without the pain!
byBertrand Delacretaz
 
SQL/XML on Oracle
bytorp42
 
Querying XML: XPath and XQuery
byKatrien Verbert
 
Introduction to XPath
bytorp42
 
XQuery - a technical overview
byLoren Cahlander
 
Xml query language and navigation
byRaghu nath
 
Xpath presentation
byAlfonso Gabriel López Ceballos
 
Xpath1
byDr.Saranya K.G
 
XSLT and XPath - without the pain!
byBertrand Delacretaz
 

What's hot

PPTX
Introductionto xslt
byKumar
 
PPT
XML and XPath details
byDSK Chakravarthy
 
PPT
Xpath
byManav Prasad
 
PPTX
X FILES
bySaraswathiRamalingam
 
PPT
Session 4
byLại Đức Chung
 
PPTX
XPATH
bySun Technlogies
 
PPTX
XPath
byRaji Ghawi
 
PPSX
Collections - Sorting, Comparing Basics
byHitesh-Java
 
PPT
10. Introduction to Datastructure
byNilesh Dalvi
 
PPTX
XSLT presentation
byMiguel Angel Teheran Garcia
 
PPT
XML/XSLT
bythinkahead.net
 
PPTX
transforming xml using xsl and xslt
byHemant Suthar
 
PDF
Day2 xslt x_path_xquery
byXAVIERCONSULTANTS
 
PPT
Xpath
byXAVIERCONSULTANTS
 
PPTX
Xslt tutorial
byBijoy Kureekkal
 
PPTX
XPath Introduction
byStuart Myles
 
PDF
XPath XSLT Workshop - Concept Listing
byIndrajeet Verma
 
PDF
SPARQL Query Containment with ShEx Constraints
byAbdullah Abbas
 
PPT
Wordpress (class,property,visibility,const,destr,inheritence,mysql etc)
byRathod Shukar
 
PPT
Core Java Concepts
bymdfkhan625
 
Introductionto xslt
byKumar
 
XML and XPath details
byDSK Chakravarthy
 
Xpath
byManav Prasad
 
X FILES
bySaraswathiRamalingam
 
Session 4
byLại Đức Chung
 
XPATH
bySun Technlogies
 
XPath
byRaji Ghawi
 
Collections - Sorting, Comparing Basics
byHitesh-Java
 
10. Introduction to Datastructure
byNilesh Dalvi
 
XSLT presentation
byMiguel Angel Teheran Garcia
 
XML/XSLT
bythinkahead.net
 
transforming xml using xsl and xslt
byHemant Suthar
 
Day2 xslt x_path_xquery
byXAVIERCONSULTANTS
 
Xpath
byXAVIERCONSULTANTS
 
Xslt tutorial
byBijoy Kureekkal
 
XPath Introduction
byStuart Myles
 
XPath XSLT Workshop - Concept Listing
byIndrajeet Verma
 
SPARQL Query Containment with ShEx Constraints
byAbdullah Abbas
 
Wordpress (class,property,visibility,const,destr,inheritence,mysql etc)
byRathod Shukar
 
Core Java Concepts
bymdfkhan625
 

Viewers also liked

PDF
D drops
byZoran Stojcevski
 
PPTX
Sql Server execution plans
byFlorin Cardasim
 
PPT
7a advanced tsql
byNauman R
 
PPTX
SQL Functions
byammarbrohi
 
PPT
Sql operators & functions 3
byDr. C.V. Suresh Babu
 
PPTX
Sql query analyzer & maintenance
bynspyrenet
 
PDF
SQL Server - Querying and Managing XML Data
byMarek Maśko
 
PDF
Stored-Procedures-Presentation
byChuck Walker
 
PPT
XML Databases
byJussi Pohjolainen
 
PDF
CSS3 and Responsive Web Design - Web Technologies (1019888BNR)
byBeat Signer
 
PDF
Course Review - Lecture 12 - Next Generation User Interfaces (4018166FNR)
byBeat Signer
 
PDF
T-SQL Data Types (Quick Overview)
byNaji El Kotob
 
PPTX
Getting Started with HTML5 in Tech Com (STC 2012)
byPeter Lubbers
 
PDF
Introduction to XML and Databases
bytorp42
 
PDF
Sql hints
bymaclean liu
 
PDF
Structured Query Language (SQL) - Lecture 5 - Introduction to Databases (1007...
byBeat Signer
 
D drops
byZoran Stojcevski
 
Sql Server execution plans
byFlorin Cardasim
 
7a advanced tsql
byNauman R
 
SQL Functions
byammarbrohi
 
Sql operators & functions 3
byDr. C.V. Suresh Babu
 
Sql query analyzer & maintenance
bynspyrenet
 
SQL Server - Querying and Managing XML Data
byMarek Maśko
 
Stored-Procedures-Presentation
byChuck Walker
 
XML Databases
byJussi Pohjolainen
 
CSS3 and Responsive Web Design - Web Technologies (1019888BNR)
byBeat Signer
 
Course Review - Lecture 12 - Next Generation User Interfaces (4018166FNR)
byBeat Signer
 
T-SQL Data Types (Quick Overview)
byNaji El Kotob
 
Getting Started with HTML5 in Tech Com (STC 2012)
byPeter Lubbers
 
Introduction to XML and Databases
bytorp42
 
Sql hints
bymaclean liu
 
Structured Query Language (SQL) - Lecture 5 - Introduction to Databases (1007...
byBeat Signer
 

Similar to XML on SQL Server

PDF
04-Intro-SQL database management systems
byArmanHarun2221777642
 
PDF
7. Nested Subqueries.pdf
bySunita Milind Dol
 
PPT
SQL.ppt
byRickyLidar
 
PPTX
SQL data types: INT, VARCHAR, CHAR,.pptx
bydharaneshgopal
 
PPT
04 Introduction to SQ(Structural Query Language)L.ppt
byShishirOyshi20143346
 
PPT
DB2 Native XML
byAmol Pujari
 
PDF
PostgreSQL and XML
byPeter Eisentraut
 
PPT
XML Data Types in the Real World
bySteve Hughes
 
PPT
UNIT 2 Structured query language commands
byBhakti Pawar
 
PPT
Sql2005 Xml
byjason hu 金良胡
 
PPTX
Views in RDBMS for students Engineering.pptx
byemilinshyni2020
 
PDF
1. Associate Data Analyst in SQL in data camp
byKhushwantArya3
 
PPTX
SQLPASS AD501-M XQuery MRys
byMichael Rys
 
PPTX
Unit-III_JQuery.pptx engineering subject for third year students
byMARasheed3
 
PPT
XMLLec1 (1xML lecturefsfsdfsdfdsfdsfsdfsdfdsf
byKamrankhan925215
 
PPT
XMLLec1.pptsfsfsafasfasdfasfdsadfdsfdf dfdsfds
byKamrankhan925215
 
PPT
XML stands for EXtensible Markup Language
byNetajiGandi1
 
PPT
Database queries
bylaiba29012
 
PPTX
XML DATABASES in the Master of Engineering
bypoonkodiraja2806
 
PPTX
unit 1 adbms _3.pptxvhjvjhvjhvjhvjjvjvjvjvjv
byzmulani8
 
04-Intro-SQL database management systems
byArmanHarun2221777642
 
7. Nested Subqueries.pdf
bySunita Milind Dol
 
SQL.ppt
byRickyLidar
 
SQL data types: INT, VARCHAR, CHAR,.pptx
bydharaneshgopal
 
04 Introduction to SQ(Structural Query Language)L.ppt
byShishirOyshi20143346
 
DB2 Native XML
byAmol Pujari
 
PostgreSQL and XML
byPeter Eisentraut
 
XML Data Types in the Real World
bySteve Hughes
 
UNIT 2 Structured query language commands
byBhakti Pawar
 
Sql2005 Xml
byjason hu 金良胡
 
Views in RDBMS for students Engineering.pptx
byemilinshyni2020
 
1. Associate Data Analyst in SQL in data camp
byKhushwantArya3
 
SQLPASS AD501-M XQuery MRys
byMichael Rys
 
Unit-III_JQuery.pptx engineering subject for third year students
byMARasheed3
 
XMLLec1 (1xML lecturefsfsdfsdfdsfdsfsdfsdfdsf
byKamrankhan925215
 
XMLLec1.pptsfsfsafasfasdfasfdsadfdsfdf dfdsfds
byKamrankhan925215
 
XML stands for EXtensible Markup Language
byNetajiGandi1
 
Database queries
bylaiba29012
 
XML DATABASES in the Master of Engineering
bypoonkodiraja2806
 
unit 1 adbms _3.pptxvhjvjhvjhvjhvjjvjvjvjvjv
byzmulani8
 

More from torp42

PDF
The DE-9IM Matrix in Details using ST_Relate: In Picture and SQL
bytorp42
 
PDF
Spatial Indexing
bytorp42
 
PDF
Entity-Relationship Diagrams ERD
bytorp42
 
PDF
Introduction to DTD
bytorp42
 
PDF
Temporal Databases: Data Models
bytorp42
 
PDF
Temporal Databases: Modifications
bytorp42
 
PDF
Temporal Databases: Queries
bytorp42
 
The DE-9IM Matrix in Details using ST_Relate: In Picture and SQL
bytorp42
 
Spatial Indexing
bytorp42
 
Entity-Relationship Diagrams ERD
bytorp42
 
Introduction to DTD
bytorp42
 
Temporal Databases: Data Models
bytorp42
 
Temporal Databases: Modifications
bytorp42
 
Temporal Databases: Queries
bytorp42
 

Recently uploaded

PPTX
Cloud Metrics: The Cost-Value Equation (All Things Open 2025)
byEric D. Schabell
 
PDF
Demystifying LLMs- How to Use GenAI Effectively.pdf
byAiko Klostermann
 
PDF
Manage VMs on Kubernetes - Introduction to KubeVirt - Meetup MUC 10.2025
byTobias Schneck
 
PPTX
This Could Have Been a Slack Message: Diagnosing and Treating Bad Meetings
byMike Clement
 
PDF
NeuroXR: Current Research and Opportunities
byMark Billinghurst
 
PDF
How computers tell who they are using TPM2.pdf
byMindtrek
 
PDF
Unleash the Power of Salesforce Winter ’26 Release.pdf
byyoyoloftis
 
PDF
Session 2 - Specialized AI Associate Series: Orchestrator Deep-Dive and UiPa...
byDianaGray10
 
PDF
Web Mapping 101: Creating Dynamic Web Maps with Geospatial Data
bySafe Software
 
PPTX
Behind the Scenes at Netflix: Distributed Systems & NoSQL Architecture.
byayesha butalia
 
PPTX
CLOUD STUDY JAM 2025 GOOGLE DEVELOPER GROUPS ON CAMPUS NSEC
byasutoshkumar560
 
PDF
The Role of Human Experiences (HX) in GenAI Adoption
byDr. Tathagat Varma
 
PDF
Atlantix is an AI-first venture studio, building companies with AI at the core
byadmin625551
 
PDF
Phishing for Answers: A Tech Filler Quiz.pdf
byConquiztadors- the Quiz Society of Sri Venkateswara College
 
PDF
Unit 3 DS - Distributed Data Storage and Retrieval - PowerPoint Content.pdf
byayesha butalia
 
PDF
AGV PROTOCOL BRAND KIT COMPLETE VIEW.pdf
byfagbolade
 
PPTX
TechVerse Kent (Combining Tech, AI, Innovation and Creativity)
byKritarthDevli
 
PDF
How to get started with Agentic Automation
byUiPathCommunity
 
PDF
Session 3 - Specialized AI Associate Series: AI Powered Automation through sp...
byDianaGray10
 
PDF
Meta and Apple close to settling EU cases.pdf
byLUMINATIVE MEDIA/PROJECT COUNSEL MEDIA GROUP
 
Cloud Metrics: The Cost-Value Equation (All Things Open 2025)
byEric D. Schabell
 
Demystifying LLMs- How to Use GenAI Effectively.pdf
byAiko Klostermann
 
Manage VMs on Kubernetes - Introduction to KubeVirt - Meetup MUC 10.2025
byTobias Schneck
 
This Could Have Been a Slack Message: Diagnosing and Treating Bad Meetings
byMike Clement
 
NeuroXR: Current Research and Opportunities
byMark Billinghurst
 
How computers tell who they are using TPM2.pdf
byMindtrek
 
Unleash the Power of Salesforce Winter ’26 Release.pdf
byyoyoloftis
 
Session 2 - Specialized AI Associate Series: Orchestrator Deep-Dive and UiPa...
byDianaGray10
 
Web Mapping 101: Creating Dynamic Web Maps with Geospatial Data
bySafe Software
 
Behind the Scenes at Netflix: Distributed Systems & NoSQL Architecture.
byayesha butalia
 
CLOUD STUDY JAM 2025 GOOGLE DEVELOPER GROUPS ON CAMPUS NSEC
byasutoshkumar560
 
The Role of Human Experiences (HX) in GenAI Adoption
byDr. Tathagat Varma
 
Atlantix is an AI-first venture studio, building companies with AI at the core
byadmin625551
 
Phishing for Answers: A Tech Filler Quiz.pdf
byConquiztadors- the Quiz Society of Sri Venkateswara College
 
Unit 3 DS - Distributed Data Storage and Retrieval - PowerPoint Content.pdf
byayesha butalia
 
AGV PROTOCOL BRAND KIT COMPLETE VIEW.pdf
byfagbolade
 
TechVerse Kent (Combining Tech, AI, Innovation and Creativity)
byKritarthDevli
 
How to get started with Agentic Automation
byUiPathCommunity
 
Session 3 - Specialized AI Associate Series: AI Powered Automation through sp...
byDianaGray10
 
Meta and Apple close to settling EU cases.pdf
byLUMINATIVE MEDIA/PROJECT COUNSEL MEDIA GROUP
 

XML on SQL Server

  • 1.
    XML on SQLServer Kristian Torp Department of Computer Science Aalborg University people.cs.aau.dk/˜torp torp@cs.aau.dk November 5, 2015 daisy.aau.dk Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 1 / 69
  • 2.
    Outline 1 Create andInsert 2 XML Queries Saying Hello exist() query() value() nodes() 3 Generating XML from Select Statements Raw Auto Path 4 Summary Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 2 / 69
  • 3.
    Learning Goals Goals Overview ofXML support on SQL Server Storing XML directly SQL Server Querying XML on SQL Server Standard Concepts are (fairly) general Code is DBMS specific DBMS Versions Tested SQL Server 2008 SQL Server 2012 Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 3 / 69
  • 4.
    Create the RelationalSchema Example (Create the Table) create table course ( id i n t primary key , dsc xml not n u l l ) Note A create table statement as we know it The XML data type named xml A mixture of “old” data types int and new data types xml The not-null constraint, just because we love them! Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 5 / 69
  • 5.
    Insert Data Example (Inserta Row) i n s e r t i n t o course values (1 , ’<courses> <course id =”22” ects =”5” > <name>XML</name> <description >XPath and XQuery f o r querying XML documents </ description > </course> <course id =”11” ects =”5” > <name>DB</name> <description >Querying a r e l a t i o n a l database </ description > </course> </courses> ’ ) Note A plain insert statement The XML data type coersed from the text string Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 6 / 69
  • 6.
    Insert Additional Row Example(Insert another Row) i n s e r t i n t o course values (2 , ’<courses> <course id =”44” ects =”10”> <name>Imperative Programming </name> <description >C and Pointers </ description > </course> <course id =”55” ects =”5” > <name>OOP</name> <description >Working with classes and objects </ description > </course> </courses> ’ ) Note Nothing new conceptually, simply an insert statement We now have a small database! Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 7 / 69
  • 7.
    Database Content Example id dsc 1 <courses> <courseid=” 22 ” ects=” 5 ”> <name>XML</name> <description>XPath . . . </ description> </ course> <course id=” 11 ” ects=” 5 ”> <name>DB</name> <description>Querying . . . </ description> </ course> </ courses> 2 <courses> <course id=” 44 ” ects=” 10 ”> <name>Imperative Programming</name> <description>C . . . </ description> </ course> <course id=” 55 ” ects=” 5 ”> <name>OOP</name> <description>Working . . . </ description> </ course> </ courses> Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 8 / 69
  • 8.
    Not Well-formed XML Example(Insert a Row) i n s e r t i n t o course values (3 , ’<course id =”77” ects =”10”> <name>Object−Oriented Analysis and Design </name> <description >Modeling with classes and objects </ description > </course> <course id =”88” ects =”5” > <name>Software Architecture </name> <description >The big picture </ description > </course> ’ ) Note Has no root element This works fine! Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 9 / 69
  • 9.
    Not Well-formed XML,cont. Example (End tag is missing) i n s e r t i n t o course values (4 , ’<course id =”99” ects =”10”> <name>Operating Systems </name> <description >Linux , Windows , and so on</ description > ’ ) Note XML parsing: line 3, character 61, unexpected end of input Example (Fix the missing end tag) i n s e r t i n t o course values (4 , ’<course id =”99” ects =”10”> <name>Operating Systems </name> <description >Linux , Windows , and so on</ description > </course> ’ ) Note Works! Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 10 / 69
  • 10.
    Not Well-formed XML,cont. Example (Nesting Wrong) i n s e r t i n t o course values (5 , ’<course id =”13” ects =”5” > <name><more>Compiler Construction </name></more> <description >Lex and Yacc</ description > </course> ’ ) Note XML parsing: line 2, character 41, end tag does not match start tag Example (“Weird” Cases) i n s e r t i n t o course values (6 , ’ ’ ) i n s e r t i n t o course values (7 , ’NULL ’ ) Note Works! Example (Does this work?) i n s e r t i n t o course values (8 ,NULL) Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 11 / 69
  • 11.
    Summary: Create andInsert Main Points Create table has new XML data type Very similar to the SQL/XML XML data type Insert statement works as expected XML documents, without root possible, tags must be matching Delete works as expected Not shown here There is special support for updating XML columns Updating only a part of the XML document in a cell Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 12 / 69
  • 12.
    Recall the DatabaseContent Example id dsc 1 <courses> <course id=” 22 ” ects=” 5 ”> <name>XML</name> <description>XPath . . . </ description> </ course> <course id=” 11 ” ects=” 5 ”> <name>DB</name> <description>Querying . . . </ description> </ course> </ courses> 2 <courses> <course id=” 44 ” ects=” 10 ”> <name>Imperative Programming</name> <description>C . . . </ description> </ course> <course id=” 55 ” ects=” 5 ”> <name>OOP</name> <description>Working . . . </ description> </ course> </ courses> Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 15 / 69
  • 13.
    Say Hello Example (Usethe Course Table) select dsc . query ( ” l e t $s := ’ Hello , World ! ’ return $s ” ) from course Note This does not work! And I used a long time to understand why not :-) Example (Try Again (almost the same)) select dsc . query ( ’ l e t $s := ” Hello , World ! ” return $s ’ ) from course Note Works! ’ and “ are different! dsc is a column on table course Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 16 / 69
  • 14.
    Say Hello, cont Example(Say Hello from XQuery) select dsc . query ( ’ l e t $s := ” Hello , World ! ” return $s ’ ) from course Example (Result) (No column name) Hello, World! Hello, World! Note A hello for each row in the table This is a simple XQuery SQL Server generally follows the XQuery standard well Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 17 / 69
  • 15.
    Say Hello, theT-SQL Way Example (Use a Variable) declare @x xml select @x. query ( ’ l e t $s := ” Hello , World ! ” return $s ’ ) Example (Result) (No column name) null Example (Initialize the Variable) declare @x xml = ’ ’ select @x. query ( ’ l e t $s := ” Hello , World ! ” return $s ’ ) Example (Result) (No column name) Hello, World! Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 18 / 69
  • 16.
    Document IDs withmore than 5 ECTS Courses Example (Use exist() in where ) select id from course where dsc . e x i s t ( ’ / / course [ @ects > 5] ’ ) Note An expression of non-boolean type specified in a context where a condition is expected, near ’)’ Example (Use exist() in select ) select id , dsc . e x i s t ( ’ / / course [ @ects > 5] ’ ) from course Example (Result) id (No column name) 1 0 2 1 Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 20 / 69
  • 17.
    Retrying Document IDswith more than 5 ECTS Courses Example (Use exist() in where ) select id from course where dsc . e x i s t ( ’ / / course [ @ects > 5] ’ ) = 1 Example (Result) id 2 Note exist() returns 0, 1 or null This is a surprise! (when used to other DBMSs) Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 21 / 69
  • 18.
    Recall the DatabaseContent Example id dsc 1 <courses> <course id=” 22 ” ects=” 5 ”> <name>XML</name> <description>XPath . . . </ description> </ course> <course id=” 11 ” ects=” 5 ”> <name>DB</name> <description>Querying . . . </ description> </ course> </ courses> 2 <courses> <course id=” 44 ” ects=” 10 ”> <name>Imperative Programming</name> <description>C . . . </ description> </ course> <course id=” 55 ” ects=” 5 ”> <name>OOP</name> <description>Working . . . </ description> </ course> </ courses> Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 23 / 69
  • 19.
    XPath for findingCourse Names Example (Use XPath) select dsc . query ( ’ / courses / course /name ’ ) as res from course Example (Result) res <name>XML</name> <name>DB</name> <name>Imperative Programming</name> <name>OOP</name> null Note What was expected, it is really XPath! A null value for each row where the XPath has no match Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 24 / 69
  • 20.
    XPath for findingCourse Names in Text Format Example (Use text()) select dsc . query ( ’ / courses / course /name/ t e x t ( ) ’ ) as res from course Example (Result) res XMLDB Imperative ProgrammingOOP Note What was expected! Not really useful in an SQL context Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 25 / 69
  • 21.
    Accessing Attributes Example (Lookat Attributes) select dsc . query ( ’ / courses / course / @ects ’ ) as res from course Note XQuery [course.dsc.query()]: Attribute may not appear outside of an element Unexpected! Example (Look at Attributes) select dsc . query ( ’sum ( / courses / course / @ects ) ’ ) as res from course Example (Result) res 10 15 Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 26 / 69
  • 22.
    More XPath Example (Find5 ECTS Courses) select dsc . query ( ’ / courses / course [ @ects =5]/name/ t e x t ( ) ’ ) as res from course Example (Result) res XMLDB OOP Example (Filtering using where ) select dsc . query ( ’ / / course /name/ t e x t ( ) ’ ) as res from course where id = 1 Example (Result) res XMLDB Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 27 / 69
  • 23.
    Multi-Column XPath Example (Morethan One Column) select dsc . query ( ’ / / course /name ’ ) as name, dsc . query ( ’ / / course / description ’ ) as descr from course Example (Result) name descr <name>XML</name> <name>DB</name> <description>XPath . . . </ description> <description>Querying . . . </ description> <name>Imperative . . . </name> <name>OOP</name> <description>C and Pointers</ description> <description>Working . . . </ description> Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 28 / 69
  • 24.
    Multi-Column XPath withtext() Example (Multiple Columns and text()) select dsc . query ( ’ / / course /name/ t e x t ( ) ’ ) as name, dsc . query ( ’ / / course / description / t e x t ( ) ’ ) as descr from course Example (Result) name descr XMLDB XPath ... Querying ... Imperative Programming OOP C and Pointers Working ... Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 29 / 69
  • 25.
    Full FLWOR Example (FLWORQuery) select dsc . query ( ’ f o r $c in / / course where $c / @ects >= 5 return ( $c /name, $c / description ) ’ ) as res from course Example (Result) res <name>XML</name> <description>XPath and XQuery f o r querying XML documents</ description> <name>DB</name> <description>Querying a r e l a t i o n a l database</ description> <name>Imperative Programming</name> <description>C and Pointers</ description> <name>OOP</name> <description>Working with classes and objects</ description> Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 30 / 69
  • 26.
    Recall the DatabaseContent Example id dsc 1 <courses> <course id=” 22 ” ects=” 5 ”> <name>XML</name> <description>XPath . . . </ description> </ course> <course id=” 11 ” ects=” 5 ”> <name>DB</name> <description>Querying . . . </ description> </ course> </ courses> 2 <courses> <course id=” 44 ” ects=” 10 ”> <name>Imperative Programming</name> <description>C . . . </ description> </ course> <course id=” 55 ” ects=” 5 ”> <name>OOP</name> <description>Working . . . </ description> </ course> </ courses> Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 32 / 69
  • 27.
    Get Course Namesin a Relational Fashion Example (Using the value() Function) select dsc . value ( ’ / / course /name ’ , ’ nvarchar (30) ’ ) from course Note XQuery [course.dsc.value()]: ’value()’ requires a singleton (or empty sequence), found operand of type ’xdt:untypedAtomic *’ Example (Singleton’ing) select dsc . value ( ’ ( / / course /name ) [ 1 ] ’ , ’ nvarchar (30) ’ ) from course Example (Result) (No column name) XML Imperative Programming Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 33 / 69
  • 28.
    Retrying Get CourseNames in a Relational Fashion Example (First) select dsc . value ( ’ ( / / course [ 1 ] / name ) [ 1 ] ’ , ’ nvarchar (30) ’ ) from course Example (Result) (No column name) XML Imperative Programming Example (Last) select dsc . value ( ’ ( / / course /name ) [ l a s t ( ) ] ’ , ’ nvarchar (30) ’ ) from course Example (Result) (No column name) DB OOP Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 34 / 69
  • 29.
    Value and WrongSQL Data Type Example (First) select dsc . value ( ’ ( / / course /name ) [ 1 ] ’ , ’ nvarchar (30) ’ ) from course Example (Result) (No column name) XML Imperative Programming Example (Last) select dsc . value ( ’ ( / / course /name ) [ 1 ] ’ , ’ nvarchar (3) ’ ) from course Example (Result) (No column name) XML Imp Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 35 / 69
  • 30.
    value() and WrongSQL Data Type, cont. Example (String as an Integer) select dsc . value ( ’ ( / / course /name ) [ 1 ] ’ , ’ i n t ’ ) from course Note Conversion failed when converting the nvarchar value ’XML’ to data type int. Example (Integer Attribute) select dsc . value ( ’ ( / / course / @ects ) [ 1 ] ’ , ’ i n t ’ ) from course Example (Result) (No column name) 5 10 Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 36 / 69
  • 31.
    Flattening using value() Example(String as an Integer) select id , dsc . value ( ’ ( / / course / @ects ) [ 1 ] ’ , ’ i n t ’ ) as ects from course where dsc . e x i s t ( ’ / / course [ @ects > 5] ’ ) = 1 Example (Result) id ects 2 10 Note Using value() and exist() functions in same query Still too few rows Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 37 / 69
  • 32.
    Flattening using value(),cont Example (String as an Integer) select dsc . value ( ’ ( / / course / @id ) [ l a s t ( ) ] ’ , ’ i n t ’ ) as cid , dsc . value ( ’ ( / / course /name ) [ l a s t ( ) ] ’ , ’ nvarchar (30) ’ ) as cname , dsc . value ( ’ ( / / course / description ) [ l a s t ( ) ] ’ , ’ nvarchar (50) ’ ) as dsc . value ( ’ ( / / course / @ects ) [ l a s t ( ) ] ’ , ’ i n t ’ ) as ects from course Example (Result) cid cname dsc ects 11 DB Querying... 5 55 OOP Working... 5 Note Pure relational world result Result only has two rows (should have four) value() cannot be used must use nodes() Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 38 / 69
  • 33.
    Flattening using nodes() Example(Flatten XML) select f l a t . course . value ( ’ ( . / @id ) [ 1 ] ’ , ’ i n t ’ ) as cid , f l a t . course . value ( ’ ( . / name ) [ 1 ] ’ , ’ varchar (50) ’ ) as cname , f l a t . course . value ( ’ ( . / description ) [ 1 ] ’ , ’ varchar (50) ’ ) as dsc , f l a t . course . value ( ’ ( . / @ects ) [ 1 ] ’ , ’ i n t ’ ) as ects from course cross apply dsc . nodes ( ’ / / course ’ ) f l a t ( course ) order by cid Example (Result) cid cname dsc ects 11 DB Querying... 5 22 XML XPath... 5 44 Imp. C and ... 10 55 OOP Working... 5 Note Relational view on XML data!!! Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 40 / 69
  • 34.
    Hide XML Example (Createa View on XML Data) create view coursexml flat as select f l a t . course . value ( ’ ( . / @id ) [ 1 ] ’ , ’ i n t ’ ) as cid , f l a t . course . value ( ’ ( . / name ) [ 1 ] ’ , ’ varchar (50) ’ ) as cname , f l a t . course . value ( ’ ( . / description ) [ 1 ] ’ , ’ varchar (50) ’ ) as dsc , f l a t . course . value ( ’ ( . / @ects ) [ 1 ] ’ , ’ i n t ’ ) as ects from course cross apply dsc . nodes ( ’ / / course ’ ) f l a t ( course ) Example (Query the View) select ∗ from coursexml flat where ects = 5 and cid > 20 order by cid Example (Result) cid cname dsc ects 22 XML XPath... 5 55 OOP Working... 5 Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 41 / 69
  • 35.
    Summary: Queries Functions Function Descrition exist()Check for existence query() XPath and XQuery value() Extract single value nodes() For flatting nodes modify() For (fine grained) updates of XML Note SQL Server only SQL/XML via programming languages Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 42 / 69
  • 36.
    Master-Detail Example I Order id customername amount OrderLine lineno price per unit quan. desc. has Assumptions An order cannot exist without order lines Amount is the sum of the amount of order lines for a give order Must be larger than zero Derived from order lines but stored because often queried Order line is a weak entity Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 44 / 69
  • 37.
    Master-Detail Example II Example createtable oorder ( oid i n t primary key , cname varchar (30) not n u l l ) Example create table oorder line ( oid i n t not null , line no i n t not n u l l check ( line no > 0) , dsc varchar (50) not null , quantity i n t not n u l l check ( quantity > 0) , price each numeric (6 ,2) not n u l l check ( price each > 0.0) , c o n s t r a i n t ol pk primary key ( oid , line no ) , c o n s t r a i n t o l o f k foreign key ( oid ) references oorder ( oid ) ) Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 45 / 69
  • 38.
    Get XML Out Example select∗ from oorder f o r xml raw Example (Result) <row oid=” 11 ” cname=”Anna” /> <row oid=” 22 ” cname=” Benny ” /> <row oid=” 33 ” cname=” Curt ” /> Note A select statement The for xml raw is SQL Server specific Pretty ugly format ;-) Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 47 / 69
  • 39.
    Get Elements Out Example select∗ from oorder f o r xml raw , elements Example (Result) <row> <oid>11</ oid> <cname>Anna</ cname> </ row> <row> <oid>22</ oid> <cname>Benny</ cname> </ row> <row> <oid>33</ oid> <cname>Curt</ cname> </ row> Note Much nicer Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 48 / 69
  • 40.
    Rename Element Names Example selectoid as ” order −id ” , cname as ” customer−name” from oorder f o r xml raw , elements Example (Result) <row> <order −id>11</ order −id> <customer−name>Anna</ customer−name> </ row> <row> <order −id>22</ order −id> <customer−name>Benny</ customer−name> </ row> snip Note A select statement The for xml raw is SQL Server specific Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 49 / 69
  • 41.
    Rename Enclosed Element Example selectoid as ” order −id ” , cname as ” customer−name” from oorder f o r xml raw ( ’ customer ’ ) , elements Example (Result) <customer> <order −id>11</ order −id> <customer−name>Anna</ customer−name> </ customer> <customer> <order −id>33</ order −id> <customer−name>Curt</ customer−name> </ customer> Note All columns are elements Missing a root element Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 50 / 69
  • 42.
    Adding a RootElement Example select oid as ” order −id ” , cname as ” customer−name” from oorder f o r xml raw ( ’ customer ’ ) , elements , root ( ’ customers ’ ) Example (Result) <customers> <customer> <order −id>11</ order −id> <customer−name>Anna</ customer−name> </ customer> <customer> <order −id>33</ order −id> <customer−name>Curt</ customer−name> </ customer> </ customers> Note Now nice XML document Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 51 / 69
  • 43.
    Joining Tables Example select ∗ fromoorder , oorder line where oorder . oid = oorder line . oid f o r xml raw , elements Example (Result (snip)) <row> <oid>11</ oid> <cname>Anna</ cname> <oid>11</ oid> <line no>1</ line no> <dsc>Cola</ dsc> <quantity>1</ quantity> <price each>10.00</ price each> </ row> Note No nesting Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 52 / 69
  • 44.
    Summary: Raw Overview Fast toget XML fragments out Nice simple extension to select statement Default output in attributes Either all attributes or all elements Mixture of attributes and elements hard Is a bit “raw” Additional options, type, xmldata, xmlschema Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 53 / 69
  • 45.
    Get XML Out Example select∗ from oorder f o r xml auto Example (Result) <row oid=” 11 ” cname=”Anna” /> <row oid=” 22 ” cname=” Benny ” /> <row oid=” 33 ” cname=” Curt ” /> Note At first glanse looks like for xml raw But wait, there is more ... Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 55 / 69
  • 46.
    The Power ofAuto Example select ∗ from oorder , oorder line where oorder . oid = oorder line . oid and oid < 30 f o r xml auto Example (Result) <oorder oid=” 11 ” cname=”Anna”> <o order line oid=” 11 ” line no =” 1 ” dsc=” Cola ” quantity =” 1 ” price each=” 10.00 ” <o order line oid=” 11 ” line no =” 2 ” dsc=” Chips ” quantity =” 1 ” price each=” 15.00 </ oorder> <oorder oid=” 22 ” cname=” Benny ”> <o order line oid=” 22 ” line no =” 1 ” dsc=” Cola ” quantity =” 1 ” price each=” 10.00 ” <o order line oid=” 22 ” line no =” 2 ” dsc=” Burger ” quantity =” 2 ” price each=” 20.0 <o order line oid=” 22 ” line no =” 3 ” dsc=” Chips ” quantity =” 1 ” price each=” 15.00 </ oorder> Note Does automatic nesting at “natural” levels Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 56 / 69
  • 47.
    Rename Elements Example select oorder. oid as ” order ” , oorder . cname as ” customer ” , oorder line . dsc as ” description ” , oorder line . quantity , oorder line . price each as ” price ” from oorder , oorder line where oorder . oid = oorder line . oid f o r xml auto Example (Result (snip)) <oorder order=” 11 ” customer=”Anna”> <o order line description =” Cola ” quantity =” 1 ” price=” 10.00 ” /> <o order line description =” Chips ” quantity =” 1 ” price=” 15.00 ” /> </ oorder> <oorder order=” 22 ” customer=” Benny ”> <o order line description =” Cola ” quantity =” 1 ” price=” 10.00 ” /> <o order line description =” Burger ” quantity =” 2 ” price=” 20.00 ” /> <o order line description =” Chips ” quantity =” 1 ” price=” 15.00 ” /> </ oorder> Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 57 / 69
  • 48.
    Add Root Element+ Rename Example select ” order ” . oid as ” id ” , ” order ” . cname as ” customer ” , ” l i n e ” . dsc as ” description ” , ” l i n e ” . quantity , ” l i n e ” . price each as ” price ” from oorder as ” order ” , oorder line as ” l i n e ” where ” order ” . oid = ” l i n e ” . oid f o r xml auto , root ( ’ orders ’ ) Example (Result (snip)) <orders> <order id=” 11 ” customer=”Anna”> <l i n e description =” Cola ” quantity =” 1 ” price=” 10.00 ” /> <l i n e description =” Chips ” quantity =” 1 ” price=” 15.00 ” /> </ order> <order id=” 22 ” customer=” Benny ”> <l i n e description =” Cola ” quantity =” 1 ” price=” 10.00 ” /> <l i n e description =” Burger ” quantity =” 2 ” price=” 20.00 ” /> <l i n e description =” Chips ” quantity =” 1 ” price=” 15.00 ” /> </ order> </ orders> Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 58 / 69
  • 49.
    Make it AllElements Example select ” order ” . oid as ” id ” , ” order ” . cname as ” customer ” , ” l i n e ” . dsc as ” description ” , ” l i n e ” . quantity , ” l i n e ” . price each as ” price ” from oorder as ” order ” , oorder line as ” l i n e ” where ” order ” . oid = ” l i n e ” . oid f o r xml auto , elements , root ( ’ orders ’ ) Example (Result (snip)) <orders> <order> <id>11</ id> <customer>Anna</ customer> <l i n e> <description>Cola</ description> <quantity>1</ quantity> <price>10.00</ price> </ l i n e> snip Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 59 / 69
  • 50.
    Summary: Auto Overview Fast toget XML out even for joins Row tag can only be renamed by table alias Okay at getting nesting correct Either attributes or elements Options similar as for the xml raw output Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 60 / 69
  • 51.
    Get XML Out Example selectoorder . oid as ” id ” , oorder . cname as ” customer ” , oorder line . line no as ” lineno ” , oorder line . dsc as ” description ” , oorder line . quantity , oorder line . price each as ” price ” from oorder , oorder line where oorder . oid = oorder line . oid f o r xml path Example (Result (snip)) <row> <id>11</ id> <customer>Anna</ customer> <lineno>1</ lineno> <description>Cola</ description> <quantity>1</ quantity> <price>10.00</ price> </ row> Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 62 / 69
  • 52.
    Adding Structure Example select oorder. oid as ” id ” , oorder . cname as ” customer ” , oorder line . line no as ” lineno ” , oorder line . dsc as ” description ” , oorder line . quantity , oorder line . price each as ” price ” from oorder , oorder line where oorder . oid = oorder line . oid f o r xml path ( ’ order ’ ) , root ( ’ orders ’ ) Example (Result (snip)) <orders> <order> <id>11</ id> <customer>Anna</ customer> <lineno>1</ lineno> <description>Cola</ description> <quantity>1</ quantity> <price>10.00</ price> </ order> <order> Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 63 / 69
  • 53.
    Adding Attributes andNesting Line Items Example select oorder . oid as ”@id” , oorder . cname as ” customer ” , oorder line . line no as ” l i n e / lineno ” , oorder line . dsc as ” l i n e / description ” , oorder line . quantity as ” l i n e / quantity ” , oorder line . price each as ” l i n e / price ” from oorder , oorder line where oorder . oid = oorder line . oid f o r xml path ( ’ order ’ ) , root ( ’ orders ’ ) Example (Result (snip)) <orders> <order id=” 11 ”> <customer>Anna</ customer> <l i n e> <lineno>1</ lineno> <description>Cola</ description> <quantity>1</ quantity> <price>10.00</ price> </ l i n e> </ order> Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 64 / 69
  • 54.
    Nesting Correctly Example select oorder. oid as ”@id” , oorder . cname as ” customer ” , ( select oorder line . line no as ” @lineno ” , oorder line . dsc as ” description ” , oorder line . quantity as ” quantity ” , oorder line . price each as ” price ” from oorder line where oorder . oid = oorder line . oid f o r xml path ( ’ l i n e ’ ) , type ) from oorder f o r xml path ( ’ order ’ ) , root ( ’ orders ’ ) Example (Result (snip)) <orders> <order id=” 11 ”> <customer>Anna</ customer> <l i n e lineno=” 1 ”> <description>Cola</ description> <quantity>1</ quantity> <price>10.00</ price> </ l i n e> Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 65 / 69
  • 55.
    Summary: Auto Overview More complicatedthan raw and auto mode Nesting “correctly” by subqueries Cast to XML type otherwise a string! Relies on naming conventions for attributes versus elements Options similar as for the raw and auto modes Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 66 / 69
  • 56.
    Summary Summary XPath follows thestandard XQuery follows the standard XML support on SQL Server far from standard! Not Looked At (RTFM :-)) Indexing XML columns Updates Querying XML from C# SQLXML (Microsoft specific) Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 68 / 69
  • 57.
    Additional Information Web Sites Introductionto XQuery in SQL Server 2005 Good overview, cannot find a newer msdn.microsoft.com/en-us/library/ms345122(v=sql.90).aspx XML Options in Microsoft SQL Server 2005 More on the XML data type, cannot find a newer msdn.microsoft.com/en-us/library/ms345110%28v=sql.90%29. aspx SQLXML 4.0 Programming Concepts Good tutorial Must be installed separately on SQL Server 2008 and above msdn.microsoft.com/en-us/library/ms171779.aspx FOR XML (SQL Server) SQL Server 2012 http://msdn.microsoft.com/en-us/library/ms178107.aspx Kristian Torp (Aalborg University) XML on SQL Server November 5, 2015 69 / 69