In-depth discussion of SQL Server 2000 support for XML

Server|xml

[Author Introduction:]wayne, a new generation of programmers, 12-Year-old began to learn programming, 13-year-old with his own computer, has learned basic, Pascal, C, FOXPRO, VB, DELPHI, + +, SQL, learning the Java language, then become a crazy fan of Java , he is now studying at China University of Science and Technology.

Introduction
Content navigation
　　
* Introduction
* Configure IIS virtual directories for SQL Server 2000
* Execute SQL statement using HTTP
* Using XML templates for querying
* XPath query schemas and templates

We can simply use ADO to access the data in the database, however, if we want to display the data retrieved from the database in XML format, it will cost a bit of God, of course, we can find some ready-made applications or modify the existing stored procedures to complete the idea.

Although ADO2.5 claims to support XML, it still needs an extra layer to handle the process of translating data into XML. Fortunately, with SQL Server 2000, it claims to be able to extract data directly from the database without ADO2.5 the data directly to the XML presentation. This feature greatly improves the performance of applications that are constructed in distributed, datasets, because this feature eliminates unnecessary layers of code.

Let's take a look at what new features are added to support Xml,sql Server 2000:

1. Ability to access SQL Server using HTTP.

2. Supports XDR (XML data simplification) schemas and the ability to specify XPath queries for these schemas.

3. Ability to retrieve and write XML data:

Retrieves XML data using the SELECT statement and the FOR XML clause.

Writes XML data using the OPENXML rowset provider.

Retrieves XML data using the XPath query language.

4. Enhanced Microsoft SQL Server OLE DB provider (SQLOLEDB), which enables you to set the XML document to the command text and return the result set as a stream.

Visible, we can use SQL Server 2000来 to access XML-formatted data in several ways: first, queries executed in URLs can access SQL Server directly 2000 generate an XML document (you can also invoke an XML template that is stored on a Web server to generate an XML data file). Second, you can use the Select command and the FOR XML keyword to get XML data by calling a stored procedure or by using an XPath query. SQL Server 2000 fully supports the XDR (XML data simplification) schema, with the ability to map XML elements and attributes to tables and fields. Next, I'll explore the support features of SQL Server 2000 for XML.

An IIS virtual directory that configures SQL Server 2000

At the beginning of this article, I'd like to talk about configuring the IIS virtual directory for SQL Server 2000. SQL Server 2000 allows you to create a virtual directory for IIS that directly accesses data in an SQL database. Once you have installed SQL Server 2000 on a computer that has IIS configured, you can run the IIS Virtual Directory Management utility for SQL Server to configure the IIS virtual directory for SQL Server 2000.

OK, let's start the configuration process!

Click Configure SQL XML support in IIS in the SQL Server Tools program group, and an interface similar to IIS Manager appears. Expand the server, select the default Web site, right-click, select the new option from the pop-up menu, and then click Virtual Directory. The property page for the new virtual directory appears on the screen. On the General tab of the new Virtual Directory Properties dialog box, enter the name of the virtual directory, and in this case, enter the Northwind and physical directory path (for example, C:\Inetpub\Wwwroot\Northwind, assuming the C:\Inetpub\ The Northwind subdirectory has been created in the Wwwroot directory, but we can also use the Browse button to select the catalog. On the Security tab, fill in a valid SQL Server login information, and when you go to the next tab, it will ask you to confirm the password you just entered. On the Data Sources tab, enter the name of the server in the SQL Server box, and in the Database box, enter the name of Northwind as the default database. On the Settings tab, you can select the Allow URL query, allow template query, allow XPath, and allow POST options.

When building an application, you should not only take into account the ability to access the SQL Server database, but have enough security levels to keep your data secure. On the Virtual Name tab, you can choose a new template type (template), schema type (schema) and template and schema type (dbonject), and create their paths. OK, so we created the virtual directory Northwind. By default, the specified query is executed against the Northwind database using this virtual directory. You can't wait to absorb the high. What is the result of executing SQL? OK, let's type Http://localhost/northwind?sql=SELECT * from CUSTOMERS for XML Auto&root=root in the browser.

We can also program to implement IIS virtual directories that configure SQL Server 2000, see the following code:

Set Objxml = CreateObject ("Sqlvdir.sqlvdircontrol")

Objxml.connect ' Connect to the ' local computer and Web site ' 1 '

Set Objvdirs = Objxml.sqlvdirs

Set objVDir = objvdirs.addvirtualdirectory ("Northwind")

Objvdir.physicalpath = "C:\Inetpub\wwwroot\northwind"

Objvdir.username = "Wayne" SQL Server Login

Objvdir.password = "" ' SQL Server Password

Objvdir.databasename = "Northwind"

Objvdir.allowflags = 73

Set objvnames = Objvdir.virtualnames

Objvnames.addvirtualname "DBObject", 1, ""

Objvnames.addvirtualname "Schema", 2, "C:\Inetpub\wwwroot\northwind\schema"

Objvnames.addvirtualname "Template", 4, "C:\Inetpub\wwwroot\northwind\template"

Objxml.disconnect

MsgBox "Done."

Executing SQL statements using HTTP

Using the virtual directory we just created, we can execute the query by writing the SQL query statement to the URL. Open the browser and write the following url:http://localhost/northwind?sql=select+ *+from+customers+where+customerid= ' ANTON ' in the Address bar +FOR+XML+ Auto&root=root, if you are using a virtual directory alias that is not Northwind or you use a remote server, just change the value to the correct one.

The browser will appear:

Let's analyze This URL and "Http://localhost/northwind" followed by an SQL query to perform the task of querying the database Northwind. In this case, the query statement we use is "select+*+from+customers+where+customerid= ' ANTON '". Please note that this statement has been encoded by the URL, where the space is replaced by the Chengga number "+" so that it can be correctly transferred to the database browser, the URL encoding format, please refer to the relevant documentation.

After the query statement, there are two new keywords added: FOR XML and Auto. The FOR XML keyword can execute an SQL query on an existing relational database to return the form of an XML document. Auto mode Returns the result of the query as a nested XML element. Within the FROM clause, each table listed in at least one column in the SELECT clause is represented as an XML element, and the columns listed in the SELECT clause are mapped to the appropriate element attributes, and when the ELEMENTS option is specified, the table column map Shoot to a child element rather than an attribute. By default, AUTO mode maps table columns to XML attributes.

After the FOR XML auto, you also need to add a parameter "root" with the parameter value as the root element name of the returned XML file. For example, you can set the parameter value of root in the example I gave above to Northwind, and you will find that the returned XML file does not change except the root element name becomes Northwind.

Above we are talking about using HTTP to execute simple queries, but you can also perform more complex queries, such as connecting different tables for querying, see the following example, in the following example, the SELECT statement connects the Customers in the Northwind database and the Orders table, and returns information.

Http://localhost/northwind?sql=SELECT
Customer.customerid%2ccustomer.contact
name%2c%5border%5d. orderid+from+customers+
customer+inner+join+orders+%5border%
5d+on+customer.customerid%3d%5border%
5d. Customerid+for+xml+auto&root=northwind

Because the XML file returned is too long, I don't list it.

If you don't want nesting in the Customers table and Orders table, SQL Server 2000 also provides another keyword to replace auto, the keyword is raw. The RAW mode converts each row in the query result set to an XML element with the generic identifier row. To give you a deeper insight into raw, let me take another example: retrieving Customer and order information using RAW mode

The following query returns customer and order information. Specifies the RAW mode in the FOR XML clause.

SELECT Customers.CustomerID, Orders.OrderID, orders.orderdate
From Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerID
For XML RAW

Here are some of the results:

You can use an outer join to specify that the above query returns all customers in the result set, regardless of whether they have orders.

SELECT C.customerid, O.orderid, o.orderdate
From Customers C-left OUTER JOIN Orders O ' C.customerid = O.customerid order by C.customerid for XML RAW

Here are some of the results:

We can also execute stored procedures using HTTP, such as the following stored procedure named Getxml:

CREATE PROCEDURE GetXml
(
@CustomerID varchar (5)
)
As
BEGIN
SELECT CustomerID, Companyname,contactname
From Customers
WHERE CustomerID like @CustomerID + '% '
For XML AUTO
End

In order to execute this stored procedure and transmit the corresponding parameters, we can use the following section url,http://localhost/northwind?sql=exec+getxml+ ' A ' &root=root. In this way, we can use the stored procedures at a higher level and can dynamically change the parameter values according to the results the end-user wants (for example, in this case, we use "A").

Using XML templates for querying

The ability of SQL Server 2000 to embed SQL statements in HTTP requests is clearly powerful and useful. But this kind of seeing also hides the extremely hidden danger, once an end user understands the method that directly uses the browser to query the database, then the data in the database is very dangerous, because the end user may attempt to execute his own insert,update or even the delete process.

To maintain the security of data transactions in most databases, so that users are not able to query directly using URLs, SQL Server 2000 introduces the concept of XML templates, which can be set up by SQL Server virtual directories to guide XML templates that contain the required SQL procedures.

Before we discuss the template concept, let's go back to the IIS Virtual Directory Manager in SQL Server and go to the Settings tab. To prevent users from using HTTP access, we must remove the "Allow URL query" option. All SQL queries will now be directed to XML templates, XPath.

To allow an XML template to execute a SQL query, go to the Virtual Name tab, and click the New button, create a new template folder, name templates, and select Template from the Drop-down menu. Then, either enter a path where your XML template will be stored or click the "Browse" button. In this case, C:\Inetpub\wwwroot\xml\templates is used. Once you have provided all the necessary information, please click the "Save" button.

Now that a virtual directory has been mapped to a folder that is designated to hold the XML query template, let's create a valid XML template to execute the SQL query. The following code is an example of a template.

File2.xml

"Urn:schemas-microsoft-com:xml-sql" >

SELECT Customers.CustomerID, Customers.contactname,
Orders.OrderID, Orders.CustomerID
From Customers
INNER JOIN Orders
On customers.customerid = Orders.CustomerID
For XML AUTO

This code uses a prefix named SQL and a URI urn:schemas-microsoft-com:xml-sql that identifies the elements that are used on the SQL Server XML ISAPI. There is an element named query, which, as the name suggests, is used to mark the SQL query statement in the template file. OK, let's show you how to use this template! Please enter in the Address bar, Http://localhost/northwind/templates/file2.xml, of course, you can also change the corresponding server name and virtual directory name according to your needs.

Let's split this URL into separate fragments for analysis, and as you can see, we first use the Northwind virtual root and then use the Templates virtual directory name, as we said before that the virtual directory name has been mapped to the templates physical directory. Finally, the URL gives the name of the template file. By executing this template, the browser will display different orders nested under the Customers element in the table as XML documents.

There are many advantages to using a template without using a URL query. First, now that an end user doesn't have the power to change the SQL statement, the option to remove the "Access to SQL Server server via URL query" is available, and only SQL Server XML ISAPI can be used to process template files, which prevents unauthorized inserts, updates, and deletes from being executed. Second, XML templates support dynamic addition of parameters, which allows you to change the value of an SQL WHERE clause without changing the template file.

Using parameters, as simple as inserting an XML header element, defines a PARAM element in the header element, using a name attribute with a value of CustomerID. This parameter is given a default value of "A", you can use this parameter in the template file as you would in a stored procedure, just add an @ before this parameter, and then put it in the SQL statement or use it to invoke a stored procedure. See the code below.

a


SELECT Customers.CustomerID, Customers.contactname,
Orders.OrderID, Orders.CustomerID
From Customers
INNER JOIN Orders
On customers.customerid = Orders.CustomerID
WHERE Customers.CustomerID like @CustomerID + '% '
For XML AUTO

In this case, the CustomerID parameter is used by a WHERE clause. If the argument is set to "B", the SQL statement returns all rows from the Customers and Orders tables that start with B. customerid. Call the template and pass the correct CustomerID parameter values, as long as you add parameter names and parameter values after the query string, such as: Http://localhost/northwind/templates/file2.xml?CustomerID=B, it's simple.

Iv. XPath query schemas and templates

An XPath query can also be embedded into an XML template file, and the following code is a simple XML template file that contains an XPath query.

"Urn:schemas-microsoft-com:
Xml-sql ">
"File4.xdr" >
/customer[@CustomerID =
' ALFKI ']/order

This query uses a schema (schema) to return all orders for the user with the CustomerID number ALFKI, and if you want the XPath statement to run, you must map the different XML elements and attributes to the corresponding database table and field names using an XDR schema file. This schema file is given below.

>
xmlns:dt= "Urn:schemas-microsoft-com:datatypes"
Xmlns:sql= "Urn:schemas-microsoft-com:xml-sql" >

"Idrefs" sql:id-prefix= "ord"/"

"Orders" sql:field= "OrderID"
key-relation= "Customers"
key= "CustomerID"
foreign-relation= "Orders"
foreign-key= "CustomerID"/>

key-relation= "Customers"
key= "CustomerID"
foreign-relation= "Orders"
foreign-key= "CustomerID"/>

"id" sql:id-prefix= "ord"/>

key-relation= "Orders"
key= "OrderID"
Foreign-relation= "[Order Details]"
foreign-key= "OrderID"/>

key-relation= "Orders"
key= "EmployeeID"
foreign-relation= "Employees"
foreign-key= "EmployeeID"/>

"[Order Details]"
sql:key-fields= "OrderID ProductID"
sql:id-prefix= "prod-"/>

Sql:relation= "[Order Details]"/>

sql:id-prefix= "emp-"/>

If you want to learn more about the schema file, refer to the user documentation for SQL Server 2000 or the later articles that await me.

As with SQL query statements embedded in XML template files, XPath query statements use URN:SCHEMAS-MICROSOFT-COM:XML-SQL and SQL as prefixes to identify custom elements and attributes used in the template, and for XPath queries, We use an element named Xpath-query to identify the query syntax, which also has a property named Mapping-schema that identifies the path to the schema file that maps tables and fields to a particular XML project.

The following code gives another template file that uses more complex XPath queries.

"Urn:schemas-microsoft-com:xml-sql" >
"Listing4.xdr" >
/customer[@CustomerID =
' ALFKI ']/order/
employee[@LastName = ' Suyama ']

When the template file is executed, the XPath query returns the name of the employee (employee) who signed the order with a customer, and the result is as follows:

"Urn:schemas-microsoft-com:xml-sql" >
Lastname= "Suyama"
Firstname= "Michael"
Title= "Sales
Representative "/>

XPath queries used in template files can also use parameters, much like using parameters in an XSL style sheet. Like XSL, use $ to specify a variable. The following code shows how to consolidate variables in a template file that contains XPath queries.

{{Should this is ' Listing6.xdr '?}}
/customer/order[@OrderID = $ID]

By passing parameter names and corresponding parameter values in the URL, we can complete the operation of passing parameters to the template. The results are as follows:

"6" orderdate= "1997-08-25t00:00:00" requireddate=
"1997-09-22t00:00:00" shippeddate=
"1997-09-02t00:00:00" >
"Suyama" firstname= "Michael" title=
"Sales representative"/>
"45.6" quantity= "15"
0.25

"Quantity=" "21" >
0.25

"Quantity=" "2" >
0.25

Summary

By using a few of the techniques I've described above, we can get XML data directly from the SQL Server 2000 database. As I've described, URL queries, XML template files, XDR schemas, and XPath queries provide powerful capabilities to get XML data directly from SQL Server 2000. In addition, there are a number of important concepts, due to space limitations in this article can not be detailed, such as for XML explicit query and OPENXML these technologies, I will further discuss in future articles, please wait.

This paper turns from http://dev.csdn.net/article/84813.shtm