MS SQL XML - Using SQLXML 4 Template with Parameters

Author

Using SQLXML 4 Template with Parameters

EagleRed@HighFlyingBirds.com

2006-10-25, 6:06 am

I am writing an ASP.NET application that uses SQLXML templates to call stored
procedures with parameters. The sample is using the AdventureWorks DB. I
have written a simple stored procedure:

CREATE PROCEDURE dbo. dbx_GetCardholderDat
a
@ContactID int
AS
SELECT contact.ContactID, contact.FirstName, contact.LastName,
card.CardNumber, card.ExpMonth, card.ExpYear
FROM Person.Contact contact INNER JOIN
Sales.ContactCreditCard cc ON contact.ContactID = cc.ContactID INNER JOIN
Sales.CreditCard card ON cc.CreditCardID = card.CreditCardID
WHERE contact.ContactID = @ContactID FOR XML AUTO
RETURN

The template (GetCardholderData.xml):
<?xml version="1.0" encoding="utf-8" ?>
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
<sql:header>
<sql:param name='ContactID'></sql:param>
</sql:header>
<sql:query>
EXEC dbx_GetCardholderDat
a @ContactID
</sql:query>
</ROOT>

The code is implemented in a HTTP handler and is invoked with a URL similar
to:
GetXmlData.ashx? templ=GetCardholderD
ata.xml&ContactID=100

The templ parameter contains the name of the template file.
The ContactID parameter has the parameter value.

The invoking code is:

// The templates are located in the Templates subdirectory under the virtual
// directory.
string template = req. PhysicalApplicationP
ath + @"\Templates\"
+ req.QueryString["templ"];

string connString = WebConfigurationMana
ger.
ConnectionStrings["AWDB"].ConnectionString;
sqlCmd = new SqlXmlCommand(connSt
ring);
sqlCmd.CommandType = SqlXmlCommandType.Template;
sqlCmd.CommandText = template;
sqlCmd.ClearParameters();
sqlCmd.CommandStream = new FileStream(template,
FileMode.Open,
FileAccess.Read);
SqlXmlParameter parm;
for (int i=1; i < req.QueryString.Count; i++)
{
parm = sqlCmd.CreateParameter();
parm.Name = req.QueryString.Keys[i];
parm.Value = req.QueryString[i];
}
using (Stream strm = sqlCmd.ExecuteStream())
{
using (StreamReader sr = new StreamReader(strm))
{
context.Response.Write(sr.ReadToEnd());
}
}

When I run this code the code returns:
<?xml version="1.0" encoding="utf-8" ?>
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql" />

However, if I supply a default value, say 80, in the template, it returns
data.
<?xml version="1.0" encoding="utf-8" ?>
- <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
- <contact ContactID="80" FirstName="Edna" LastName="Benson">
<card CardNumber="33339360470691" ExpMonth="1" ExpYear="2006" />
</contact>
</ROOT>

How can I get the parameter value passed to the underlying stored procedure
from the code?

Any input would be appreciated.

EagleRed@HighFlyingBirds.com

2006-10-25, 6:06 am

A colleague pointed me to the resolution.

In settting up the SqlXmlParameter name it is necessary that the first
character of the parameter name be '@'. The problem "went away" by modifying
the statement,

parm.Name = req.QueryString.Keys[i];

to

parm.Name = "@" + req.QueryString.Keys[i];

The MSDN documentation and sample code does not point this out.

Hopes this helps posterity!

Enjoy,
Eagle

" EagleRed@HighFlyingB
irds.com" wrote:

> I am writing an ASP.NET application that uses SQLXML templates to call stored
> procedures with parameters. The sample is using the AdventureWorks DB. I
> have written a simple stored procedure:
>
> CREATE PROCEDURE dbo. dbx_GetCardholderDat
a
> @ContactID int
> AS
> SELECT contact.ContactID, contact.FirstName, contact.LastName,
> card.CardNumber, card.ExpMonth, card.ExpYear
> FROM Person.Contact contact INNER JOIN
> Sales.ContactCreditCard cc ON contact.ContactID = cc.ContactID INNER JOIN
> Sales.CreditCard card ON cc.CreditCardID = card.CreditCardID
> WHERE contact.ContactID = @ContactID FOR XML AUTO
> RETURN
>
> The template (GetCardholderData.xml):
> <?xml version="1.0" encoding="utf-8" ?>
> <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
> <sql:header>
> <sql:param name='ContactID'></sql:param>
> </sql:header>
> <sql:query>
> EXEC dbx_GetCardholderDat
a @ContactID
> </sql:query>
> </ROOT>
>
> The code is implemented in a HTTP handler and is invoked with a URL similar
> to:
> GetXmlData.ashx? templ=GetCardholderD
ata.xml&ContactID=100
>
> The templ parameter contains the name of the template file.
> The ContactID parameter has the parameter value.
>
> The invoking code is:
>
> // The templates are located in the Templates subdirectory under the virtual
> // directory.
> string template = req. PhysicalApplicationP
ath + @"\Templates\"
> + req.QueryString["templ"];
>
> string connString = WebConfigurationMana
ger.
> ConnectionStrings["AWDB"].ConnectionString;
> sqlCmd = new SqlXmlCommand(connSt
ring);
> sqlCmd.CommandType = SqlXmlCommandType.Template;
> sqlCmd.CommandText = template;
> sqlCmd.ClearParameters();
> sqlCmd.CommandStream = new FileStream(template,
FileMode.Open,
> FileAccess.Read);
> SqlXmlParameter parm;
> for (int i=1; i < req.QueryString.Count; i++)
> {
> parm = sqlCmd.CreateParameter();
> parm.Name = req.QueryString.Keys[i];
> parm.Value = req.QueryString[i];
> }
> using (Stream strm = sqlCmd.ExecuteStream())
> {
> using (StreamReader sr = new StreamReader(strm))
> {
> context.Response.Write(sr.ReadToEnd());
> }
> }
>
> When I run this code the code returns:
> <?xml version="1.0" encoding="utf-8" ?>
> <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql" />
>
> However, if I supply a default value, say 80, in the template, it returns
> data.
> <?xml version="1.0" encoding="utf-8" ?>
> - <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
> - <contact ContactID="80" FirstName="Edna" LastName="Benson">
> <card CardNumber="33339360470691" ExpMonth="1" ExpYear="2006" />
> </contact>
> </ROOT>
>
> How can I get the parameter value passed to the underlying stored procedure
> from the code?
>
> Any input would be appreciated.
>
>
>

Avner Aharoni [MSFT]

2006-10-28, 7:30 pm

Hi Eagle,

Thanks for pointing it out for us.
--
Thanks,
Avner Aharoni
Program Manager
[MSFT]

This posting is provided "AS IS" with no warranties, and confers no rights.

" EagleRed@HighFlyingB
irds.com" wrote:
[color=darkred]
> A colleague pointed me to the resolution.
>
> In settting up the SqlXmlParameter name it is necessary that the first
> character of the parameter name be '@'. The problem "went away" by modifying
> the statement,
>
> parm.Name = req.QueryString.Keys[i];
>
> to
>
> parm.Name = "@" + req.QueryString.Keys[i];
>
> The MSDN documentation and sample code does not point this out.
>
> Hopes this helps posterity!
>
> Enjoy,
> Eagle
>
> " EagleRed@HighFlyingB
irds.com" wrote:
>

Sponsored Links