As far as you have an error on MS SQL Server side you can use MS SQL Profiler to determine what exactly is executing. (Btw, XML syntax differs from MS SQL 2005 to MS SQL 2008. I used 2008 one.) Unfortunately, XsltDb has no logging for the moment.
You say you tried hardcoded Teams. Did you use something like this?
declare @xml xml;
set @xml = '<root><Team>1</Team><Team>2</Team></root>';
select Team, UrlWebsite from TeamsPerClub where Team in (
select x.value('.', 'nvarchar(max)') from @xml.nodes('//Team') t(x)
If code above succeed, you must look at value of $teams variable.
Try the following:
<xsl:value-of select="$teams" />
This code outputs value of $teams variable so you can see what you pass to the MS SQL.
It seems you are using XsltDb for DNN 4, It eat all SQL errors...