XML Inside SQL Server

1
XML Inside SQL Server

• David Lundell, MBA
• MCDBA MCT MCSE MCSD
• Mutually Beneficial Inc
• David_at_MutuallyBeneficial.com
• www.MutuallyBeneficial.com

2
Overview

• Retrieving XML data using FOR XML
• Shredding XML data using OPENXML
• Storing XML
• FLOWR Power

3
FOR XML

• Used to convert Relational data into XML
• AUTO Mode
• RAW Mode
• Can now be element centric
• Explicit Mode
• Path Mode (new to SQL 2005)
• Most of the Power of Explicit without the pain
• Can easily mix attribute and elements
• ROOT Directive
• Type Directive (used to keep an xml column as xml)

4
OPENXML

• Now we can use XML Datatype as source doc for
  sp_prepare_xml_document
• We can also return an xml data type as part of
  the relational data

5
Storing XML

• XML data type upto 2GB
• Untyped XML
• Typed XML validates using a schema
• Can control whether must be Document or can
  accept fragments
• What is more efficient in terms of space?
• What is more efficient in terms of querying?

6
Can load data directly from file or stream

• OPENROWSET(BULK 'C\SQL_BagOTricks\BagOXML\SalesPe
  oples.xml', SINGLE_BLOB)

7
Query and FLOWR (FLWOR)

• Query method allows us to do a query directly off
  of a xml type
• for, let, where, order by, and return (wont let
  us use let)

8
More Information

• SQL BOL
• Bulk Importing and Exporting XML Documents
• MOC 2734 Upgrading Dev Skills to SQL 2005
• MOC 2779 Implementing a Database in SQL 2005