Jump to content
  • 0

Dynamic parameters



I'm attempting to create a dynamic query string and for some reason an & returns amp; on the first row returned from select.



(SELECT  ABC = STUFF((SELECT  ' &' + CAST(param AS nvarchar) + '=' + CAST(AnswerID AS nvarchar) FROM _v_AnswerList    WHERE AnswerGroupID = target.[@field:AnswerGroupID]FOR XML PATH ('')), 1, 1, ''))



I want the result to be 


Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0

Hello @RandyHVM,

You may check this external post https://stackoverflow.com/questions/35894546/avoid-encoding-of-ampersand-in-sql-xml-attribute
This post describes the cause of this behavior. 

Could you specify the goal of using FOR XML PATH clause? Do you need to return multiple records? 
What is the next step to use the result in a query string? 

Link to comment
Share on other sites

  • 0

Hi Copper, thanks for the response.

I was looking for a way to loop through  multiple records in an SQL statement to produce dynamic parameters to pass to a page.

The target page would then use those parameters for handling input based on the parameters. The target page will most likely also be dynamic, just trying to get through the parameters first.

Example /TargetPage/?recid12345&sars=HFUFB1T2&age=10FLLCRC&risk=AU2YIIL9

I'll try the suggestions for casting xml in the post you provided.  I'm new to Caspio Calculated fields and have been stumbling with the syntax. 

Also, the example code above was incorrect, it has a leading space prior to the &, this space should have been removed to produce the issue.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...