SQL Agent Man Series Replication to the Rescue

1
Microsoft is the most influential company
driving the deployment of XML. Source Zona
Enterprise Usage Survey, Application Development
- Q4 1999
recognize the great power that the world will
derive over the next five or ten years from
XML Source Steve Ballmer, Financial Services
Summit 4/2000
2
SQL Agent Man Series What does SQL Server have
to do with XML?

• Trip Ottinger, MCSD
• Lead Architect, EnterSolve, Inc.

3
Evolution of Programming the Web
.NET XML
DNA
Technology
Desktop
Enterprise
Internet
Usability
Innovation
Great User Experience
Business
eBusiness Web Services
Program the Web
4
Web Services
5
Web Services - Defined

• Web Services are the intersection of
  connectivity, software and a business model
• Common, Public end-points to process data
  (platform, language, implementation neutral)
• Ability to program the web with a common
  integration point XML
• Build on current languages, tools, operating
  systems, APIs

6
XML Defined

• XML or Extensible Markup Language is a
  meta-markup language that provides a format for
  describing structured data
• XML is the universal format for structured
  documents and data
• XML provides a standard for more precise
  declarations of content and more meaningful
  search results across loosely coupled
  applications
• What is another word for loosely coupled
  applications?

7
A Quick Vocabulary Tour

• XML Document A Document that Contains XML
  Elements and Attributes
• Demo XML Document Example
• Style Sheet Used to Format XML Data
• XSL - eXtensible Stylesheet Language
• Template A Valid XML document containing SQL
  Statements
• Virtual Root A Reference within Internet
  Information Services (IIS) to a Physical Directory

8
XML Features in SQL Server 2000

• Access SQL Server via HTTP
• You Can Also OLEDB and ADO APIs to Produce XML
• Support for XML-Data Reduced (XDR) schemas
• XDR is a Logical View of the Tables in you
  Database
• Use XPath Queries against XDR Schemas
• Use Xpath to Build XML Documents from XDR
• Retrieve and Write XML Data
• FOR XML Clause
• OPENXML rowset provider
• XPATH Query Language

9
The FOR XML Clause

• Return XML Instead of rowsets with FOR XML
• Execute Directly or from Stored Procedures
• Specify One of Three Nodes
• RAW
• AUTO
• EXPLICIT

10
RAW, AUTO, EXPLICIT

• RAW
• Each Returned Row is a Row Element
• A Flat Document is returned to the Client
• Group By is Supported as well as aggregates
• AUTO
• Each Table is Transformed into an Element
• Columns are Transformed into Attributes
• Column Order Determines Nesting Order
• EXPLICIT
• You Define the Schema in the Query
• Then, SQL Server will translate into XML
• Define VERY FLEXIBLE Hierarchies

11
When XML Is Not Allowed

• Not Allowed in Sub Selects
• Not Allowed in COMPUTE BY
• Not Allowed in GROUP BY and Aggregates with FOR
  XML AUTO
• Not Allowed within a VIEW definition
• IS Allowed When Referencing a View
• Not Allowed with a CURSOR
• Demo - XML Not Allowed.sql

12
HTTP Access to Your SQL Server

• IIS Virtual Directory Management for SQL Server
  Utility
• Define and Register a New Virtual Directory
• ISAPI DLL (sqlisapi.dll) Associates the Virtual
  Directory to SQL Server
• Login, Password, and Access Permissions
• Configure a Connection to a Specific Database
• Use the IIS Server and Virtual Directory in the
  URL
• Demo Configure a Basic Virtual Directory

13
What can you do in the URL?

• Execute Template Objects
• Execute SQL Statements (Not Recommended)
• Execute Xpath Queries (XDR Schema File)
• Demo 2) BerlinOrders.xml

14
Fast Data Access Over the Web

• Specify SQL Queries in the URL
• Demo Specify Template Files in the URL
• Advantages of Templates
• SQL in the URL is Cumbersome
• Browsers have limitations of text amount in URL
• Enhance Security
• Remove the URL Query Processing Service
• Demo Template with Stored Procedures

15
XMLDATA Argument

• XMLDATA Produces XML Data Reduced (XDR) Schema
• XDR Schema is XML
• XDR Schema Describes Your Data
• Clients Read Schema and Decide How to Format
• XDR Schema is Microsoft Proposal to the W3Cs
  Consortium
• Has the W3C Approved XDR Schema as a Standard
  Definition?
• Demo 4) BerlinOrders.xml

16
Formatting XML Data

• Demo Using XSL Style sheets

17
Writing XML Using OPENXML

• Use sp_xml_preparedocument to Create a XML DOM
  Tree
• Pass DOM Tree to OPENXML
• OPENXML is a TSQL Keyword
• Allows Relational Access to XML Data
• Creates a Table like Rowset from an XML Document
• Rowset Exisits In Memory

18
OPENXML Process
XML
Sp_xml_preparedocument
XMLDOM
OPENXML to Shred
SELECT INTO
19
When is XML Appropriate?

• Moving Data Between Organizations
• Moving Data Between Hardware or Software
  Platforms
• Sharing Data without Application Dependencies
• Transferring Data over the Internet
• Store Web Session Data as XML with a GUID in a
  Database

20
Introducing EnterSolves FastTrack

• FastTrack is an XML based architecture for
  building web and Win32 applications.
• Reusable Code
• Template Code
• Better Code Better App.
• Faster Development Times
• Lower Cost of Development

• Leverages XML capabilities of SQL Server 2000.
• COM components written in Visual Basic talk in
  XML.
• Flexible Presentation Services
• HTTP Enabled
• A look behind the scenes at FastTrack.

21
Summary

• XML
• Is integral to the vision for building
  next-generation Web Service based applications
• Is a key technology in Microsofts .NET platform
• Is Data
• Understand that XML is not a panacea for todays
  development challenges
• Microsoft supports XML standards
• Microsoft is integrating XML support into its
  product and technology offerings