XML in SQL Server 2005

1
XML in SQL Server 2005
2
Overview

• XML is a key part of any modern data environment
• It can be used to transmit data in a platform,
  application neutral form
• It can be used to store document type data that
  is not easily described in normal table
  structures
• SQL Server 2005 has many advanced xml features

3
Relational to XML

• SQL Server provides four methods to output
  relational data as XML
• FOR XML RAW
• FOR XML AUTO
• FOR XML PATH
• FOR XML EXPLICIT

4
For XML Raw

• Xml Raw is the easiest to use. With no other
  arguments it outputs an xml fragment with each
  column represented as an attribute, value pair.
• SELECT LastName, Firstname, HomePhone
• FROM Employees
• FOR XML RAW

5
XML RAW Results

• ltrow LastName"Davolio" Firstname"Nancy"
  HomePhone"[phone]" /gt
• ltrow LastName"Fuller" Firstname"Andrew"
  HomePhone"[phone]" /gt
• ltrow LastName"Leverling" Firstname"Janet"
  HomePhone"[phone]" /gt
• ltrow LastName"Peacock" Firstname"Margaret"
  HomePhone"[phone]" /gt
• ltrow LastName"Buchanan" Firstname"Steven"
  HomePhone"(71) 555-4848" /gt

6
Elements and Root

• To change the output you can use the ELEMENTS
  keyword to have the columns output as elements
  instead of attributes.
• You can also add a root element with the keyword
  ROOT so that the result is a well-formed XML
  file

7
EXAMPLE with ELEMENTS and ROOT

• SELECT LastName, Firstname, HomePhone
• FROM Employees
• FOR XML RAW('Employees'), ELEMENTS,
  ROOT('EmployeeList')

8
Limits of XML RAW

• All columns must be formatted in the same wayall
  attributes or all elements
• Only a one level hierarchy no nested levels

9
For XML Auto

• XML Auto allows you to create nested structures
• SELECT CategoryName,
• ProductName,
• QuantityperUnit,
• unitprice
• FROM categories
• INNER JOIN Products
• ON Categories.CategoryIDProducts.CategoryID
• FOR XML AUTO, ELEMENTS, ROOT('ProductCategories')

10

• ltProductCategoriesgt
• ltcategoriesgt
• ltCategoryNamegtBeverageslt/CategoryNamegt
• ltProductsgt
• ltProductNamegtChailt/ProductNamegt
• ltQuantityperUnitgt10 boxes x 20
  bagslt/QuantityperUnitgt
• ltunitpricegt18.0000lt/unitpricegt
• lt/Productsgt
• ltProductsgt
• ltProductNamegtChanglt/ProductNamegt
• ltQuantityperUnitgt24 - 12 oz
  bottleslt/QuantityperUnitgt
• ltunitpricegt19.0000lt/unitpricegt
• lt/Productsgt
• lt/categoriesgt

11
For XML PATH

• XML Path provides full control over the XML and
  is easier than XML EXPLICIT
• SELECT CategoryName 'Category/CategoryName',
• ProductName 'Category/Product/ProductName',
• QuantityPerUnit 'Category/Product/QuantityPerUnit'
  ,
• UnitPrice 'Category/Product/price/data()'
• FROM categories
• INNER JOIN Products
• ON Categories.CategoryIDProducts.CategoryID
• Order By CategoryName
• FOR XML PATH, ROOT('ProductCategories')

12
For XML Explicit

• Explict provides even more control over the xml
  but is complex to implement

13
Storing XML in the Database

• Xml can be stored in the database as either text
  or
• Starting with SQL Server 2005 as a native XML
  data type

14
As Text

• Advantages to saving as text
• Preserves white space
• Preserves xml declaration
• Easily retrieved as a string and reconverted to
  xml
• Disadvantages
• Cant be searched directly
• Cant be directly updated
• Cant be tested against a schema

15
XML Data Type

• Limit 2Gbs
• Strips xml declaration
• Can be fragment or whole document
• Must be well formed
• Can be validated against a schema store in an XML
  Schema Collection

16
Validating XML (Schema Collections)

• Schema collects are stored in the server and can
  be used to validate columns with xml data types

17
XQuery

• SQL Server fully incorporates XQuery standards
  allowing a user to directly query xml content
  stored in the XML data type columns

18
SqlXml

• SQL XML, consists of a set of tools for
  processing and manipulating SQL server Data
  programmically