URL Accessing in MS SQL Server- II

With URL Accessing you can query your MS SQL Server database from the address bar of your web browser

In the first part we saw how to create and query basic queries and also how to execute a simple stored procedure. In this part we will see how to access Database Objects, execute Template Files and XPath Queries.

               First let us see how we can execute Template Files. A Template is a well-formed XML document containing one or more SQL statements and/or XPath queries. Templates provide security, as the templates cannot be edited. For that, create a template file named MyTemplate.xml and type in the code below.

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">

  <sql:query>

      select * from emp Emp,dept Dept where Emp.deptno=Dept.deptno and      Emp.deptno=10 For XML AUTO

  </sql:query>

</ROOT>

               Now copy the MyTemplate.xml to \Inetpub\wwwroot\Master\Template directory of your web server and test it by typing the following in the address bar of your web browser.

http://localhost/masterVirDir/Template/MyTemplate.xml

              

                    The element <query> is defined in sql namespace (i.e , xmlns:sql) thus the namespace declaration is required. “sql” is only an alias, we can name it anything. The other elements available are <header>, <param>, <xpath-query>. The screenshot shows the result.

               You can also specify the templates directly   in the URL as

http://localhost/masterVirDir?Template=<ROOT+xmlns:sql="urn:schemas-microsoft-com:xml-sql"><sql:query>select+*+from+emp+Emp,dept+Dept+where+Emp.deptno=Dept.deptno+and+Emp.deptno=10+For+XML+AUTO</sql:query></ROOT>

               XML Path Language (XPath) is a graph navigation language. It is used to select a set of nodes from an XML document. XPath language is defined by the W3C as a standard navigation language. XPath Queries are executed against annotated XML-Data Reduced (XDR) schema, which is an XML view of the relational data. A detailed explanation of the language is out of the scope of this article. To query, create a file named MySchema.xml and type in the code below.

<?xml version="1.0" ?>

<Schema xmlns="urn:schemas-microsoft-com:xml-data"

        xmlns:dt="urn:schemas-microsoft-com:datatypes"

        xmlns:sql="urn:schemas-microsoft-com:xml-sql">

  <ElementType name="Emp" sql:relation="emp" >

    <AttributeType name="empno" />

    <AttributeType name="ename" />

    <AttributeType name="deptno" />

    <attribute type="empno" />

    <attribute type="ename" />

    <attribute type="deptno" >

   

    </attribute>

  </ElementType>

  <ElementType name="Dept" sql:relation="dept" sql:key-fields="deptno" >

    <AttributeType name="deptno" />

    <AttributeType name="dname" />

    <attribute type="deptno" />

    <attribute type="dname" />

 

    <element type="Emp" >

             <sql:relationship

                         key-relation="dept"

                         key="deptno"

                         foreign-relation="emp"

                         foreign-key="deptno" />

    </element>

  </ElementType>

</Schema>

               Now copy the MySchema.xml to \Inetpub\wwwroot\Master\Schema directory of your web server and test it by typing the following in the address bar of your web browser. The screenshot shows the result.

http://localhost/masterVirDir/Schema/MySchema.xml/Dept[Emp[@deptno=10]]?root=EmployeeDetails

              

               We can directly access Database Objects as well as shown. This is primarily used for retrieving binary data. More over the XPath query must identify a single row and a single column.

http://localhost/MasterVirDir/dbobject/emp[@deptno=30]/@ename

               We can even write Distributed Queries in the URL as shown. For more on Distributed Queries refer PCQuest April 2004 issue.

http://localhost/MasterVirDir?sql=select +*+from+openquery(mysqlDB,’select+*+from+mysql.title’)+as+title+for+xml+auto&root=BookDetails