SQL Agent Man Series Replication to the Rescue - PowerPoint PPT Presentation

1 / 21
About This Presentation
Title:

SQL Agent Man Series Replication to the Rescue

Description:

... and more meaningful search results across loosely coupled ... Not Allowed in GROUP BY and Aggregates with FOR XML AUTO. Not Allowed within a VIEW definition ... – PowerPoint PPT presentation

Number of Views:39
Avg rating:3.0/5.0
Slides: 22
Provided by: tripot
Category:

less

Transcript and Presenter's Notes

Title: 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
Write a Comment
User Comments (0)
About PowerShow.com