1 / 58

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

brady-boyer
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 www.NovickSoftware.com .Net Programmatic Access to SQL Server 2000 XML

  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. www.NovickSoftware.com .Net Programmatic Access to SQL Server 2000 XML

  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.comHome of the Transact-SQL User-Defined Function of the Week www.NovickSoftware.com .Net Programmatic Access to SQL Server 2000 XML

  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, they’re going out of business) www.NovickSoftware.com .Net Programmatic Access to SQL Server 2000 XML

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

  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 www.NovickSoftware.com .Net Programmatic Access to SQL Server 2000 XML

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

  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 www.NovickSoftware.com .Net Programmatic Access to SQL Server 2000 XML

  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 www.NovickSoftware.com .Net Programmatic Access to SQL Server 2000 XML

  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 www.NovickSoftware.com .Net Programmatic Access to SQL Server 2000 XML

  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 www.NovickSoftware.com .Net Programmatic Access to SQL Server 2000 XML

  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 www.NovickSoftware.com .Net Programmatic Access to SQL Server 2000 XML

  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 www.NovickSoftware.com .Net Programmatic Access to SQL Server 2000 XML

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

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

  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 www.NovickSoftware.com .Net Programmatic Access to SQL Server 2000 XML

  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 www.NovickSoftware.com .Net Programmatic Access to SQL Server 2000 XML

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

  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 www.NovickSoftware.com .Net Programmatic Access to SQL Server 2000 XML

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

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

  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 www.NovickSoftware.com .Net Programmatic Access to SQL Server 2000 XML

  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 www.NovickSoftware.com .Net Programmatic Access to SQL Server 2000 XML

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

  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 www.NovickSoftware.com .Net Programmatic Access to SQL Server 2000 XML

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

  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. www.NovickSoftware.com .Net Programmatic Access to SQL Server 2000 XML

  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. www.NovickSoftware.com .Net Programmatic Access to SQL Server 2000 XML

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

  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) www.NovickSoftware.com .Net Programmatic Access to SQL Server 2000 XML

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

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

  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 www.NovickSoftware.com .Net Programmatic Access to SQL Server 2000 XML

  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 www.NovickSoftware.com .Net Programmatic Access to SQL Server 2000 XML

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

  37. Demo: SQLXMLCommand • .Net Solution SQLXMLDemonstrations • .Net Project SQLXMLCommandDemo • Illustrates • Using SQLXMLCommand www.NovickSoftware.com .Net Programmatic Access to SQL Server 2000 XML

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

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

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

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

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

  43. SQLXMLCommand XSLT • Transforms XML to HTML or other text www.NovickSoftware.com .Net Programmatic Access to SQL Server 2000 XML

  44. SQLXMLCommand XPath • CustomerInvoices • CustomerInvoices/Customer[@state="CA"]/Invoice Requires a Mapping Schema www.NovickSoftware.com .Net Programmatic Access to SQL Server 2000 XML

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

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

  47. Demo: Diffgram • .Net Solution SQLXMLDemonstrations • .Net Project SQLXMLDiffgramInsert • Illustrates • SQLXMLAdapter • Diffgram www.NovickSoftware.com .Net Programmatic Access to SQL Server 2000 XML

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

  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

More Related