XML and SQL Server 2000

2017-07-07 02:10:07

When we talked about XML documents in Chapter 1, we mentioned certain special characters that must be treated differently than other characters because they are interpreted differently depending on their location in documents. For more information, go to http://www.landfield.com/rfcs/rfc1738.html.

We must also be concerned now about special characters in URLs. Certain characters have a functionality all their own when used in URLs.

Entities in XML

The characters listed in Table 4.1 should not be used between tags in an XML document. These characters have special meaning to XML and will cause misinterpretation during parsing. The appropriate substitution entities that should be used in their place are provided in the table.

For a more thorough discussion of these characters and why they affect things the way they do, see RFC 2396. This RFC is freely available on the Internet at http://www.landfield.com/rfcs/rfc2396.html.

Table 4.1. Entity Substitutions

Character	Entity
& (ampersand)	Use `&`
' (apostrophe)	Use `'`
< (less than)	Use `<`
> (greater than)	Use `>`
" (quote)	Use `"`

Let's look at the sample template file in Listing 4.2. You'll see why these entities are necessary. Listing 4.3 shows the result.

Listing 4.2 Entities in Template Files

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:query> SELECT CustomerID, OrderDate, Freight FROM Orders WHERE Freight > 800  FOR XML AUTO </sql:query> </ROOT>

Listing 4.3 Results of Entity Substitution

Entities in URLs

When executing a query in a URL, you must be careful when using the characters listed in Table 4.2. They are interpreted according to the description in the table. All these characters are required at one point or another in the interpretation of URLs. Again, RFC 2396 discusses these characters in greater detail.

Table 4.2. Special Characters in URLs

Character	Description	Hexadecimal Value
+	Indicates a space (spaces cannot be used in a URL).	%20
/	Separates directories and subdirectories.	%2F
?	Separates the URL from the parameters.	%3F
%	Specifies special characters.	%25
#	Indicates bookmark anchors.	%23
&	Separates parameters specified in the URL.	%26

Here's an example of a direct SQL query in a URL:

http://IISServer/Nwind?sql=SELECT+*+FROM+Employees+WHERE+LastName+LIKE+ 'D%'FOR+XML+AUTO&root=root

Here we are trying to retrieve all information concerning employees whose last names start with D . Because the % character is one of the special characters for URLs, trying this query directly in a URL results in several errors. To fix the problem, you need to substitute the hexadecimal value of the special character in its place, like this:

http://IISServer/Nwind?sql=SELECT+*+FROM+Employees+WHERE+LastName+LIKE+ 'D%25'+FOR+XML+AUTO&root=root

There's one more point to make here. There are instances when it might become necessary to use a combination of XML and URL special characters. Look at the following sample template that could be specified directly in a URL. See if you can spot the problem.

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"><sql:query>SELECT+ CustomerID,OrderDate,Freight+FROM+Orders+WHERE+Freight+>+800+FOR+XML+AUTO </sql:query></ROOT>

Hopefully, by now you know that the > character won't work here, so we'll change it to the required XML entity.

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"><sql:query>SELECT+ CustomerID,OrderDate,Freight+FROM+Orders+WHERE+Freight+>+800+FOR+XML+ AUTO</sql:query></ROOT>

Did you make it this far? If so, good; but if you stopped here, you didn't go quite far enough. The & character is a URL special character, and a substitution needs to be made here also. Replace the & with the hexadecimal value %26 .

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"><sql:query>SELECT+ CustomerID,OrderDate,Freight+FROM+Orders+WHERE+Freight+%26gt;+800+FOR+XML +AUTO</sql:query></ROOT>