Special Characters

Some characters have special meanings when they are used in a URL or in an XML document, and must be encoded properly for these meanings to take effect.

Special Characters in a URL

In queries executed at the URL, special characters are specified as %xx, where xx is the hexadecimal value of the character. The following table lists these special characters and describes their meanings. For more information, see the RFC1738 specification at http://www.faqs.org/rfcs/rfc1738.html.

For example, consider this query:

SELECT *
FROM Employees
WHERE EmployeeID=?

Because the ? character has a special meaning in the URL (separates the URL and the parameters being passed), it is encoded as %3F when this query is specified in the URL.

The following URL executes the query. In the URL, the parameter value is passed. For more information about executing SQL statement using HTTP, see Executing SQL Statements Using HTTP.

http://IISServer/nwind?sql=SELECT * FROM Employees WHERE EmployeeID=%3F FOR XML AUTO&root=root&EmployeeID=1

Any special character (such as a + character) to the right of a ? character is escaped by the browser (that is, a + character to the right of a ? is converted to %20).

Special Characters in XML

Characters such as the > and < characters are XML markup characters and have special meaning in XML. When these characters are specified in SQL queries (or an XPath queries), they must be properly encoded (also referred to as entity encoding). The following table lists these special characters and describes their meanings. For more information, see the XML 1.0 specification at XML 1.0 Specifications.

For example, consider this SQL query:

SELECT  TOP 2 * 
FROM      [Order Details] 
WHERE   UnitPrice > 10 
FOR XML AUTO

Because the > character has a special meaning in XML, it is encoded as &gt; when this query is specified in a template (an XML document) This is the template with the query:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <sql:query>
      SELECT top 2 * 
      FROM     [Order Details] 
      WHERE     UnitPrice &gt; 10 
      FOR XML AUTO
  </sql:query>
</ROOT>

For more information about templates, see Executing SQL Queries Using Templates and Executing XPath Queries Using Templates.

Entity Encoding Within URL Encoding

At times you may have to specify both the URL encoding and entity encoding. For example, this template can be specified directly in the URL (instead of specifying the file name):

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <sql:query>
      SELECT top 2 * 
      FROM     [Order Details] 
      WHERE     UnitPrice &gt; 10 
      FOR XML AUTO
  </sql:query>
</ROOT>

In this case, the & character in the entity encoding &gt; (specified for > markup character) has a special meaning in the URL and requires further encoding. The & character must be encoded as %26; otherwise it is treated as a parameter separator in the URL. The URL is then specified as:

http://IISServer/nwind?template=<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"><sql:query>SELECT TOP 2 * FROM [Order Details] WHERE UnitPrice %26gt; 10 FOR XML AUTO</sql:query></ROOT>

See Also

Accessing SQL Server Using HTTP

Retrieving XML Documents Using FOR XML

Using IIS Virtual Directory Management for SQL Server Utility