.Net Programmatic Access to SQL Server 2000 XML

1
.Net Programmatic Access to SQL Server 2000 XML

• Andrew Novick

Boston .Net User Group
April 9, 2003
2
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

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

4
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.com Home of the
  Transact-SQL User-Defined Function of the Week

5
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, theyre going out of business)

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

8
Sample XML?

• ltrootgt
• ltpet speciesGuniea Piggt
• ltnamegtVioletlt/namegtltagegt1lt/agegt
• lt/petgtltpetgtltnamegtRodricklt/namegtltagegt3lt/agegtlt/
  petgt
• lt/rootgt

9
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

10
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

11
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

12
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

13
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

14
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

15
Sample OPENXML

• Schema is up to you.

ltupdategt ltAuthors au_id"238-95-7766"
au_lname"Ismore" au_fname"Les"/gt ltAuthors
au_id"427-17-2319" au_lname"More"
au_fname"Bill"/gt lt/updategt
16
Sample OPEN XML
CREATE PROC usp_Update_AuthorNames _at_AuthorData
text AS DECLARE _at_hDoc int exec
sp_xml_preparedocument _at_hDoc OUTPUT, _at_AuthorData
UPDATE Authors SET Authors.au_fname
XMLEmployee.au_fname,
Authors.au_lname XMLEmployee.au_lname
FROM OPENXML(_at_hDoc, 'update/Authors')
WITH Authors XMLEmployee WHERE
Authors.au_id XMLEmployee.au_id -- free
any memory consumed by the document EXEC
sp_xml_removedocument _at_hDoc
17
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

18
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

19
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 .Nets
  SQLXMLCommand
• Used by Visual Basic 6, VBScript, JavaScript. or
  other COM consumer

20
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

21
Web Based Access to SQL XML

• Uses IIS to achieve Web Access
• Installs its own ISAPI filter
• Configuration Tool allows setting up sites and
  controlling access
• Web Services supported in SQLXML 3.0

22
Web Formats

• SQL SELECT FROM FOR XML
• Templates
• SQL FOR XML Query
• SQL Queries with Parameters
• Multiple Queries
• Web Service/SOAP

23
Demo Web Based Queries

• Files
• IIS Based Queries.txt
• CustomersOrders.xml
• CustomerOrders.XML
• MultipleQueries.XML
• Authors2.XSD
• Illustrates
• IIS based queries
• Templates
• Xpath Query

24
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

25
Mapping Schema Authors2.xsd
lt?xml version"1.0"?gt ltxsdschema
xmlnsxsd"http//www.w3.org/2001/XMLSchema"
xmlnssql"urnschemas-microsoft-commapping-schem
a"gt ltxsdelement name"Authors"
sqlrelation"Authors"gt
ltxsdcomplexTypegt ltxsdsequencegt
ltxsdelement name"ID" type"xsdstring"
sqlfield"au_id" /gt
ltxsdelement name"FirstName"
type"xsdstring"
sqlfield"au_fname" /gt
ltxsdelement name"LastName" type"xsdstring"
sqlfield"au_lname" /gt
lt/xsdsequencegt
lt/xsdcomplexTypegt lt/xsdelementgt
lt/xsdschemagt
www.NovickSoftware.com .Net Programmatic Access
to SQL Server 2000 XML
26
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

27
Format of an UpdateGram
ltROOT xmlnsupdg"urnschemas-microsoft-comxml-up
dategram"gt ltupdgsync mapping-schema
"AnnotatedSchemaFile.xml" gt ltupdgbeforegt ...
lt/updgbeforegt ltupdgaftergt ... lt/updgaftergt
lt/updgsyncgt lt/ROOTgt
28
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.

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

30
.Net Programmatic Access

• ADO.Net and XMLReader class on a FOR XML query
• ADO.Nets Dataset Uses XML to represent its
  contents as a Diffgram.
• SQLXML includes .Net Managed Classes
• Consume Web Services

31
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)

32
Demo ADO.Net to Retrieve XML

• .Net Solution SQLXMLDemonstrations
• .Net Project XMLTextReaderExample
• Illustrates
• Referencing Microsoft.Data.SqlXML
• For XML Query
• Using XMLTextReader

33
ADO.Net Dataset Object

• Uses XML as an internal representation
• No SQLXML involved
• Methods
• GetXML
• GetXMLSchema
• InferXMLSchema
• ReadXML
• WriteXML

34
Demo Write XML From a Dataset

• .Net Solution SQLXMLDemonstrations
• .Net Project SQLXMLDataSetDemo
• Illustrates
• ADO.Net SQLCommand
• Persisting XML from a Dataset to a file

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

36
SQLXMLCommand Class

• Properties control how the class behaves
• ExecuteStream method executes the command and
  returns a stream object

37
Demo SQLXMLCommand

• .Net Solution SQLXMLDemonstrations
• .Net Project SQLXMLCommandDemo
• Illustrates
• Using SQLXMLCommand

38
SQLXMLCommand.CommandType

• Dialects
• T-SQL SQLXMLCommandType.SQL
• Templates SQLXMLCommandType.Template
• Template File SQLXMLCommandType.TemplateFile
• Xpath SQLXMLCommandType.XPath

39
Templates

• Can be sent to SQL Server many ways including via
  SQLXMLCommand
• Contain
• SQL Queries
• UpdateGrams
• DiffGrams

40
Demo Template

• .Net Solution SQLXMLDemonstrations
• .Net Project SQLXMLCommandTemplate
• Illustrates
• Using a template to provide the query to
  SQLXMLCommand

41
SQLXMLCommandXML Formatting

• Root .RootTag root
• Output Encoding UDF-8, UNICODE,
  etc. .OutputEncoding UDF-8
• NameSpaces .NameSpaces
  xmlnsrdfhttp//www.w3.org/TR/WD-rdf-syntax

42
SQLXMLCommandFile Management Properties

• .BasePath Top-level path to XML files
• .SchemaPath
• .XSLPath
• Paths can be either
• File Paths
• URLs

43
SQLXMLCommand XSLT

• Transforms XML to HTML or other text

44
SQLXMLCommand XPath

• CustomerInvoices
• CustomerInvoices/Customer_at_state"CA"/Invoice
• Requires a Mapping Schema

45
SQLXMLAdapter

• Like the SQLAdapter
• Acts an an intermediary between the SQLConnection
  and Dataset objects

46
Demo SQLXMLAdapter

• .Net Solution SQLXMLDemonstrations
• .Net Project SQLXMLAdapterExample
• File Authors.XSD
• Illustrates
• SQLXMLAdapter
• Xpath Query
• Mapping Schema

47
Demo Diffgram

• .Net Solution SQLXMLDemonstrations
• .Net Project SQLXMLDiffgramInsert
• Illustrates
• SQLXMLAdapter
• Diffgram

48
Client Side Programming

• SQL Server Web Release 2 and above
• SQLXMLOLEDB Provider moves the work of formatting
  XML to the client.

49
Server Based XML Creation
www.NovickSoftware.com .Net Programmatic Access
to SQL Server 2000 XML
50
Moving the Work to the Client
www.NovickSoftware.com .Net Programmatic Access
to SQL Server 2000 XML
51
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

52
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

53
Consuming SQL XML Web Services

• Web Services are created by exposing
• Stored Procedures
• User Defined Functions
• templates
• Consumed like any other Web Service

54
Configure IIS Support Tool

• Configure soap virtual name
• Add stored procedures and UDFs as web methods

55
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

56
SQL Server 2000 XML Distilled

• Curlingstone an Imprint of Wrox
• Download code from the book atwww.Curlingstone.co
  m
• Andrew Novick
• anovick_at_NovickSofware.com
• 978-440-8126
• www.NovickSoftware.com
• Consulting - Project Management Design -
  Programming - Training

57
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

58
Thanks for coming!