Accessing SQL Server 2000 databases via HTTP

server| Access | data | One of the new features of database Microsoft SQL Server 2000 is that it supports XML, so users can access XML-enabled SQL Server2000 databases directly via HTTP. The implementation method is described below.

System environment: Windows2000 Server (contains IIS) + MS SQL Server2000

Database: Classic database sample included with SQL Server2000: NorthWind

A, configure IIS and SQL Server virtual domains;
1. New file directory

Open Windows Explorer, and under Inetpub\Wwwroot, create a new directory Northwind:

C:\Inetpub\wwwroot\northwind

Create a new 2 subdirectories under the Northwind directory: template and Schema:

C:\Inetpub\wwwroot\nwind\template, for placing XML templates;

C:\Inetpub\Wwwroot\nwind\schema, which is used to place the schema file.

2. Map Virtual directories for IIS

In the SQL program group, select Configure SQL XML Support in IIS, select New-> virtual path under the default site, and then make a five-step standard configuration.

(1) in the "General" page, enter the virtual path name "Northwind" and map to the actual hard drive path, as shown in the figure.


(2) in the "Security" page, select the appropriate login account number and password. In this case, the SYSTEM account SA is used, as shown in the figure.

(3) On the Data Source page, select the SQL Server name or IP address and select the database, as shown in the figure.

(4) on the Settings page, select Allow URL query, allow template query, allow XPath, and allow post, as shown in the figure.

(5) in the "Virtual Names" page, define template, schema, and DBObject, and specify the actual hard drive path, as shown in the figure.

Then, identify and close the window. The results are as shown.

Executing the query and stored procedure by URL method;
Now that we've set up SQL and IIS, let's start with a wonderful visit. First type the following address in IE browser:

Http://172.24.2.98/northwind?sql=select+*+from+customers+for+xml+auto&root=root

The results are as shown in the figure.


which

"Northwind" is the virtual path name;

"Select+*+from+customers" for SQL query statement "SELECT * FROM Customers"

The FOR XML statement represents the data format of the output as an XML document, not as a standard data row.

The "root" parameter is represented as a top-level element.

RAW, Auto, and explicit three types of XML Schemas are:

Raw to add a simple identification before each record, such as "<rowcustomerid=" ALFKI "..."

Auto will include the name of the datasheet before each record, such as "<customerscustomerid=" ALFKI ""

Explicit can be used to define the tree structure of XML.

After the initial success, we tried to execute the stored procedure using the URL address (Stored Procedure). Create a new stored procedure named CategoryInfo in SQL Query Analyzer, with the following code:

CREATE PROCEDURE CategoryInfo
As
SELECT CategoryName, Description from Categories
For XML AUTO

by address

Http://172.24.2.98/northwind?sql=execute+categoryinfo&root=root

You can execute the stored procedure.

Iii. Accessing XML template files
In addition, site developers can define templates for XML, define query conditions and result output formats:

For example, generate an XML document First.xml in the Wwwroot\northwind\template directory that contains a query statement with the following code:

<root xmlns:sql= "Urn:schemas-microsoft-com:xml-sql" >
<sql:query>
SELECT *
From Customers as Customer
For XML Auto
</sql:query>
</root>

Through the address Http://172.24.2.98/northwind/template/first.xml access, the results are obtained.

Combined with extensible Stylesheet Language (XSL), you can format the query results to make the report more beautiful.

From what has been described above, you may have a rudimentary understanding of SQL2000 and XML. Careful reading of SQL Help files and msdn.com will give you more help in mastering SQL2000.