Pass Greater Than or Lesser Than in XML

If you have a need to pass a greater than (>) or lesser than (<) symbol to a SQL stored procedure using a XML string, then this may help you...

Most of the stored procs that I work with in my work environment require me to pass data in to them using XML. This is not by personal choice but it is required to keep to standards set a loooong time ago.

The stored proc builds a query based on the WhereClause that is passed within the XML string.

Passing a where clause that contains the greater than (>) or lesser than (<) symbols will cause an error in your query because they are reserved as building blocks of an XML string. I get around this by using substitution characters like this...

Dim StartDate As String = "2012-12-01"

Dim EndDate As String = "2012-12-07"

Dim XML As New StringBuilder("Exec sp_ReturnData '<Contact Debug=""1"">")

XML.Append("<Record WhereClause="" Where ActionDate [gt]= '" & StartDate & "' And ActionDate [lt]= '" & EndDate & "'"" />")

XML.Append("</Contact>'")

In my stored proc there is a variable declared as @WhereClause which contanes the "WhereClause" that I pass in through the XML.

I perform a character substitution on @WhereClause like this...

Set @WhereClause = Replace(@WhereClause,'[lt]','<')

Set @WhereClause = Replace(@WhereClause,'[gt]','>')

And then I set the sql command and add @WhereClause to it...

Set @SQL = 'SELECT FirstName, Surname From MyTable '

Set @SQL = @SQL + @WhereClause

You can pick your own characters to substitute. Just make sure they aren't characters that you might use elsewhere, or the wrong characters will be substituted.

Do you use a different method to do this? Let me know in the comments.