SQL Agent Man Series Replication to the Rescue by uzd19483


									“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
       SQL Agent Man Series

What does SQL Server have to do with
          Trip Ottinger, MCSD
     Lead Architect, EnterSolve, Inc.
  Evolution of Programming the Web

Usability   Enterprise             Internet

                         the Web
                                  Web Services

                                  Web Service

                      Seller                       Seller

                                                            Web Service
Order Full-Fillment                                           Content
                                  Web Service

                      Publisher             Publisher

     Web Service
             Web Services - Defined

   Web Services are the intersection of
    connectivity, software and a business
   Common, Public end-points to process
    data (platform, language, implementation
   Ability to program the web with a common
    integration point – XML
   Build on current languages, tools,
    operating systems, API‟s
               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‟?…
             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
         XML Features in SQL Server 2000

   Access SQL Server via HTTP
   You Can Also OLEDB and ADO API‟s to Produce
   Support for XML-Data Reduced (XDR) schemas
     – XDR is a Logical View of the Tables in you
   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
               The FOR XML Clause

   Return XML Instead of rowsets with FOR

   Execute Directly or from Stored Procedures

   Specify One of Three Nodes
    – RAW
    – AUTO
                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
     – You Define the Schema in the Query
     – Then, SQL Server will translate into XML
     – Define VERY FLEXIBLE Hierarchies
            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
         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
     – Use the IIS Server and Virtual Directory in
       the URL
   Demo – Configure a Basic Virtual Directory
           What can you do in the URL?

   Execute Template Objects

   Execute SQL Statements (Not

   Execute Xpath Queries (XDR Schema File)
    – Demo: 2) BerlinOrders.xml
          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
               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
   XDR Schema is Microsoft Proposal to the
    W3C‟s Consortium
   Has the W3C Approved XDR Schema as a
    Standard Definition?
   Demo – 4) BerlinOrders.xml
              Formatting XML Data

   Demo – Using XSL Style sheets
          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
   Rowset Exisits In Memory
              OPENXML Process

XML   Sp_xml_preparedocument

       OPENXML to Shred

                    SELECT INTO        Table
                                    SQL Server
           When is XML Appropriate?

 Moving  Data Between Organizations
 Moving Data Between Hardware or
  Software Platforms
 Sharing Data without Application
 Transferring Data over the Internet
 Store Web Session Data as XML
  with a GUID in a Database
         Introducing EnterSolve‟s FastTrack

   FastTrack is an XML         Leverages XML
    based architecture for       capabilities of SQL
    building web and             Server 2000.
    Win32 applications.         COM+ components
   Reusable Code                written in Visual Basic
   Template Code                „talk‟ in XML.
   Better Code = Better        Flexible Presentation
    App.                         Services
   Faster Development          HTTP Enabled
    Times                       A look behind the
   Lower Cost of                scenes at FastTrack.

   XML
    – Is integral to the vision for building
       next-generation Web Service based
    – Is a key technology in Microsoft’s .NET
    – Is Data
    – Understand that XML is not a panacea
       for today’s development challenges
   Microsoft supports XML standards
   Microsoft is integrating XML support into
    its product and technology offerings

To top