Parameterizing the XPath for a modify()

Hi all,

I'm trying to write a generic stored procedure which will parameterize the XPath in my XML file so that I can update a value by giving just its path and the new value I wish to store:

Here is the code:
CREATE PROCEDURE ModifyLoanXML
@.LoanNumber char(60),
@.XPathQuery varchar(300),
@.Value varchar(300)
AS
UPDATE Loans SET LoanXML.modify('replace value of (sql:variable("@.XPathQuery"))[1] with xs:string(sql:variable("@.Value"))')
WHERE Loans.LoanNumber = @.LoanNumber
GO

Unfortunately I am getting the following error:
Msg 2337, Level 16, State 1, Procedure ModifyLoanXML, Line 6
XQuery [Loans.LoanXML.modify()]: The target of 'replace' must be at most one node, found 'xs:string ?'

I've tried a number of things to try to get this to go through but I'm having no luck. Is it possible to completely parameterize the XPath you wish to change when calling XQuery.modify() ?

Is there any way to specify that my path will always point to an attribute (not a node) so that this proc can be created? Thanks!

-Karthik Hariharan

The path specified in modify() must be a string literal, so the approach you are taking will not work out. The sql:variable("@.XPathQuery") is not interpreted as a path, but as a string value.

You can use dynamic sql to achieve what you are looking for. SInce you will be generating dynamic SQL, you will need to take steps to ensure that you dont end up with SQL injection from accepting untrusted XPaths.

|||Thanks Todd. I was trying to avoid the dynamic SQL method precisely to avoid any SQL injection vulnerabilities. Is there no way to achieve this dynamix XQuery using a stored procedure? If anyone else has a suggestion please let me know. Thanks.
|||I resolved the issue by using an Exec() T-SQL command. Here is my code below:

CREATE PROCEDURE [dbo].[ModifyLoanXML]
@.LoanID UNIQUEIDENTIFIER,
@.XPathQuery varchar(max),
@.Value varchar(max)
AS
DECLARE @.query varchar(max)
DECLARE @.LoanIDstr varchar(max)
SET @.LoanIDStr = CONVERT (varchar(max),@.LoanID)

SET @.query='UPDATE Loans SET LoanXML.modify(''declare namespace MISMO="http://mrgdev.local/mismo/";replace value of ' + @.XPathQuery + ' with "' +@.Value +'"'') WHERE Loans.InternalID = ''' + @.LoanIDstr + ''''
exec(@.query)

To avoid a possible SQL injection, I parameterized the LoanID and convert it to a varchar within the stored procedure. Just wanted to share this with you all.

Regards,
Karthik Hariharan
|||

Hi Karthik,

Your code still appears to be subject to SQL injection attacks with respect to the XPathQuery variable and the Value variable, if either is untrusted. Since you are concatenating them with the SQL string, if an untrusted user was able to specify the XPathQuery or Value variable, then they may be able to embed quotes and comment characters to change the behavior of your query. You can change your query to parameterize both Value (using sql:variable) and LoadIDstr (using a parameter) but accepting untrusted XPathQuery will be difficult without fully validating that it is safe.

Parameterizing SQL connection

I'd like to know what's the best practice in parameterizing SQL connection in SSIS so that we can move from Dev to QA and to Production easily.

Thanks,

Tommy

Hi Tommy,

I'm not sure if it's a "Best Practice" but I'm using Indirect configuration files to store connection information on different servers.

Kirk Haselden has an article called "Keep your packages in the dark" in the November 2005 edition of SQL Server Magazine, which I found extremely helpful.

|||

Using configurations is certainly best practice, whether indirect or not. You can find more information on configurations here: http://msdn2.microsoft.com/en-us/library/ms141682.aspx

When using Books Online, please remember that you can score and comment on the content at the foot of each page. This helps us to improve the content continuously.

There's also a whitepaper here which may help ... www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx

Donald

Parameterizing page break in a group

We would like to have the Page Break for a group be based on a Yes/No
Parameter that would be a the parameter section of the report. In the
Grouping and sorting options where the "Page Break at End" check box is,
there is no way to enter an expression. Is there any way to do this without
writing code, and/or what would be the code that would make the Page Break
conditional?I think this will work though I haven't tried it. Put a Rectangle in the
bottom of your group footer with PageBreakAtEnd set to true. Then set the
Rectangle.Hidden expression (under visiblility) to =not
cbool(Parameters!PageBreaks.Value) assuming you have a parameter called
PageBreaks.
"Alec Hardy" <AlecHardy@.discussions.microsoft.com> wrote in message
news:E730E694-6B20-4A4A-86A8-2BF05EF0DB84@.microsoft.com...
> We would like to have the Page Break for a group be based on a Yes/No
> Parameter that would be a the parameter section of the report. In the
> Grouping and sorting options where the "Page Break at End" check box is,
> there is no way to enter an expression. Is there any way to do this
> without
> writing code, and/or what would be the code that would make the Page Break
> conditional?|||Thanks Bob, we're going to look into it.
"Bob Fisher" wrote:
> I think this will work though I haven't tried it. Put a Rectangle in the
> bottom of your group footer with PageBreakAtEnd set to true. Then set the
> Rectangle.Hidden expression (under visiblility) to =not
> cbool(Parameters!PageBreaks.Value) assuming you have a parameter called
> PageBreaks.
> "Alec Hardy" <AlecHardy@.discussions.microsoft.com> wrote in message
> news:E730E694-6B20-4A4A-86A8-2BF05EF0DB84@.microsoft.com...
> > We would like to have the Page Break for a group be based on a Yes/No
> > Parameter that would be a the parameter section of the report. In the
> > Grouping and sorting options where the "Page Break at End" check box is,
> > there is no way to enter an expression. Is there any way to do this
> > without
> > writing code, and/or what would be the code that would make the Page Break
> > conditional?
>
>|||Can anyone verify if this worked?
I tried entering and expression that would evaluate to true or false in the
element, but it did not work:
<PageBreakAtEnd>=CBool(iif(Parameters!GroupBreak1.Value = "true", true,
false))<PageBreakAtEnd>
Thanks!!!!
-Brian
"Alec Hardy" wrote:
> Thanks Bob, we're going to look into it.
> "Bob Fisher" wrote:
> > I think this will work though I haven't tried it. Put a Rectangle in the
> > bottom of your group footer with PageBreakAtEnd set to true. Then set the
> > Rectangle.Hidden expression (under visiblility) to =not
> > cbool(Parameters!PageBreaks.Value) assuming you have a parameter called
> > PageBreaks.
> >
> > "Alec Hardy" <AlecHardy@.discussions.microsoft.com> wrote in message
> > news:E730E694-6B20-4A4A-86A8-2BF05EF0DB84@.microsoft.com...
> > > We would like to have the Page Break for a group be based on a Yes/No
> > > Parameter that would be a the parameter section of the report. In the
> > > Grouping and sorting options where the "Page Break at End" check box is,
> > > there is no way to enter an expression. Is there any way to do this
> > > without
> > > writing code, and/or what would be the code that would make the Page Break
> > > conditional?
> >
> >
> >

parameterizing multi valued parameter in data set

Hi,

I am creating a data set for a ROLAP report which executes a dynamic SQL using EXEC. I am finding problems parameterizing multi-valued report parameters in the SQL being executed through EXEC.

To consider a sample code:

EXEC('select * from country where country_key in ('+@.country+')')

where @.country is a multi-valued report parameter.

Regards,

Emil

google is a wonderful thing
http://groups.google.co.uk/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/9d9739713fe89f5f/11b4fc22ce2e9e0b?lnk=st&q=multi+value+parameter+%22reporting+services%22&rnum=3#11b4fc22ce2e9e0b|||

Hi,

i am facing similar problem. I was trying dynamic SQL for the performance of the report.

But got stuck on multivalued parameter......

For me

Declare @.sql varchar(4000), @.rcc varchar(4000)

set @.rcc='a'; -- now if more than one value will be passed to it, this query will fail, it will execute but return nothing.

select @.sql='SELECT * FROM abc '

select @.sql=@.sql + 'where abc_column in (''' + @.rcc + N''')'

Exec( @.sql)

I am not sure creating a function and using it to split the parameter values finally result as a plus or minus to the performance.

If any one has any other way of doing this, Pls help.

|||and did you bother reading the link?|||

Try this

create another parameter new_param_country which is single valued

make it hidden

give value as

="'" & Join(Parameters!country.Label, "', '") & "'"

Give the same expression for default value too

For eg:- if the countries selected are FRANCE, INDONESIA and ICELAND, the value of the new parameter will be 'FRANCE', 'INDONESIA', 'ICELAND' which is a string

Use this parameter in the dataset instead of the multivalued parameter.

Regards,

Meenu

|||

Thanks a lot Meenu. :-)

Works fine for me. Just did a little change to the expression

=Join(Parameters!country.Value, ", ")

as I needed only value.

Regards,

Emil

|||

This is exactly what I've been looking for to put all if the multi-value parameter values in the page header.

Thanks!

-Marianne

parameterizing multi valued parameter in data set

Hi,

I am creating a data set for a ROLAP report which executes a dynamic SQL using EXEC. I am finding problems parameterizing multi-valued report parameters in the SQL being executed through EXEC.

To consider a sample code:

EXEC('select * from country where country_key in ('+@.country+')')

where @.country is a multi-valued report parameter.

Regards,

Emil

google is a wonderful thing
http://groups.google.co.uk/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/9d9739713fe89f5f/11b4fc22ce2e9e0b?lnk=st&q=multi+value+parameter+%22reporting+services%22&rnum=3#11b4fc22ce2e9e0b|||

Hi,

i am facing similar problem. I was trying dynamic SQL for the performance of the report.

But got stuck on multivalued parameter......

For me

Declare @.sql varchar(4000), @.rcc varchar(4000)

set @.rcc='a'; -- now if more than one value will be passed to it, this query will fail, it will execute but return nothing.

select @.sql='SELECT * FROM abc '

select @.sql=@.sql + 'where abc_column in (''' + @.rcc + N''')'

Exec( @.sql)

I am not sure creating a function and using it to split the parameter values finally result as a plus or minus to the performance.

If any one has any other way of doing this, Pls help.

|||and did you bother reading the link?|||

Try this

create another parameter new_param_country which is single valued

make it hidden

give value as

="'" & Join(Parameters!country.Label, "', '") & "'"

Give the same expression for default value too

For eg:- if the countries selected are FRANCE, INDONESIA and ICELAND, the value of the new parameter will be 'FRANCE', 'INDONESIA', 'ICELAND' which is a string

Use this parameter in the dataset instead of the multivalued parameter.

Regards,

Meenu

|||

Thanks a lot Meenu. :-)

Works fine for me. Just did a little change to the expression

=Join(Parameters!country.Value, ", ")

as I needed only value.

Regards,

Emil

|||

This is exactly what I've been looking for to put all if the multi-value parameter values in the page header.

Thanks!

-Marianne

Parameterizing BULK INSERT

I have a statement in a stored procedure as follows - which works a dream.
BULK INSERT cashpost
FROM 'c:\carparkfines\data\cashpost.txt'
WITH
(
FORMATFILE = 'c:\carparkfines\data\cashpost_format.txt'
)
However I want to parameterize the two file paths.
I know how to get them as follows
select @.filename01 = (SELECT ImportCashFilePath FROM tblSystem WHERE RecNo =
1)
However TSQL objects if I write
BULK INSERT cashpost
FROM @.filename01
WITH
(
FORMATFILE = @.filename02
)
So who is going to tell the new boy how to do it? Thanks in anticipation.
Andy Willis
Use some dynamic SQL, e.g.
DECLARE @.Sql nvarchar(4000)
SET @.Sql = '
BULK INSERT cashpost
FROM ''' + @.filename01 + '''
WITH
(
FORMATFILE = ''' + @.filename02 + '''
)
'
PRINT @.Sql
EXEC(@.Sql)
Darren Green
http://www.sqldts.com
"Andy Willis" <andrewrwillis@.blueyonder.co.uk> wrote in message
news:%23gqfKAlaEHA.2908@.TK2MSFTNGP10.phx.gbl...
> I have a statement in a stored procedure as follows - which works a dream.
> BULK INSERT cashpost
> FROM 'c:\carparkfines\data\cashpost.txt'
> WITH
> (
> FORMATFILE = 'c:\carparkfines\data\cashpost_format.txt'
> )
> However I want to parameterize the two file paths.
> I know how to get them as follows
> select @.filename01 = (SELECT ImportCashFilePath FROM tblSystem WHERE RecNo
=
> 1)
> However TSQL objects if I write
> BULK INSERT cashpost
> FROM @.filename01
> WITH
> (
> FORMATFILE = @.filename02
> )
> So who is going to tell the new boy how to do it? Thanks in anticipation.
> Andy Willis
>

Parameterizing Allow Nulls Columns

Here's a question I though would be common but can't find an answer to!

My select statement, which pulls from SQL Server tables, has a column which allows nulls. When I try to add a parameter to this column, it no longer returns rows that have null in that column when I test it with the default value of %. I want it to return all the records.

SELECT Jobs.JobID, Jobs.JobName, Engineers.Engineer FROM Jobs LEFT OUTER JOIN Engineers ON Jobs.AccountManager = Engineers.ID WHERE (Engineers.Engineer = @.Engineer)

Parameter is;
ConvertEmptyStringToNull = True
DefaultValue = %
Direction = Input
Name = Engineer
QueryStringField = Engineer
Size = 0
Type = Empty

When I run this I get no records! Isn't % suppose to return anything including nulls?

Isn't % suppose to return anything including nulls?

No. % is only valid for the LIKE operator, and even then it will not return nulls.

SELECT Jobs.JobID, Jobs.JobName, Engineers.Engineer FROM Jobs LEFT OUTER JOIN Engineers ON Jobs.AccountManager = Engineers.ID WHERE (Engineers.Engineer = @.Engineer) OR (@.Engineer='%')

|||

This works and I thank you much but the thickness of my skull is prohibiting me from absorbing why it work.

WHERE (Engineers.Engineer = @.Engineer)
if a parameter is not provided - the default of % is used and this returns every value accept for nulls. How does it treat zero length strings?
if a parameter is provided - it returns the rows that match the parameter

OR (@.Engineer='%')
if a parameter is not provided - ?
if a parameter is provided - ?

I don't understand how this connects to Engineers.Engineer.

|||

WHERE (Engineers.Engineer = @.Engineer)
if a parameter is not provided, the default of % is used and only rows in which engineer contains EXACTLY % will be returned. I assume you have no engineers that contain exactly %, so no rows will be returned.

If a parameter is provided, it returns the rows that match the parameter.

OR (@.Engineer='%')
if a parameter is not provided, the default of % is used and % always is equal to %. So it will match on every row.

if a parameter is provided (Assuming of course the parameter isn't %), then it will never match.

Recap

WHERE (Engineers.Engineer = @.Engineer) OR (@.Engineer='%')

No parameter: WHERE (FALSE) OR (TRUE)

simplified: WHERE TRUE

Parameter: WHERE (Possibly TRUE -- if they match) OR (FALSE)

simplified: WHERE (Possibly TRUE -- if they match)

Make any sense now?

|||

Perfect Sense. Thank you! So I'm guessing the use of a wildcard really doesn't work in ASP.NET or Sql Server? In Dreamweaver I can provide a string parameter of '%' and it will return all rows including nulls. The WHERE clause is Field LIKE '%'.

|||

Yes, the like operator accepts wildcards, but the equals operator does not.

WHERE Field LIKE '%' will find all fields that have 0 or more characters (NULL is not 0 or more characters, it's NULL).

WHERE Field='%' will find all fields that contain one character, and that one character must be the percent symbol.

|||LIKE '%' returns NULL fields in SQL Server and in my Dreamweaver application but not in my asp.net application. I just posted a different thread asking why it doesn't work in asp.net.