How do i pass params to SQL Templates?

[Padgett]

Hi There,

I'm trying to do an XML / XLS transform but am having trouble retrieving my
data. The trouble seems to be with the parameter - if I hard code the id in
the template SQL, everything works, but when I use .CreateParameter to set
the @ID value then i get an exception:

"Microsoft.Data.SqlXml.SqlXmlException: Exception from HRESULT:
0x80040E21. ---> System.Runtime.InteropServices.COMException (0x80040E21):
Exception from HRESULT: 0x80040E21. at
Microsoft.Data.SqlXml.Common.ISQLXMLCommandManaged Interface.ExecuteToOutputS
tream()"

The code looks like this:

Dim oTransform As New Xsl.XslTransform
Dim DataURL As String

Try
'Set response properties
With Response
.ContentType = "text/html"
'Get the data as XML
Dim ID As Integer
ID = Integer.Parse(Request.QueryString("ID"))
Dim oCmd As New SqlXmlCommand(CONN_STRING)
oCmd.CommandType = SqlXmlCommandType.TemplateFile
oCmd.CommandText =
Server.MapPath("./templates/Template.xml")
Dim oParam As SqlXmlParameter = oCmd.CreateParameter()
oParam.Name = "@ID"
oParam.Value = ID

'Transform
oTransform.Load(Server.MapPath("./templates/p7.xsl"))
Dim xDoc As New XPathDocument(oCmd.ExecuteXmlReader,
XmlSpace.Default)

Dim xNav As XPathNavigator = xDoc.CreateNavigator()
oTransform.Transform(xDoc, Nothing, Response.OutputStream,
Nothing)
.Flush()
End With

Catch ex As System.Xml.Xsl.XsltException
Throw New Exception("Doh", ex)
Catch ex As Exception
Throw New Exception("Doh", ex)
Finally
oTransform = Nothing
End Try

And the Template.xml file looks like:

<Plan xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:query>
SELECT Business.*,
FROM Business
WHERE (Business.BusinessID = @ID)
FOR XML AUTO, ELEMENTS
</sql:query>
</Plan>

The only thing the MS documentation has to say is:

"Note If you pass a parameter to a template, the parameter name must begin
with '@' (for example, p.Name="@EmployeeID", where p is a SqlXmlParameter
object)."

which doesn't help much as there isn't an example. (I tried this, but
cannot seem to get it to work)

Any help would be greatly appreciated.

If anyone has successfully passed a parameter to a query like this an
example would be wonderful!

Cheers,

Padgett Rowell