Small query - removing XML tags
If you do not want the XML Tags, then do not add them!

This code frustrates me because:


you have not given any indication as to what this code should be doing.
you are embedding presentation-layer logic in the database layer
you convert all the data to an XML document, and then strip all the XML tags from the result because this appears to be a 'cheap' way to get all the data as a single result instead of multiple rows.
you do not document this
you use less-common features of the database (XML manipulation) without good reason, which locks you in to a single provider for the database ;-)
there is no documented reason for why you strip the last character from the @result... this will strip the closing &gt; from the last &lt;br /&gt;, and will thus leave some invalid X?HTML? I presume? A bug?


As for some nit-picks:


You should not be using SELECT @Result = (...), but should, instead be using SET @Result = (...) See the documentation for SELECT.
you capitalize some of your query key-words, but not all. I recommend using whatever case you do not use for your columns. In your case, your db.schema.table.columns are all lower/CamelCase, so your keywords should all be full CAPITALS. Thus, you should have SELECT everywhere. You have one SELECT, and one Select. You have one LEFT, and two replace. Your consistency of convention should be improved.
you do not use table-aliases for your query objects. Not only do table-aliases make the SQL more readable, but, if you need to change a table name for some reason (e.g. to get data from a view, or different schema), then you have to change multiple places, and could lead to bugs....


As far as I am concerned, this function should be removed entirely, and a simple presentation-layer system should be added that simply appends a &lt;br /&gt; after each bond description from the query:

SELECT uC.Description
FROM Justice.dbo.uCode uC
INNER JOIN Justice.dbo.xBondCondition xBC
        ON uC.CodeID = xBC.ConditionID
WHERE xBC.BondID = ?