Net programmatic access to sql server 2000 xml l.jpg
This presentation is the property of its rightful owner.
Sponsored Links
1 / 58

.Net Programmatic Access to SQL Server 2000 XML PowerPoint PPT Presentation


  • 81 Views
  • Uploaded on
  • Presentation posted in: General

.Net Programmatic Access to SQL Server 2000 XML. Andrew Novick. Boston .Net User Group. April 9, 2003. Agenda. Overview of SQL Server 2000 XML What is XML What is SQL XML SQL Server 2000 XML For XML Queries IIS Access Web Services .Net Programmatic Access ADO.Net

Download Presentation

.Net Programmatic Access to SQL Server 2000 XML

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


Net programmatic access to sql server 2000 xml l.jpg

.Net Programmatic Access to SQL Server 2000 XML

Andrew Novick

Boston .Net User Group

April 9, 2003


Agenda l.jpg

Agenda

  • Overview of SQL Server 2000 XML

    • What is XML

    • What is SQL XML

  • SQL Server 2000 XML

    • For XML Queries

    • IIS Access

    • Web Services

  • .Net Programmatic Access

    • ADO.Net

    • SQL XML Managed Classes

    • Web Services

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Session objectives l.jpg

Session Objectives

  • Know the requirements for installing SQL XML

  • Understand the parts of SQL XML and how they relate to SQL Server

  • Be able to use the SQLXML Managed Classes

  • Be able to expose a stored procedure as a Web Service and consume it from .Net code.

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Novick software l.jpg

Novick Software

  • Consulting Company of Andrew Novick

    • Project Management

    • Business Applications Design

    • Programming

    • Coaching

    • Training

  • Technologies:

    • SQL Server, VB, VB.Net, ASP, ASP.Net, and XML

  • http://www.NovickSoftware.comHome of the Transact-SQL User-Defined Function of the Week

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


The book l.jpg

The Book

  • SQL Server 2000 XML Distilled

  • Curlingstone Press (WROX)

  • Published October 2002

  • ISBN 1-904347-08-8

  • Code Samples www.Curlingstone.com

    • (get them soon, they’re going out of business)

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


The cover l.jpg

The Cover

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


So what is xml l.jpg

So What is XML

  • XML is a systematic method of formatting text based on an SGML syntax so that the structure of the text coveys meaning.

  • XML is Comma Separated Values on Steroids

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Sample xml l.jpg

Sample XML?

<root>

<pet species=“Guniea Pig”>

<name>Violet</name><age>1</age>

</pet><pet><name>Rodrick</name><age>3</age></pet>

</root>

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


A few ways that xml is used today l.jpg

A few ways that XML is used today.

  • Configuration Files in .Net

    • WebConfig.XML

    • App.config

  • Send Data

    • BlueExpress – SEC Form NF

    • BizTalk

  • Persist Data – Diffgrams used by ADO.Net

  • Exchange News Feeds – NewsML – RDF/RSS

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


What are the other xs l.jpg

What are the other Xs?

  • XSL

    • eXtensible Stylesheet LanguageA language for transforming XML into some other type of text, usually HTML

  • XSD/XDR

    • XML Grammar for XML documents.

  • XQuery, XSL-FO, XHTML, XLink, XPath, WS-XML, Xforms

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Sql server 2000 xml l.jpg

SQL Server 2000 XML

  • Built into SQL Server 2000

    • For XML Queries

      • For XML RAW, AUTO, EXPLICIT, ELEMENTS, XMLDATA

    • OpenXML

  • In SQL Server Web Releases 1, 2, 3

    • IIS access

      • SQL, Templates, and Web Services

    • .Net Managed Classes

    • Client-side Processing

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Sql server 2000 forxml queries l.jpg

SQL Server 2000 FORXML Queries

  • SELECT * from Authors for XML RAW

  • Say the secret word:

    • DBCC TRACEON (257)

  • Types

    • Auto Mode

    • Explicit Mode

    • Elements for Element oriented XML

    • XMLDATA – for adding an XDR Schema

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Demo for xml l.jpg

Demo: For XML

  • Using SQL Query Analyzer

  • File NS_SQLXML_ForXML_Examples.sql

  • Illustrates

    • For XML Raw

    • For XML Auto

    • For XML Auto Elements

    • For XML Explicit

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Sql server 2000 openxml l.jpg

SQL Server 2000 OpenXML

  • Parses XML documents inside SQL Server

  • Uses MSXML to parse the documents

    • Will consume up to 1/8th the memory allocated to SQL Server

  • OpenXML is a rowset returning function

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Sample openxml l.jpg

Sample OPENXML

  • Schema is up to you.

<update>

<Authors au_id="238-95-7766" au_lname="Ismore" au_fname="Les"/>

<Authors au_id="427-17-2319" au_lname="More" au_fname="Bill"/>

</update>

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Sample open xml l.jpg

Sample OPEN XML

CREATE PROC usp_Update_AuthorNames @AuthorData text AS

DECLARE @hDoc int

exec sp_xml_preparedocument @hDoc OUTPUT, @AuthorData

UPDATE Authors

SET Authors.au_fname = XMLEmployee.au_fname,

Authors.au_lname = XMLEmployee.au_lname

FROM OPENXML(@hDoc, 'update/Authors')

WITH Authors XMLEmployee

WHERE Authors.au_id = XMLEmployee.au_id

-- free any memory consumed by the document

EXEC sp_xml_removedocument @hDoc

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Sql server 2000 web releases l.jpg

SQL Server 2000 Web Releases

  • SQL Server Web Release 3 SP1

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

  • Requires

    • SQL Server 2000

    • MSXML 4.0

    • Soap Toolkit 2.0

    • IIS to use Web features

    • A Net language to use the managed classes

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Sql xml web services toolkit l.jpg

SQL XML Web Services Toolkit

  • Released February 2003

  • Packages all required components

    • SQLXML 3.0 SP 1

    • MSXML 4.0

    • Soap Toolkit 2.0

  • White Papers and Examples

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Ado classic access l.jpg

ADO Classic Access

  • Capable of using SQL XML from ADO 2.5 (Recommend 2.6 or above)

  • Use ADO Stream objects to return textual XML

  • SQLOLEDB Provider exposes extended properties that mimic those available in .Net’s SQLXMLCommand

  • Used by Visual Basic 6, VBScript, JavaScript. or other COM consumer

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Sqlxmlbulkload activex component l.jpg

SQLXMLBulkLoad ActiveX Component.

  • Loads XML

  • Input Only – Use For XML Query to Generate the output.

  • Needs an XDR or XSD schema

  • Can load linked tables from one file

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Web based access to sql xml l.jpg

Web Based Access to SQL XML

  • Uses IIS to achieve Web Access

  • Installs it’s own ISAPI filter

  • Configuration Tool allows setting up sites and controlling access

  • Web Services supported in SQLXML 3.0

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Web formats l.jpg

Web Formats

  • SQL= SELECT … FROM… FOR XML…

  • Templates

    • SQL FOR XML Query

    • SQL Queries with Parameters

    • Multiple Queries

  • Web Service/SOAP

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Demo web based queries l.jpg

Demo: Web Based Queries

  • Files

    • IIS Based Queries.txt

    • CustomersOrders.xml

    • CustomerOrders.XML

    • MultipleQueries.XML

    • Authors2.XSD

  • Illustrates

    • IIS based queries

    • Templates

    • Xpath Query

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Schemas l.jpg

Schemas

  • Two forms XDR and XSD

  • Replace DTDs in the XML World

  • Define the format of a valid XML Document

  • Map from the Relational to the SQL World

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Mapping schema authors2 xsd l.jpg

Mapping Schema: Authors2.xsd

<?xml version="1.0"?>

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"

xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

<xsd:element name="Authors" sql:relation="Authors">

<xsd:complexType>

<xsd:sequence>

<xsd:element name="ID" type="xsd:string"

sql:field="au_id" />

<xsd:element name="FirstName" type="xsd:string"

sql:field="au_fname" />

<xsd:element name="LastName" type="xsd:string"

sql:field="au_lname" />

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:schema>

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Updategram and diffgrams l.jpg

Updategram and Diffgrams

  • A types of SQLXML template

  • UpdateGram Specifies database operation(s)

    • Insert

    • Update

    • Delete

  • Diffgram Has Before and After state of the datbase

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Format of an updategram l.jpg

Format of an UpdateGram

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram"> <updg:sync [mapping-schema= "AnnotatedSchemaFile.xml"] >

<updg:before> ... </updg:before>

<updg:after> ... </updg:after>

</updg:sync>

</ROOT>

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Why use updategrams l.jpg

Why Use Updategrams?

  • Any source of XML may be transformed into an updategram.

  • Updategram stores the before and after state of the database whilch can be used for delayed application of to the database.

  • Alternative transport mechanisms can be used when a direct connection to the database is unavailable.

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Why use diffgrams l.jpg

Why Use Diffgrams

  • Useful for offline operations. Diffgrams are the persistible format of the .Net dataset. They can be used to reconstitute a dataset without going back to the database.

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Net programmatic access l.jpg

.Net Programmatic Access

  • ADO.Net and XMLReader class on aFOR XML query

  • ADO.Net’s Dataset Uses XML to represent its contents as a Diffgram.

  • SQLXML includes .Net Managed Classes

  • Consume Web Services

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Ado net can retrieve xml l.jpg

ADO.Net Can Retrieve XML

  • Can retrieve the results of a FOR XML Query

  • Pass the results to .Net XML classes for further manipulation.

    • XMLReader

      • XMLTextReader

    • XMLDocument (DOM)

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Demo ado net to retrieve xml l.jpg

Demo: ADO.Net to Retrieve XML

  • .Net Solution SQLXMLDemonstrations

  • .Net Project XMLTextReaderExample

  • Illustrates

    • Referencing Microsoft.Data.SqlXML

    • For XML Query

    • Using XMLTextReader

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Ado net dataset object l.jpg

ADO.Net Dataset Object

  • Uses XML as an internal representation

  • No SQLXML involved

  • Methods

    • GetXML

    • GetXMLSchema

    • InferXMLSchema

    • ReadXML

    • WriteXML

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Demo write xml from a dataset l.jpg

Demo: Write XML From a Dataset

  • .Net Solution SQLXMLDemonstrations

  • .Net Project SQLXMLDataSetDemo

  • Illustrates

    • ADO.Net SQLCommand

    • Persisting XML from a Dataset to a file

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Net managed classes l.jpg

.Net Managed Classes

  • SQLXMLCommand

    • Executes a SQL, Template, or XPath query

  • SQLXMLParameter

    • Provides parameters to a query

  • SQLXMLAdapter

    • .Net Adapter Class to act an intermediary between Dataset objects and the database connection

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Sqlxmlcommand class l.jpg

SQLXMLCommand Class

  • Properties control how the class behaves

  • ExecuteStream method executes the command and returns a stream object

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Demo sqlxmlcommand l.jpg

Demo: SQLXMLCommand

  • .Net Solution SQLXMLDemonstrations

  • .Net Project SQLXMLCommandDemo

  • Illustrates

    • Using SQLXMLCommand

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Sqlxmlcommand commandtype l.jpg

SQLXMLCommand.CommandType

  • Dialects

    • T-SQL SQLXMLCommandType.SQL

    • TemplatesSQLXMLCommandType.Template

    • Template FileSQLXMLCommandType.TemplateFile

    • XpathSQLXMLCommandType.XPath

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Templates l.jpg

Templates

  • Can be sent to SQL Server many ways including via SQLXMLCommand

  • Contain

    • SQL Queries

    • UpdateGrams

    • DiffGrams

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Demo template l.jpg

Demo: Template

  • .Net Solution SQLXMLDemonstrations

  • .Net Project SQLXMLCommandTemplate

  • Illustrates

    • Using a template to provide the query to SQLXMLCommand

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Sqlxmlcommand xml formatting l.jpg

SQLXMLCommandXML Formatting

  • Root .RootTag = “root”

  • Output Encoding: UDF-8, UNICODE, etc..OutputEncoding = “UDF-8”

  • NameSpaces.NameSpaces = “xmlns:rdf=“”http://www.w3.org/TR/WD-rdf-syntax”

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Sqlxmlcommand file management properties l.jpg

SQLXMLCommandFile Management Properties

  • .BasePath – Top-level path to XML files

  • .SchemaPath

  • .XSLPath

  • Paths can be either:

    • File Paths

    • URLs

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Sqlxmlcommand xslt l.jpg

SQLXMLCommand XSLT

  • Transforms XML to HTML or other text

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Sqlxmlcommand xpath l.jpg

SQLXMLCommand XPath

  • CustomerInvoices

  • CustomerInvoices/Customer[@state="CA"]/Invoice

    Requires a Mapping Schema

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Sqlxmladapter l.jpg

SQLXMLAdapter

  • Like the SQLAdapter

  • Acts an an intermediary between the SQLConnection and Dataset objects

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Demo sqlxmladapter l.jpg

Demo: SQLXMLAdapter

  • .Net Solution SQLXMLDemonstrations

  • .Net Project SQLXMLAdapterExample

  • File Authors.XSD

  • Illustrates

    • SQLXMLAdapter

    • Xpath Query

    • Mapping Schema

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Demo diffgram l.jpg

Demo: Diffgram

  • .Net Solution SQLXMLDemonstrations

  • .Net Project SQLXMLDiffgramInsert

  • Illustrates

    • SQLXMLAdapter

    • Diffgram

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Client side programming l.jpg

Client Side Programming

  • SQL Server Web Release 2 and above

  • SQLXMLOLEDB Provider moves the work of formatting XML to the client.

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Server based xml creation l.jpg

Server Based XML Creation

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Moving the work to the client l.jpg

Moving the Work to the Client

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Sqlxmloledb provider l.jpg

SQLXMLOLEDB Provider

  • SQLXMLOLEDB Provider uses SQLOLEDB to retrieve data with an standard Query

  • XML is formed in the client

  • New FOR XML type XML Nested Query

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Demo client side processing l.jpg

Demo: Client Side Processing

  • .Net Solution SQLXMLDemonstrations

  • .Net Project SQLCommandClientSide

  • Stored Procedure Pubs.dbo.usp_AuthorBooks

  • Illustrates

    • For XML Nested Query

    • Client Side Processing

    • SQLXMLParameter Class

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Consuming sql xml web services l.jpg

Consuming SQL XML Web Services

  • Web Services are created by exposing

    • Stored Procedures

    • User Defined Functions

    • templates

  • Consumed like any other Web Service

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Configure iis support tool l.jpg

Configure IIS Support Tool

  • Configure soap virtual name

  • Add stored procedures and UDFs as web methods

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Demo create consume a web service l.jpg

Demo: Create & Consume a Web Service

  • .Net Solution SQLXMLDemonstrations

  • .Net Project SQLXMLWebServiceConsumer

  • Illustrates

    • Configuring IIS for Support

    • Exposing a stored procedure as a Web Service

    • WSDL

    • Consuming a Web Service

    • WebServicesStudio

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Sql server 2000 xml distilled l.jpg

SQL Server 2000 XML Distilled

  • Curlingstone an Imprint of Wrox

  • Download code from the book atwww.Curlingstone.com

  • Andrew Novick

    • [email protected]

    • 978-440-8126

    • www.NovickSoftware.com

      Consulting - Project Management – Design - Programming - Training

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Transact sql udf of the week l.jpg

Transact-SQL UDF of the Week

  • Free newsletter about SQL Server User-Defined Functions

  • A CREATE FUNCTION script in each issue

  • Additional information on UDFs

  • Find it athttp://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


Thanks for coming l.jpg

Thanks for coming!

www.NovickSoftware.com.Net Programmatic Access to SQL Server 2000 XML


  • Login