In-depth discussion of SQL Server 2000 support for XML (II)

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 in the address bar
Into the following url:http://localhost/northwind?sql=select+ *+from+customers+where+customerid= ' ANTON '
+for+xml+auto&root=root, if you are using a virtual directory alias that is not Northwind or you use a remote server, simply change the corresponding value
It's OK to drop it.
The browser will appear:
<?xml version= "1.0" encoding= "Utf-8"?
<root>
<customers customerid= "ANTON" companyname= "Antonio Moreno taquería" contactname= "Antonio Moreno"
Contacttitle= "Owner" address= "Mataderos 2312" city= "México D.F." Postalcode= "05023" country= "Mexico"
Phone= "(5) 555-3932"/>
</root>
Let's analyze This URL, "Http://localhost/northwind" followed by an SQL query to execute the query database
The task of Northwind. In this case, the query statement we use is "select+*+from+customers+where+customerid= ' ANTON '". Please note
Meaning, 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 browser to the database, off
Please refer to the documentation in the URL encoding format.
After the query statement, there are two new keywords added: FOR XML and Auto. The FOR XML keyword can perform SQL checks on an existing relational database
To return the form of an XML document. Auto mode returns the query result as a nested XML element, within the FROM clause, each in the SELECT clause to
Rarely a list of listed tables is represented as an XML element, and the columns listed in the SELECT clause map to the appropriate element attributes when the ELEMENTS option is specified
, the table columns are mapped to child elements rather than attributes. 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 put up
Face I give the example of the root parameter value set to Northwind, you will find that the returned XML file in addition to the root element name into Northwind, the
It has not changed.
Above we are talking about using HTTP to perform simple queries, and you can perform more complex queries, such as connecting different tables
Query, see the following example, in the following example, the SELECT statement connects the Customers and Orders tables in the Northwind database and returns the letter