xml data in ms sql server query and modification l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
XML Data in MS SQL Server Query and Modification PowerPoint Presentation
Download Presentation
XML Data in MS SQL Server Query and Modification

Loading in 2 Seconds...

play fullscreen
1 / 23

XML Data in MS SQL Server Query and Modification - PowerPoint PPT Presentation


  • 251 Views
  • Uploaded on

XML Data in MS SQL Server Query and Modification. Steven Blundy, Duc Duong, Abhishek Mukherji, Bartlett Shappee CS561. Outline. Introduction XML Data Type Structure and storage Schema, validation Methods Publishing FORXML Queries. Approaches for XML integration to DB. Mid-tier

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'XML Data in MS SQL Server Query and Modification' - takara


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
xml data in ms sql server query and modification

XML Data in MS SQL Server Query and Modification

Steven Blundy, Duc Duong, Abhishek Mukherji, Bartlett Shappee

CS561

outline
Outline
  • Introduction
  • XML Data Type
    • Structure and storage
    • Schema, validation
    • Methods
  • Publishing
    • FORXML Queries
approaches for xml integration to db
Approaches for XML integration to DB
  • Mid-tier
    • Bi-directional XML view
      • Query view using XPath
      • Schema-driven approach for shredding
  • Server-side
    • rowset-to-XML aggregator for XML Publishing - FOR XML
    • Query-driven shredding mechanism- Open XML
xml storage native
XML Storage - Native
  • Checks Well Formedness
    • Validation is optional
  • XML Documents or Fragments
xml storage db
SQL BLOB

Allows for utilization BLOB optimizations

Streaming

Parsing

Compression

Unicode (UTF-16) Strings - UNTYPED

Requires Conversion

XML Schema - TYPED

Encode to match schema

Much more efficient

XML Storage - DB
xml storage the numbers
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 - Schema
  • Storage Optimization
    • Size
    • Processing
  • Uses the XML Infoset
  • Defined in an XMLSchemaCollection
validation schema
Validation - Schema
  • XML Schema Collection
    • Stores 1+ XML Schemas
      • Identified by Name Space
      • Not the Same as Constraints (No Business Logic)
    • Metadata Entity
    • Certain Type are Format Constrained
      • i.e. Date must use ISO 8601 format
  • Uses the XML Infoset
validation schema collection
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
Validation - How?
  • At Data Entry
    • Validness checked when typed data is inserted
  • Checking Data
    • Applied using Cast
  • Updated Schema
    • Does not Require re-validation
xml dt methods
XML DT Methods
  • All evaluate an XQuery
  • Methods
    • query() - returns XML output
    • exists() - checks if expression results in nodes
    • nodes() - returns XML DT values
    • value() - returns value as SQL DT
    • modify() - modify XML data
query nodes methods
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() & 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
Example: value()

SELECT data.value(‘(/bibliograph/book/[1]/title)[1]’,

‘NVARCHAR(255)’) AS Title

FROM Test

example value w nodes
Example: value() w/ nodes()

SELECT book.value(‘(title)[1]’,

‘NVARCHAR(255)’) AS Title

FROM Test CROSS APPLY

data.nodes(‘/bibliograph/book’) AS R(book)

example exist
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

modify method
modify() method
  • Uses extended XQuery
    • insert, delete, and replace keywords
  • Used in SQL UPDATEs
example modify
Example: modify()

UPDATE docs SET xCol.modify(‘

insert

<section num="2">

<title>Background</title>

</section>

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

example 2 modify
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')

xml publishing
XML Publishing

select CustomerID as "@CustomerID",

City as "address/city",

PostalCode as "address/zip",

ContactName as "contact/name",

Phone as "contact/phone",

from Customers

for xml path('Customer'), root('Doc')

additional papers used
Additional Papers Used
  • XML Support in Microsoft SQL Server 2005
    • Shankar Pal, Mark Fussell, and Irwin Dolobowsk
    • http://msdn2.microsoft.com/en-us/library/ms345117.aspx
  • XML Best Practices for Microsoft SQL Server 2005
    • Shankar Pal, Vishesh Parikh, Vasili Zolotov, Leo Giakoumakis, Michael Rys
    • http://msdn2.microsoft.com/en-us/library/ms345115(d=printer).aspx