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

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


  • 91 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

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

    • SQL XML Managed Classes

    • Web Services

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


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

  • 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

  • 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

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


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?

<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.

  • 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?

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

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

  • 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

  • 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

  • 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.

  • 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

  • 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

  • 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

  • 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

  • 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

<?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

  • 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

<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?

  • 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

  • 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

  • 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

  • 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

  • .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

  • 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

  • .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

  • 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

  • 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

  • .Net Solution SQLXMLDemonstrations

  • .Net Project SQLXMLCommandDemo

  • Illustrates

    • Using SQLXMLCommand

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


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

  • 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

  • .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


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


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

  • Transforms XML to HTML or other text

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


SQLXMLCommand XPath

  • CustomerInvoices

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

    Requires a Mapping Schema

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


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

  • .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

  • .Net Solution SQLXMLDemonstrations

  • .Net Project SQLXMLDiffgramInsert

  • Illustrates

    • SQLXMLAdapter

    • Diffgram

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


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

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


Moving the Work to the Client

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


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

  • .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

  • 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

  • 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

  • .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

  • Curlingstone an Imprint of Wrox

  • Download code from the book atwww.Curlingstone.com

  • Andrew Novick

    • anovick@NovickSofware.com

    • 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

  • 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!

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


  • Login