1 / 33

Web Services with SQLXML 3.0: Integration Guide

Learn how to build web services with SQL Server and SQLXML 3.0. Explore SQLXML architecture, data access methods, and web service creation steps. Take advantage of XML technology for SQL Server integration.

mark-barnes
Download Presentation

Web Services with SQLXML 3.0: Integration Guide

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. DAT 330Building Web Services with SQL Server and SQLXML 3.0 Alexander Vaschillo Lead Program Manager WebData XML Group Microsoft Corporation

  2. SQLXML Talks • SQLXML: XML Technology for SQL Server • DAT200, Alexander Vaschillo, Jul 2 8:30 • Building Web Services with SQL Server and SQLXML 3.0 • DAT330, Alexander Vaschillo, Jul 2 18:15 • Inside data access with SQLXML: Architecture guide • DAT400, Alexander Vaschillo, Jul 3 10:00

  3. Agenda • SQLXML Overview • SQL Server2000 Web Services Overview • What can be exposed • What does SQLXML 3.0 do for you • How to create a Web Service • Setting up a Web Service • Available Options • Support for Visual Studio.NET • Demo

  4. XPath XQuery XML Files XML View XML/ HTML SQLXML – Bringing worlds together XML world The Two Worlds SQL Language SQL Server Data storage RowSet Data output Relational world

  5. Query/Update Technologies • FOR XML (raw, auto, nested, explicit) • SQL language extension to retrieve XML instead of rowsets • XML Views – XPath, XQuery • Work with your relational database as if it was XML file (through annotated schema) • Updategrams/Diffgrams • Update through XML View • Bulkload • Shred large XML files into existing tables

  6. Data Access Methods • HTTP Access via URL • SQL Query • XPath Query • XML Templates • ADO / OLEDB • .NET access through SQLXML Managed Classes • Web Services

  7. SQLXML 3.0 • Introduces Web Services for SQL Server • Turn your SQL Server into a Web Service in 15 minutes • Released at Visual Studio.NET Launch, February 2002 • Localized to 8 languages at launch • Integral part of Microsoft’s .NET vision

  8. Web Services Overview • Web Services expose Stored Procedures and XML Templates • Messaging done according to SOAP 1.1 standard • Variety of response options • Can run on database server or mid-tier • Wherever IIS is • Simple UI for configuration • Even DBA’s can do it!

  9. What can be exposed? • XML Templates • XML returned from template is simply returned from the Web Service • Stored Procedures • Relational data converted to XML • XML Data is returned • Output Parameters also returned • UDF’s- Visible like a store procedure

  10. What does SQLXML do for you? • Creates Web Services Description Language (WSDL) file • Describes Web Service • Indicates what Methods are available • Difficult to write by hand. WE DO IT FOR YOU! • Parses SOAP requests • Returns proper SOAP response

  11. How is it done? • Admin creates a Web Service and adds Web Methods via a simple UI • Web Methods map to Stored Procedures and Templates • WSDL file is created • Client retrieves WSDL via URL • http://doman/vDir/vName?wsdl • Client now knows the methods available • Method names • Parameters • Return Values

  12. How is it done (continued)? • Client application sends SOAP encoded request (Procedure call) • POST to SOAP vName • SQLXML parses request • Method Name • Parameters • XML Template or Stored Procedure is called • Client has no idea that this is a template or stored procedure, or even that it’s SQL Server

  13. How is it done (continued)? • Proper SOAP encoded response is constructed and returned to the client • For methods mapping to XML Templates, results are already XML • Results are wrapped in SOAP envelope and returned • For methods mapping to stored procedures, results are processed • Results are converted to XML according to options chosen • XML is wrapped in SOAP envelope and returned

  14. SQL Server 2000 SP’s Architecture SOAP VName Virtual Directory Web Server WSDL Method Config / Mapping File Method Method XML Templates

  15. Creating a Web Service • Use the configuration tool to create a Virtual Directory (VDir) • Create a Virtual Name of type “SOAP” • Name your Web Service • Give it a domain You Have a Web Service! Now to add some methods…

  16. XML Template Methods • Browse for an XML Template • All templates available on the SQLXML tier • Choose Web Method name • Results always returned in “XML Objects” mode

  17. Stored Procedure Methods • Select Stored Procedure from list • Generated from login used for the VDir • Choose Web Method name • Select Output Formatting • RAW or NESTED • Select Response Type • XML Objects • Dataset Objects • Single Dataset

  18. demo Web Service Creation

  19. Response Types • All Responses are XML / SOAP • Response Type dictates what Visual Studio.NET De-Serializes the XML into • Other clients (Microsoft SOAP Toolkit) will just see XML • Methods mapping to XML Templates have a fixed return type • XML Objects

  20. XML Objects Responses • Visual Studio.NET recognizes an “array of objects” • Each item in the array is de-serialized into an object • XMLElement - Data • SQLMessage – Error Information • Templates MUST use this response type

  21. Dataset Objects Responses • Visual Studio.NET recognizes an “array of dataset objects” • Each item in the array is de-serialized into an object • Dataset - Data • SQLMessage – Error Information • Only for Stored Procedures

  22. SQLMessage • Conveys Error information • Object Contains the following: • Created directly from SQL Server Error Messages

  23. Single Dataset Responses • Visual Studio.NET recognizes a Dataset • Retrieve results of a Stored Procedure and load into a Dataset in 1 line of code! Dim Service As New MyHost.MyWebService() Dim ds As New System.Data.DataSet() Dim retval As Integer ds = Service.GetCustomerInfo(“ANTON”, retval)

  24. Single Dataset Response Limitations • No Error Information • Either a dataset comes back or it doesn’t, no other messages • Single Result Sets Only • Use Dataset Objects mode for multiple result sets

  25. Visual Studio.NET Integration • SQLXML 3.0 Web Services designed for Visual Studio.NET • In a Visual Studio.NET Project, “Add Web Reference” to your Web Service • Proxy classes generated for your methods • Forget that you are not accessing local information • Visual Studio.NET takes care of the SOAP messaging and XML serialization • Intellisence recognizes SPs and Templates and their parameters

  26. demo Visual Studio.NET Web Service Client

  27. Visual Studio.NETGotchas • Value-Typed Parameters • No NULL values • No Missing/Empty Parameters • Value-Typed Return Values • No NULL Values • Data Munging in Dataset Only Mode • Special Characters in Table Names

  28. Call to Action • Web Services Toolkit • Download from • http://www.microsoft.com/sql/techinfo/xml • Turn your SQL Server into a Web Service Today! • Let us know what your think

  29. Additional Resources • Web page • http://www.microsoft.com/sql/techinfo/xml/ • Download SQLXML releases at: • http://msdn.microsoft.com/sqlxml/ • Contact us • PSS • news:microsoft.public.sqlserver.xml • Email us • Documentation • SQL Server 2000 Documentation (Books Online) • Web Release documentation • Books

  30. Books About SQLXML • “Professional XML Databases”Various; Wrox Press; December, 2000 • “Scripting XML and WML for Microsoft SQL Server 2000”, Tobias Martinsson; John Wiley & Sons; January, 2001 • “Programming Microsoft SQL Server 2000 with XML”Graeme Malcolm; Microsoft Press; June, 2001 • “Professional SQL Server 2000 XML”Various; Wrox Press; June, 2001 • “XML and SQL Server 2000”John Griffin; New Riders Pub; July, 2001 • “Working With Microsoft SQL Server and XML”Sams Pub; January, 2002

  31. Resources from Microsoft Press SQL & XML • For more information please visit the TechEd Bookshop. • www.microsoft.com/mspress

  32. Don’t forget to complete the on-line Session Feedback form on the Attendee Web sitehttps://web.mseventseurope.com/teched/

More Related