Integrating Web Services and SQL Server 2000 XML

As you know, XML is no longer a simple markup language that programmers use to unclutter data on the Web. Instead, XML has become the basis for standards, such as SOAP and Web services. These standards will soon become the basis for the architecture used to develop the next generation of distributed applications.

To integrate XML with the application that uses SQL Server 2000, you can use SQLXML 3.0. This toolkit allows SQL Server 2000 to provide a more robust and varied support for XML than its built-in tools and commands. We will now discuss the SQLXML 3.0 package and its features in detail.

Introduction to SQLXML 3.0

As discussed earlier, SQLXML 3.0 uses SQL Server 2000 to expose Web services that use the SOAP messaging techniques. SOAP enables your applications to act as a client of the Web services that SQL Server exposes. Web services that are created by using SQLXML 3.0 extend support for the execution of stored procedures, user-defined functions, and templates.

Another key feature of SQLXML 3.0 is the managed classes that are exposed within the .NET Framework. These classes extend the ability to access XML data from Microsoft SQL Server from within the .NET environment.

In this project, you will learn how to expose a stored procedure as a Web service and how to access the Web service. You also will learn how to use the SQMLXML managed classes to execute SQL queries from the .NET applications.

Accessing SQL Server Data Through the Web

Before proceeding to work with the SQLXML 3.0 toolkit, you will need the following software:

  • SQL Server 2000
  • Microsoft SOAP Toolkit 2.0
  • SQLXML3.0
  • MSXML Parser 4.0 (comes with SQLXML 3.0)

In this section, you will learn how to access data in an SQL Server database by using XML and Internet Explorer.

Querying the Database by Using XML Templates

To access the data in a SQL Server database, you first need to create a virtual directory and a few support files. You can do this in two ways, as mentioned in the following list:

  • Using the Internet Services Manager from the Administrative tools
  • Using the IIS Virtual Directory Management for SQLXML 3.0 Microsoft

Management Console (MMC) snap-in Because the IIS Virtual Directory Management for SQLXML 3.0 is designed for creating virtual directories for hosting XML templates and stored procedure-based Web services, in this section, we will discuss the second way of creating a virtual directory. These utilities install automatically with SQLXML 3.0 from the SQLXML 3.0 program group. To run the MMC snap-in utilities, perform the following steps:

  1. Create a folder on the local hard disk and name it SqlXmlProj1.
  2. In the SqlXmlProj1folder, create a subfolder called templates that contains the XML templates.
  3. Select Configure IIS Support from the program group. The IIS Virtual Directory Manager for SQLXML 3.0 starts, as shown in Figure.
  4. The IIS Virtual Directory Manager.

    The IIS Virtual Directory Manager.

  5. Click on the machine name in the tree view to expand the tree structure.
  6. Right-click on Default Web Site and select New, Virtual Directory in the resulting menus. The New Virtual Directory Properties window is displayed, as shown in Figure.
  7. The New Virtual Directory Properties Window.

    The New Virtual Directory Properties Window.

  8. In the dialog box that appears under the General tab, enter the name of the virtual directory. In this case, enter the name for the virtual directory as SqlXmlProj1.
  9. In the text box for the local path, enter the path of the folder that you created in step 1.
  10. Click on the Security tab to display the dialog box, as shown in Figure
  11. The Security Tab.

    The Security Tab.

  12. In this dialog box, enter the login credentials for connecting to the SQL Server. Depending on the configuration of the server, you can use an SQL Server account, the Windows integrated security, or both to access the data in SQL Server.
  13. Click on the Data Source tab. The dialog box that appears is shown in Figure.
  14. The Data Source Tab.

    The Data Source Tab.

  15. Specify the name of the SQL Server you need to use or select the name of the server from the drop-down list.
  16. Uncheck the Use Default Database for Current Login check box.
  17. Select the name of the database you will connect to. For this example, select the Northwind database, which is a sample SQL Server database.
  18. Click on the Settings tab to display the dialog box shown in Figure
  19. The Settings Tab.

    Settings tab to display the dialog box

  20. In this dialog box, select the Allow Sql, Allow Template Queries, and Allow POST check boxes.
  21. Click on the Virtual Names tab. The dialog box that appears is shown in Figure
  22. The Virtual Names Tab.

    The Virtual Names Tab

  23. In the Name text box, type templates and select the templates folder that you created in step 1.
  24. Click on the Save button.
  25. Click on the Advanced tab to display the dialog box shown in Figure
  26. The Advanced Tab.

    The Advanced Tab

  27. In this dialog box, ensure that the path of the ISAPI DLL that is required for processing your query is specified in the text box. If the path is not specified by default, enter the path of the ISAPI DLL that you plan to use.
  28. Note:If the ISAPI extension DLL that you plan to use is correctly installed and registered, the path in the dialog box will appear correctly.

  29. Click on the OK button to close the window.

After performing the preceding steps, you can access the virtual directory that you created and configured by using the Internet protocols. The next step in accessing the data from an SQL Server database is to create an XML template, which you can access by using a browser.

The content of the template file is shown in the following code:

The preceding code displays the contents of the Employees table in the Northwind database. Save the file as emp.xml in the templates folder.

The next step is to test the SQLXML installation by connecting to the virtual directory that you have created. To do this, you can use the http:// localhost /SqlXmlProj1 /templates /emp.xml URL. The emp.xml file, as shown in Figure is displayed.

Querying Through a Template.

Querying Through a Template

Creating a Web Service

Next, you will create a Web service that accesses data from a database and returns some values. In this case, you will create a Web service that exposes the functionality of a stored procedure called Ten Most Expensive Products from the Northwind sample database. To create a Web service, perform the following steps:

  1. In the SqlXmlProj1 folder that contains the templates subfolder, create another folder, Soap. You will use this folder to store the files that the Web service requires.
  2. On the Start menu, in the SQLXML group, click on the Configure IIS Support option.
  3. Select the name of the virtual directory that you have created from the list view on the right.
  4. Right-click the name of the virtual directory and select Properties to open the Virtual Directory Properties window.
  5. Click on the Settings tab and ensure that the Allow POST check box is selected.
  6. Click on the Virtual Names tab. Enter the name as Soap.
  7. Select the type as SOAP.
  8. The Web Service Name and the Domain Name text boxes are activated. Enter the name for the Web service and the domain.
  9. Click on Save. The Configure button is activated.
  10. Click on the Configure button. The Soap Virtual Name Configuration dialog box is displayed, as shown in Figure.
  11. The Soap Virtual Name Configuration Dialog Box.

    The Soap Virtual Name Configuration Dialog Box.

  12. Select SP as the type that specifies the stored procedures.
  13. Click on the Ellipsis button (…) to the right of the SP/Template text box.
  14. The SOAP Stored Procedure Mapping dialog box is displayed, as shown in Figure.

    The SOAP Stored Procedure Mapping Dialog Box.

    The SOAP Stored Procedure Mapping Dialog Box.

  15. Select the Ten Most Expensive Products stored procedure and click on the OK button.
  16. Click on the OK button to close the Virtual Directory Properties window.
  17. Check the contents of the Soap folder. It contains two files, as discussed in Table.
  18. Files in the Soap Folder

    Files in the Soap Folder

The following code displays the contents of the .wsdl file for the Web service:

The Web service that the preceding .wsdl file supports contains only a single Web method called Ten_Most_Expensive_Products(). The .wsdl file also specifies the location where you can connect to the Web service.

After looking at the .wsdl file, consider the contents of the .ssc file, as shown in the following code snippet:

Notice that the preceding file connects the Web service method to the stored procedure.

Testing the Web Service

To test a Web service, create a client application that accesses the Web service. In this case, you will create a Visual Basic 6.0 client application that makes use of the SOAP Toolkit to connect to the Web service.

The client application contains a Button control. When you clic on the Button control, the application connects to a Web service and returns the required data. The returned data is in the form of an XML node list. You then need to parse the XML node list, which is displayed in a message box.

To create a client application in Visual Basic 6.0, perform the following steps:

  1. Create a desktop application in Visual basic 6.0.
  2. From the Reference dialog box, add a reference to the MS SOAP type library and MSXML 4.0 type library.
  3. Add a button to the form.
  4. In the Click event handler of the Button control, add the following code:

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

XML Topics