net programmatic access to sql server 2000 xml
Download
Skip this Video
Download Presentation
.Net Programmatic Access to SQL Server 2000 XML

Loading in 2 Seconds...

play fullscreen
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

.Net Programmatic Access to SQL Server 2000 XML

Andrew Novick

Boston .Net User Group

April 9, 2003

agenda
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
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
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
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
The Cover

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

so what is 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
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
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
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
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
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
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
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
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
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 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
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
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
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
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
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
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
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
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
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
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
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
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
.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
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
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
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
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
.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
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
Demo: SQLXMLCommand
  • .Net Solution SQLXMLDemonstrations
  • .Net Project SQLXMLCommandDemo
  • Illustrates
    • Using SQLXMLCommand

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

sqlxmlcommand commandtype
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
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
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
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
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
SQLXMLCommand XSLT
  • Transforms XML to HTML or other text

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

sqlxmlcommand xpath
SQLXMLCommand XPath
  • CustomerInvoices
  • CustomerInvoices/Customer[@state="CA"]/Invoice

Requires a Mapping Schema

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

sqlxmladapter
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
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
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
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
Server Based XML Creation

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

moving the work to the client
Moving the Work to the Client

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

sqlxmloledb provider
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
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
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 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
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
SQL Server 2000 XML Distilled
  • Curlingstone an Imprint of Wrox
  • Download code from the book atwww.Curlingstone.com
  • Andrew Novick

Consulting - Project Management – Design - Programming - Training

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

transact sql udf of the week
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
Thanks for coming!

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

ad