XML and the SQL 2000 Server, Part 2 - Using Select Queries in URL
(Page 3 of 7 )
In sending SQL commands to a URL, the command is sent in the form of a Query String named sql to the virtual root. For example, to send the following query (a + replacing a white space ):
Select *
from authors
for xml auto
The string can be constructed as follows:
?sql=select+*+from+authors+for+xml+auto&root=root
Here,
?sql= represents a valid SQL statement to be executed by the SQL Server, Select+*+from+authors+xml+auto is the statement
and &root=root returns the XML as well formed XML by enclosing the SQL Server returned fragment inside <root></root>.
For example, by sending the following request to the default site XPHTEK and virutal root PubsVirtual:
http://xphtek/PubsVirtual/?sql=select+*+from+authors+for+xml+auto&root=root
The following result is displayed on the browser. Only part of the result is shown here.
Running this query in Netscape 7.2 adds an extra comment: "The XML file does not appear to have any style information attached with it."
Using Select Query with a Where Clause In most business activities there is seldom a necessity to return all rows from the database. Filtering the required data and sending it to the client is the best way.Here is a filtered query and the result:
http://xphtek/PubsVirtual/?sql=select+*+from+authors+where+au_lname=
'Bond'+for+xml+auto&root=root
It is also possible to send the same query as follows to obtain the same result:
http://xphtek/PubsVirtual/?sql=select%20*%20from%20authors%20where%20au_lname='Bond'%20for%20xml%20auto&root=root
Stored procedure from a URL It is also possible to access a stored procedure from a URL. Consider the following stored procedure on the SQL Server in th pubs database. Since a stored procedure needs to be executed, the object, namely MySimpleXML2, must be configured such that the user accessing the procedure has the permissions.
Create procedure MySimpleXML2
@State char(2)
as
Select stor_name, city, zip, state
from stores
where state=@State
order by city
for XML auto
The following query can be used to access this stored procedure and the result is shown in the next picture. http://xphtek/PubsVirtual/?sql=MySimpleXML2+'CA'&root=root
Next: Considerations When Sending Queries to a URL >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
