Query and Modification - Advanced Database System - Lecture Slides, Slides of Database Management Systems (DBMS)

Download Query and Modification - Advanced Database System - Lecture Slides and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

XML Data in MS SQL Server

Query and Modification

Outline

• Introduction
• XML Data Type
  • Structure and storage
  • Schema, validation
  • Methods
• Publishing
  • FORXML Queries

SQL SERVER 2005 XML Architecture

XML Storage - Native

• Checks Well Formedness
  • Validation is optional
• XML Documents or Fragments

XML Storage - The Numbers

• Advantages of Binary Storage
  • 20 to 30% Size Reduction
  • Faster
• Limitations
  • 2gb of stored binary per instance
  • Hierarchy is limited to 128 Levels

Storage - Schema

• Storage Optimization
  • Size
  • Processing
• Uses the XML Infoset
• Defined in an XMLSchemaCollection

Validation - Schema Collection

CREATE XML SCHEMA COLLECTION myCollection AS '<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://myBooks" elementFormDefault="qualified" targetNamespace="http://myBooks"> <xsd:element name="bookstore" type="bookstoreType" /> <xsd:complexType name="bookstoreType"> <xsd:sequence maxOccurs="unbounded"> <xsd:element name="book" type="bookType" /> </xsd:sequence> </xsd:complexType> <xsd:complexType name="authorName"> <xsd:sequence> <xsd:element name="first-name" type="xsd:string" /> <xsd:element name="last-name" type="xsd:string" /> </xsd:sequence> </xsd:complexType> </xsd:schema>'

Validation - How?

• At Data Entry
  • Validness checked when typed data is inserted
• Checking Data
  • Applied using Cast
• Updated Schema
  • Does not Require re-validation

query() & nodes() methods

• Both take XQuery string
• query() returns list of untyped XML nodes
  • Can be converted to strings
  • For SELECT output
• nodes() returns list of typed XML nodes
  • All XML DT methods available
  • count(*) works
  • No converting to strings

exist() & value() methods

• exist()
  • returns true if XQuery returns any nodes
• value()
  • Takes 2 params: an XQuery & a SQL DT
  • Converts xml value returned by XQuery to specified SQL type

Example: value() w/ nodes()

SELECT book.value(‘(title)[1]’, ‘NVARCHAR(255)’) AS Title FROM Test CROSS APPLY data.nodes(‘/bibliograph/book’) AS R(book)

Title Design Patterns All about XML …

Example: exist()

SELECT book.value(‘(title)[1]’, ‘NVARCHAR(255)’) AS Title FROM Test CROSS APPLY data.nodes(‘/bibliograph/book’) AS R(book) WHERE data.exist(‘/bibliograph/book’) = 1

Title Design Patterns All about XML …

Example: modify()

UPDATE docs SET xCol.modify(‘ insert

after (/doc//section[@num=1])[1]')

Example 2: modify()

UPDATE XmlCatalog SET Document.modify (' declare namespace bk = "http://myBooks"; replace value of (/bk:bookstore/bk:book [@ISBN="1-861003-11- 0"]/bk:price)[1] with 49.99')