SQL Server 2000
Download
1 / 72

SQL Server 2000 XML Enhancements - PowerPoint PPT Presentation


  • 305 Views
  • Updated On :

SQL Server 2000 XML Enhancements Peter Ty MCDBA, MCSE, MCP+SB Technology Specialist Microsoft Hong Kong Ltd. [email protected] What You Will See Today. XML Support in Microsoft SQL Server 2000 XML/XSLT fundamentals Available features

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'SQL Server 2000 XML Enhancements' - LionelDale


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Slide1 l.jpg

SQL Server 2000 XML EnhancementsPeter TyMCDBA, MCSE, MCP+SBTechnology SpecialistMicrosoft Hong Kong Ltd. [email protected]


What you will see today l.jpg
What You Will See Today

  • XML Support in Microsoft SQL Server 2000

    • XML/XSLT fundamentals

    • Available features

    • Development techniques used to take advantage of these features

    • SQL Server 2000 integration with other tools through XML


Session prerequisites l.jpg
Session Prerequisites

  • XML Basics

  • Transact-SQL (T-SQL) Language

  • Microsoft Visual Basic Language (Basics)

  • Microsoft Visual Basic Development Environment

  • SQL Server Tools


Agenda l.jpg
Agenda

  • XML Overview

  • XML in SQL Server 2000

  • Database Publishing

  • OpenXML Rowsets

  • SQL Server 2000 XML Solutions

  • BizTalk Server 2000 Integration


Xml overview origin of xml l.jpg
XML OverviewOrigin of XML

  • SGML Originated as a Markup Language for Documents

    • Many other languages derive from it

      • XML, HTML, etc.

  • The Main Goal Is to Unify the Data and Document Transport Format

    • XML is a W3C standard


Xml overview origin of xml6 l.jpg

SGML

XML

HTML

CSS

XSLT

XML Overview Origin of XML

  • XML Originated as a Standard Language for Data Representation


Xml overview xml basics l.jpg
XML OverviewXML Basics

  • XML Structures the Content

    • In HTML: <p> Jan 15, 2000 </p>

    • In XML: <OrderDate> Jan 15, 2000 <OrderDate>

  • XML Does Not Display or Transform Data

    • XML separates data from formatting and transforming

    • HTML and XML are derived from SGML


Xml overview xml syntax l.jpg
XML OverviewXML Syntax

  • XML Is Composed of Tags and Attributes

    • Tags can be nested

      • Representing entities, entity properties, and entity hierarchy

<ROOT>

<Orders OrderID="10643" CustomerID="ALFKI" EmployeeID="6" OrderDate="1997-08-25T00:00:00" RequiredDate="1997-09-22T00:00:00" ShippedDate="1997-09-02T00:00:00" />

</ROOT>


Xml overview node structure of xml documents l.jpg

Document

Elements

} Attributes

Text

...

Text

Text

XML OverviewNode Structure of XML Documents

  • XML Is Parsed into a Tree Structure

    • Nodes of the tree contain the data


Xml overview node structure of xml documents10 l.jpg

Namespaceuri=“…”

root

Comment

customer list

customer

customer

id

id

345

120

name

orders

first

last

order

order

date

date

Jane

Doe

07/16/98

07/23/98

XML OverviewNode Structure of XML Documents

  • Parsing XML into Nodes


Xml overview xml basics11 l.jpg
XML OverviewXML Basics

  • Use XSLT to Display and Transform Data

    • For example, XSLT can tell Internet Explorer how to format each tag and eventually how to transform it

<?xml-stylesheet type="text/xsl" href="t12.xsl"?>


Xml overview xslt basics l.jpg
XML OverviewXSLT Basics

  • XSLT Rules Are Applied at the End of the Process

  • Once the XML Document Is Parsed and the DOM Is Instantiated with Document Data, XSLT Transformations Are Applied

XML

FinalOutput

(HTML)

DOM

XSLT


Xml overview xslt basics13 l.jpg
XML OverviewXSLT Basics

  • Example

    • This XSLT code first sets the table formatting, then it defines the content of each table cell

...

<TABLE STYLE="border:1px solid black">

<xsl:for-each select="ROOT/customers">

<TR >

<TD><xsl:value-of select="CustomerID"/></TD>

<TD ><xsl:value-of select="ContactName"/></TD>

<TD><xsl:value-of select="CompanyName"/></TD>

</TR>

</xsl:for-each>

</TABLE>

...


Xml overview xslt basics14 l.jpg
XML OverviewXSLT Basics

...

<TABLE STYLE="border:1px solid black">

<xsl:for-each select="ROOT/customers">

<TR >

<TD><xsl:value-of select="CustomerID"/></TD>

<TD ><xsl:value-of select="ContactName"/></TD>

<TD><xsl:value-of select="CompanyName"/></TD>

</TR>

</xsl:for-each>

</TABLE>

...


Xml overview xslt basics15 l.jpg

#1: Draw an empty table

X

X

X

X

X

#2: Select all elements from ROOT/Customers node

#3: Populate each cell in the HTML table with the element’s text value

XML OverviewXSLT Basics

  • Three Basic Steps for XSLT Transformation

Table X


Xml overview xpath basics l.jpg
XML OverviewXPath Basics

  • An XPath Provides a Simple Mechanism for Finding and Addressing Specific Parts of an XML Document

  • The XPath Selects Element Nodes from a Document

    • Specifies a path in the node tree

    • Filters nodes with a selection criteria based on element and attribute values


Xml overview xml schemas l.jpg
XML OverviewXML Schemas

  • XML Schemas Describe the Structure of an XML Document

    • XML schemas describe the tag and attribute specifications

    • XML schemas also describe constraints on the contained text

    • XML schemas and the DTD are mutually exclusive


Xml overview dom basics l.jpg
XML Overview DOM Basics

  • Document Object Model

  • World Wide Web Consortium (W3C) Language-Independent Interface

  • Provides Access to XML Structure Through an Object-Oriented Model

  • Implemented in msxml.dll

    • Microsoft.XMLDOM


Xml overview microsoft dom implementation l.jpg
XML Overview Microsoft DOM Implementation

XMLDOMDocument / XMLDOMNode

–XMLDOMNodeList

– XMLDOMNode

– XMLDOMNodeList

– XMLDOMAttribute

– XMLDOMElement

– XMLDOMNodeList

–XMLDOMNodeMap

– XMLDOMNamedNodeMap

– XMLDOMAttribute


Xml overview microsoft dom implementation20 l.jpg
XML Overview Microsoft DOM Implementation

  • XMLDOM = XML Document Object Model

    • XMLDOMDocument – top node in the tree

    • XMLDOMNode – represents a node in the tree

    • XMLDOMNodeList – collection of nodes

    • XMLDOMNamedNodeMap – collection of attribute nodes


Xml overview anatomy of an element node l.jpg

parentNode

nodeType = Element

nodeName = customer

nodeValue = null

hasChildNodes = true

previousSibling

nextSibling

attributes

firstChild

childNodes

NamedNodeMap

lastChild

NodeList

...

...

XML OverviewAnatomy of an Element Node


Xml overview using dom in visual basic l.jpg
XML Overview Using DOM in Visual Basic

  • Obtain a Reference to an XML Document

  • Load or Create the Document

  • Navigate Through Its Nodes

    • Document

    • Elements

    • Attributes

  • Retrieve and Modify the XML Data


Xml overview getting data from an xml source l.jpg
XML Overview Getting Data from an XML Source

  • Load the Object with loadXML

  • XML String Parameter Allows Any URL That Returns a Valid XML Document

    • Use any database publishing method in SQL Server 2000

  • Once the Document Is Initialized, It Can Be Navigated


Agenda24 l.jpg
Agenda

  • XML Overview

  • XML in SQL Server 2000

  • Database Publishing

  • OpenXML Rowsets

  • SQL Server 2000 XML Solutions

  • BizTalk Server 2000 Integration


Xml in sql server 2000 business scenarios l.jpg
XML in SQL Server 2000 Business Scenarios

  • Web Applications with Dynamic Data

    • Browser-based applications that require data from a database

  • Business-to-Business Data Processing

    • Data interchange using XML as a

      • Ubiquitous

      • Extensible

      • Platform-independent data transport mechanism


Xml in sql server 2000 areas of support l.jpg
XML in SQL Server 2000 Areas of Support

  • Publishing a Database

    • Provides HTTP access through URLs to templates and annotated schemas

  • T-SQL Language Extensions

    • FOR XML Clause in a SELECT Statement

    • Retrieves XML data from the database engine

  • New OpenXML Syntax in T-SQL

    • Stores data into SQL Server


Xml in sql server 2000 publishing a database l.jpg
XML in SQL Server 2000 Publishing a Database

  • Many Forms of HTTP Access – Each with a Different Purpose

    • URL queries are intended for debugging and easy access in development or testing environments

      • They are not intended for production sites

    • Templates and annotated schemas are intended for production sites

      • They provide safe access by hiding T-SQL code


Xml in sql server 2000 for xml clause l.jpg
XML in SQL Server 2000 FOR XML Clause

  • SELECT Statements Now Have a New Clause

    • FOR XML clause tells SQL Server 2000 the results

      • Should be formatted as XML

    • FOR XML clause supports some modifiers: AUTO, RAW, EXPLICIT

      • Example

SELECT *

FROM customers

FOR XML AUTO


Xml in sql server 2000 openxml syntax l.jpg
XML in SQL Server 2000 OpenXML Syntax

  • OpenXML Clause Provides a Rowset View of an XML Document

    • Can be used wherever a rowset provider such as a table, view, or OpenRecordset appears

  • T-SQL Provides Stored Procedures and Clauses to Manipulate XML Data

    • With T-SQL, you can modify data


Agenda30 l.jpg
Agenda

  • XML Overview

  • XML in SQL Server 2000

  • Database Publishing

  • OpenXML Rowsets

  • SQL Server 2000 XML Solutions

  • BizTalk Server 2000 Integration


Database publishing setting up http access l.jpg
Database PublishingSetting Up HTTP Access

  • HTTP Access Is Provided by an ISAPI Filter that Maps the Request to SQL Server 2000 Through OLE DB

  • An MMC Snap-In Provides the HTTP Configuration Support with a Graphical Interface

    • This tool creates the Microsoft Internet Information Server virtual subdirectory with the ISAPI filter


Slide32 l.jpg

4

9

8

5

7

6

2

1

3

ISAPI Filter

Customer

OLE DB

SQL

Internet

IIS

Then Data GoesBack to Customer

Database PublishingSetting Up HTTP Access

  • Example

    • User sends HTTP request to retrieve SQL Server 2000 data


Database publishing setting up http access33 l.jpg
Database PublishingSetting Up HTTP Access

  • HTTP URL Format

    • http://domain/vroot/vname/…

  • SQL IIS Admin Features

    • Managing the virtual root

      • Security and settings

      • Virtual root physical path

    • Registering virtual names

    • Restarting HTTP access to SQL Server 2000

    • Managing multiple servers


Database publishing setting up http access demo l.jpg
Database PublishingSetting Up HTTP Access Demo

  • Create HTTP Access for the Northwind Database

  • Allow URL Queries to this Virtual Root

  • Browse the Customers Table

  • Shows What HTTP Access Is and How To Set It Up on a Particular Server

Demo…


Database publishing for xml clause l.jpg
Database PublishingFOR XML Clause

  • Besides the ISAPI Application and Its Virtual Directory, the Engine Has to Know Which Data Format to Use

  • SELECT Clause Now Supports the FOR XML Clause

  • It Requests XML Results from SQL Server 2000

SELECT *

FROM customers

FOR XML AUTO


Database publishing for xml clause36 l.jpg
Database PublishingFOR XML Clause

  • FOR XML Clause Supports Three Different Modifiers

    • Raw

      • Transforms each row in the result set into an XML element with the generic identifier row

    • Auto

      • Returns query results in a simple, nested XML tree

    • Explicit

      • Specifies the shape of the XML tree


Database publishing for xml clause37 l.jpg
Database PublishingFOR XML Clause

  • Supports Two Optional Arguments

    • SchemaOption

      • Uses XMLData Schema specification

      • With this option, XMLData schema will be returned

    • Elements

      • Columns are returned as sub-elements instead of XML attributes

      • Auto mode only


Database publishing for xml modes l.jpg
Database PublishingFOR XML Modes

  • EXPLICIT

    • Allows complete control over XML format of XML result

    • Values in columns can be mapped to attributes or sub-elements

    • Supports arbitrary nesting including siblings and collapsing of hierarchy

    • Construction of ID/IDREF relationships

    • Supports CDATA sections in XML output

    • Nesting done based on PK/FK relationships


Database publishing explicit example l.jpg
Database PublishingEXPLICIT Example

select 1 as TAG, NULL as PARENT, ‘P-’+ProductID as [Product!1!pid!id], ProductName as [Product!1!name!element],

NULL as [OrderDetail!2!oid]from Products

union all

select 2, 1, ‘P-’+P.ProductID, NULL , OD.OrderIDfrom Products P inner join OrderDetails OD on Products.ProductID=OrderDetails.ProductID

order by [Product!1!pid!id]

for xml explicit


Database publishing explicit example results l.jpg
Database Publishing EXPLICIT Example (Results)

<Product pid="P-2">

<Name>Chang</Name>

<OrderDetail oid="10258"/> </Products>…


Database publishing for xml clause demo l.jpg
Database Publishing FOR XML Clause Demo

  • Code Walkthrough of SQL XML Viewer

  • Execute SQL XML Viewer

    • Use different FOR XML options

  • Shows How To

    • Use FOR XML clause

    • Use the DOM Inside Visual Basic

    • Manage SQL Server 2000 XML in Visual Basic

Demo…


Database publishing virtual names l.jpg
Database PublishingVirtual Names

  • Establish the Initial Mapping to Subdirectories Containing Annotated Schemas and Templates

    • Virtual Names can be set by the SQL IIS Admin, given a name, a path to directory or file, and a type

      • Supports Dbobject, Schema, or Template types

  • Thus, Templates or Annotated Schemas Can Be Referenced in the URL

    http://localhost/Northwind/Customer/cust.xml


Database publishing using templates l.jpg
Database PublishingUsing Templates

  • Templates Are Equivalent to Method Calls

  • Use Templates to Avoid Having the T-SQL Code in the T-SQL Address Query String

    • Then the browser shows only the resulting code from the template

  • Like ASP Pages, the Actual Template Requesting Code Is Not Shown


Database publishing using templates44 l.jpg
Database PublishingUsing Templates

  • Other Advantages of Using Templates

    • Can store several queries

    • Can contain an associated XSLT file, as with any other XML file

    • Supports parameters and stored procedures

    • Supports query schemas through XPaths


Database publishing using templates45 l.jpg
Database PublishingUsing Templates

  • Templates Are Accessed by Creating a Virtual Name in SQL IIS Admin

    • The virtual name maps a directory where files are stored

  • Templates Are Then Accessed by References in the URL Using the Virtual Name

    http://localhost/Northwind/Templates/customer.xml


Database publishing using templates46 l.jpg
Database PublishingUsing Templates

  • Template Example

<?xml-stylesheet type="text/xsl" href="Orders.xsl"?>

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">

<sql:header>

<sql:param name="CustomerID">AAAA</sql:param>

</sql:header>

<sql:query>select OrderId, CustomerID, OrderDate from Orders where [email protected] order by OrderDate for XML auto, elements

</sql:query>

</ROOT>


Database publishing using the templates demo l.jpg
Database PublishingUsing the Templates Demo

  • Walkthrough the customer.xml Template File

  • Walkthrough the Customer.xsl Transformation File

  • Create a Virtual Name Template

  • Browse the customer.xml File

Demo…


Database publishing annotated schemas l.jpg
Database PublishingAnnotated Schemas

  • Provide Mapping Between XML and Relational Schema

    • Uses annotations

    • Between elements and attributes in the XMLdata schema

    • To tables and columns in a database

    • Relationships between XML hierarchy and relational tables


Database publishing annotated schemas49 l.jpg
Database PublishingAnnotated Schemas

  • sql:relation

    • Establishes a mapping between an element and a database table

  • sql:field

    • Establishes a mapping between an element and a database field

  • sql:relationship

    • Defines a relationship between two tables or views in the database


Database publishing annotated schema demo l.jpg
Database PublishingAnnotated Schema Demo

  • Create a Virtual Name for Demo Schemas

  • Walkthrough the Schema

  • Browse Schemas with Internet Explorer

  • Demonstrates How Different Elements in the Schema Map to Different Database Objects

Demo…


Database publishing xpaths l.jpg
Database PublishingXPaths

  • Query Rows in XML in the Virtual Document Are Defined by Annotated Schema

  • Thus, Elements Inside the Template or Schema Can Be Referenced in the URL

  • Using the Nodes Hierarchy of an XML Document

http://localhost/Northwind/Customer.xml/OrderDetails


Database publishing xpaths52 l.jpg
Database PublishingXPaths

  • Example: E-Commerce Database

    • The XPath is Customer.xml/OrderDetails

Order Details

Customer

Order

Product

Vendor


Database publishing using xpaths demo l.jpg
Database PublishingUsing XPaths Demo

  • Shows How to Access Schemas With XPaths

  • Demo Uses Template to Write the XPath and the Pointer to the Schema File

  • Demo Selects Element Nodes and Filters Data

Demo…


Agenda54 l.jpg
Agenda

  • XML Overview

  • XML in SQL Server 2000

  • Database Publishing

  • OpenXML Rowsets

  • SQL Server 2000 XML Solutions

  • BizTalk Server 2000 Integration


Openxml rowsets xml rowset support l.jpg

XMLDOM

sp_xml_preparedocument

OpenXML

Table/View

FOR XML Clause

OpenXML RowsetsXML Rowset Support

XML


Openxml rowsets openxml clause l.jpg
OpenXML RowsetsOpenXML Clause

  • OpenXML Is Combined with T-SQL Code

    • Use OpenXML as a relational view of XML

    • Execute any valid operation

INSERT INTO Customers(CustID, FirstName)

SELECT *

FROM OpenXML(@idoc, 'Customer‘, 1)

WITH (CustID varchar(5),

FirstName nvarchar(30))


Openxml rowsets openxml clause57 l.jpg
OpenXML RowsetsOpenXML Clause

  • @idoc Parameter Is the XML Document Reference

  • @rowpattern Is the XPath Pattern Used to Select Rows of the View

  • @flags

    • Define if attributes or elements from selected node are mapped to columns of rows

  • With Clause

    • Defines exposed rowset and column mapping for the view


Openxml rowsets xml in t sql demo l.jpg
OpenXML RowsetsXML in T-SQL Demo

  • Walkthrough Demo Files

    • These files show how to use OpenXML to create a Rowset from XML documents

  • Execute the Files Using Different XPaths and Flags

  • Explains How To Manage XML Documents Inside T-SQL Code

Demo…


Agenda59 l.jpg
Agenda

  • XML Overview

  • XML in SQL Server 2000

  • Database Publishing

  • OpenXML Rowsets

  • SQL Server 2000 XML Solutions

  • BizTalk Server 2000 Integration


Sql server 2000 xml solutions advantages l.jpg
SQL Server 2000 XML SolutionsAdvantages

  • XML Enables Applications To Be Built in a Loosely Coupled Manner

  • XML as Data Representation Has many Advantages

    • XML was built to be inherently extensible

    • XML can be transmitted through firewalls using standard internet protocols

    • XML can be transmitted across platforms

    • XML is a standard


Sql server 2000 xml solutions examples l.jpg
SQL Server 2000 XML Solutions Examples

  • Web Applications That Require Data from Sources Outside of the Enterprise

    • Web Applications can expose SQL Server 2000 data to other applications in an easy manner

      • Using a standard

  • Enterprise Applications That Need to Adapt Easy to Data Changes


Sql server 2000 xml solutions sql xml and ado xml positioning l.jpg
SQL Server 2000 XML Solutions SQL XML and ADO XML Positioning

  • SQL Server 2000 Generates XML Ranging from a Predefined Format to Arbitrary Formats

  • ADO XML Generates Only a Pre-Defined Format

    • ADO 2.5 release supports generation of hierarchical XML

      • Once again, in a pre-defined format


Sql server 2000 xml solutions sql xml and ado xml positioning63 l.jpg
SQL Server 2000 XML Solutions SQL XML and ADO XML Positioning

  • SQL Server 2000 Provides the Flexibility Needed To Use Standard XML Grammars

    • There Are Significant Performance Gains by Using the SQL Server 2000 Implementation

  • ADO XML Should Be the Choice If a Single Organization Controls All the System

    • Programmers Can Use Existing Skills To Access Data with ADO XML


Agenda64 l.jpg
Agenda

  • XML Overview

  • XML in SQL Server 2000

  • Database Publishing

  • OpenXML Rowsets

  • SQL Server 2000 XML Solutions

  • BizTalk Server 2000 Integration


Biztalk server 2000 integration annotated schemas l.jpg
BizTalk Server 2000 Integration Annotated Schemas

  • Schemas Provide the Rules to Construct or Validate an XML Document

  • BizTalk Uses Public Schemas to Standardize Documents Sent and Received

  • BizTalk XML Schema Specifications Can Be Stored and Retrieved in SQL Server 2000

    • An annotated schema that matches a BizTalk schema can be created and then used like any other schema


Biztalk server 2000 integration annotated schemas66 l.jpg
BizTalk Server 2000 Integration Annotated Schemas

  • SQL Server 2000 Can Store and Retrieve Data in Corresponding BizTalk Schema Format

    • The XML document retrieved can be submitted to BizTalk Server

    • Because it is based on a schema, it is always a valid XML document


Biztalk server 2000 integration annotated schemas67 l.jpg
BizTalk Server 2000 Integration Annotated Schemas

BizTalk

Public

Schema

Schema

+

Annotations

XMLData

Format

SQL Server 2000


Biztalk server 2000 integration demo l.jpg
BizTalk Server 2000 Integration Demo

  • Generate a Schema Specification in BizTalk Server 2000

  • Save It in the SQL Server 2000 Schema Directory

  • Query It with Internet Explorer

Demo…


Call to action l.jpg
Call to Action

  • Get Ready for XML

  • Get Ready with XSLT Transformations

  • Work with the Document Object Model

  • Download SQL Server 2000 Evaluation

  • Build Web Applications Using XML Format for Data



More resources l.jpg
More Resources

  • http://msdn.microsoft.com/xml/

  • http://www.microsoft.com/sql/

  • http://www.w3.org/XML/


ad