Sql server 2000 and xml
Download
1 / 44

SQL Server 2000 and XML - PowerPoint PPT Presentation


  • 293 Views
  • Updated On :

SQL Server 2000 and XML. Erik Veerman Consultant Intellinet Business Intelligence. Objectives . Give an overview on the new XML capabilities in SQL Server 2000 Demonstrate rather than present Provide base knowledge for… XML architecture decisions Research starting points

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 and XML' - DoraAna


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
Sql server 2000 and xml l.jpg

SQL Server 2000and XML

Erik Veerman

Consultant

IntellinetBusiness Intelligence


Objectives l.jpg
Objectives

  • Give an overview on the new XML capabilities in SQL Server 2000

  • Demonstrate rather than present

  • Provide base knowledge for…

    • XML architecture decisions

    • Research starting points

    • Pros and cons of parallel XML technology implementations

    • Data interaction leveraging SQL’s XML capabilities


Agenda l.jpg
Agenda

  • The ability to retrieve and write XML data:

    • Retrieve XML data using the SELECT statement and the FOR XML clause.

    • Write XML data using OPENXML rowset provider.

  • The ability to access SQL Server using HTTP.

    • IIS Configuration

    • Template Queries

    • XSL Style sheets


Agenda4 l.jpg
Agenda

  • Support for XDR schemas/XML Views

    • XPath queries against these schemas.

  • XML for SQL Web Release

    • Bulk Load

    • Updategram

  • ADO programming methods (Chris Hagen)

  • XML in use (Chris Hagen)


Agenda sql server 2000 xml select l.jpg

Agenda:SQL Server 2000 XML SELECT

How has SQL Server extended the SELECT paradigm to support XML?


Select statement syntax l.jpg
SELECT Statement Syntax:

SELECT … … …

FOR XML { RAW | AUTO | EXPLICIT }

[, XMLDATA]

[, ELEMENTS]

[, BINARY Base64]


Simple xml query l.jpg

Simple XML Query:

SELECT oh.CustomerID, oh.OrderId,

od.ProductID, od.UnitPrice, od.Quantity

FROM Orders oh

INNER JOIN [Order Details] od

ON oh.orderid = od.orderid

FOR XML {RAW | AUTO}


For xml raw format l.jpg

FOR XML RAW Format

Takes the query result and transforms each row in the result set into an XML element with a generic identifier <row /> as the element tag.


For xml auto format l.jpg

FOR XML AUTO Format

Returns query results in a simple, nested XML tree. Each table in the FROM clause for which at least one column is listed in the SELECT clause is represented as an XML element. The columns listed in the SELECT clause are mapped to the appropriate element attributes.


For xml explicit format l.jpg
FOR XML EXPLICIT Format

  • Based on the concept of a Universal Table containing all the information about the resulting XML tree.

  • Contains Tag and Parent meta-data

    • Tag: Tag number of current element

    • Parent: Tag number of parent element

  • Column names are XML Generic identifiers.

    • Identifier!TagNumber!AttributeName


Explicit sample query l.jpg

EXPLICIT Sample Query

SELECT 1 AS Tag, NULL AS Parent,

oh.CustomerID [Order!1!CustomerID], oh.OrderId [Order!1!OrderId],

NULL [OrderDetail!2!ProductId], NULL [OrderDetail!2!UnitPrice],

NULL [OrderDetail!2!Quantity]

FROM Orders oh WHERE oh.CustomerId = ‘ANTON’

UNION ALL

SELECT 2, 1,

oh.CustomerID, oh.OrderId, od.ProductID, od.UnitPrice, od.Quantity

FROM Orders oh INNER JOIN [Order Details] od

ON oh.orderid = od.orderid WHERE CustomerId = ‘ANTON’

ORDER BY

[Order!1!CustomerID], [Order!1!OrderId], [OrderDetail!2!ProductId]

FOR XML EXPLICIT



Agenda sql server 2000 openxml l.jpg

Agenda:SQL Server 2000 OpenXML

Allows one to parse and utilize an XML document


Openxml process l.jpg
OpenXML Process

  • Input an XML document

  • Process an internal representation

    • exec sp_xml_preparedocument

  • Parse the XML document

    • OpenXML Rowset function

  • Remove internal representation from memory

    • EXEC sp_xml_removedocument



Openxml syntax l.jpg

OpenXML Syntax

OPENXML(idoc int [in], rowpattern nvarchar[in], [flags byte[in]])

[WITH (SchemaDeclaration | TableName)]



Agenda sql server 2000 iis integration l.jpg

Agenda:SQL Server 2000 IIS Integration

How does one use IIS for retrieving XML data.


Using iis the process l.jpg
Using IIS, the process.

  • Configure an IIS Virtual Root

  • Define template queries

  • Define style sheets

  • Make HTTP request



Http access to data l.jpg
HTTP Access to Data

  • URL Query

    • http://sqlserver/vroot?parameters

  • Parameters

    • Sql = SELECT+*+FROM+Sku+FOR+ XML+RAW

    • Encoding = UTF-8

    • Root = root


Http access to data22 l.jpg
HTTP Access to Data

  • Direct Query

    • Good for a single select of a single column

    • Value is returned in native format, not XML

    • Enables direct retrieval of objects like images, OLE objects, etc.

  • http://sqlserver/vroot/dbobject/xpath


What are template queries l.jpg
What are Template Queries?

  • These are XML documents that define queries

  • Can be bound directly to Style Sheets

  • Called by referencing the XML document in the URL


What are template queries24 l.jpg
What are Template Queries?

  • Template

    • http://sqlserver/vroot/vname?params

    • Vname is a complete path to the template XML file

    • Parameters may be xsl, encoding, or contenttype; or user defined.

    • Provides an easy way to perform complex queries with little network traffic


Why use template queries l.jpg
Why use Template Queries?

  • Can use dynamic SQL but no restrictions on what is executed

  • Automatic formatting of the XML header information

  • Templates simplify the HTTP request

  • Templates have better support parameters


Define and use stylesheets l.jpg
Define and Use StyleSheets

  • Can be added to the URL

    • XSL=stylesheet.xsl

    • ContentType = text/html

  • Can be defined in the Template



Agenda xml xdr schema xml views update gram l.jpg

Agenda:XML XDR SchemaXML Views | Update gram

Mapping relational data through an XML schema


What is an xml view l.jpg
What is an XML View?

  • Defines an XML-formatted view on the database

  • Annotations specify the XML to relational database mapping for column values and relationships

  • Uses Xpath to query the XML View


How to use xml views l.jpg
How to use XML Views

  • http://server/vroot/vname/xpath?parameters

  • Vname is a direct reference to the schema file

  • Xpath is the xpath query

  • Parameters may be xsl, encoding, contenttype, or user defined



Xml for sql web release l.jpg
XML for SQL Web Release

  • XML for SQL Web Release 1

    • UpdateGrams

    • Bulk Load XML support

  • XML for SQL Web Release 2 (July 2001)

    • XML Views with XSD

    • Support for DiffGrams

    • Client Side XML formatting


Update gram l.jpg
Update gram

  • Schema mapping, Update gram syntax

  • Inserts, Updates, Deletes

  • Using <Before> and <After> blocks

  • Passing Parameters


Writing data with update grams l.jpg
Writing Data withUpdate Grams

  • Update grams (example)

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

    <updg:sync mapping-schema="SampleSchema.xml" >

    <updg:before>

    </updg:before>

    <updg:after>

    <Customer CustID="AAAAA" Company="Bottom-Dollar Markets" />

    </updg:after>

    </updg:sync>

    </ROOT>


Miscellaneous data retreival olap data sp makewebsql l.jpg

Miscellaneous Data Retreival:OLAP datasp_makewebsql

DEMO


Agenda ado programming methods for sql server 2000 xml l.jpg

Agenda:ADO Programming Methods for SQL Server 2000 XML

How does one write components utilizing the new SELECT FOR XML and IIS?


Utilize ado functionality l.jpg
Utilize ADO Functionality

  • Calls made through ADO Command

  • Uses ADO Stream Object

  • Requires ADO 2.6

    • Stream Object defined as a property of the Command Object

  • XML Data placed in the Stream Object

    • Obtained using ReadText


Ado supports xml l.jpg
ADO supports XML

  • Recordsets can be persisted to XML

    • File

    • Stream Object

    • DOM Integration

    • IIS5 Response Object

  • Also support for ADTG

    • Advanced Data Table Gram format

  • Support Hierarchical Recordsets


Ado xml stream support l.jpg
ADO XML Stream Support

  • Can persist directly to an ADO Stream Object

  • XML extracted using Stream ReadText

  • XML data can then be utilized in memory

    • Pass XML back to client

    • Transform to HTML using XSL


Ado and dom integration l.jpg
ADO and DOM Integration

  • Can persist XML directly into the XML DOM Document

  • Recordset.Save MSXML.DOMDocument, adPersistXML

  • Can perform direct XSL transform for HTML based components


Ado and iis5 response l.jpg
ADO and IIS5 Response

  • Response Object exposes and IStream Interface

  • Recordset.Save Response, adPersistXML

  • Displays XML in browser

  • Avoids expensive disk operations



For further information l.jpg
For further Information

  • XML website

    http://msdn.microsoft.com/xml

  • SQL Server 2000 and IIS

    http://msdn.microsoft.com/msdnmag/issues/0300/sql/sql.asp

  • SQL Server 2000 http://www.microsoft.com/sql

  • ADO http://www.microsoft.com/ado



ad