Net programmatic access to sql server 2000 xml
Download
1 / 58

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


  • 117 Views
  • Uploaded on

.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

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 '.Net Programmatic Access to SQL Server 2000 XML' - zivanka


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 a FOR 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

    • Templates SQLXMLCommandType.Template

    • Template File SQLXMLCommandType.TemplateFile

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


ad