RandyHVM Posted November 29, 2022 Report Share Posted November 29, 2022 I'm attempting to create a dynamic query string and for some reason an & returns amp; on the first row returned from select. Calculation (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, '')) Produces amp;sars=HFUFB1T2&age=10FLLCRC&risk=AU2YIIL9 I want the result to be &sars=HFUFB1T2&age=10FLLCRC&risk=AU2YIIL9 Quote Link to comment Share on other sites More sharing options...
0 CoopperBackpack Posted November 30, 2022 Report Share Posted November 30, 2022 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? Quote Link to comment Share on other sites More sharing options...
0 RandyHVM Posted November 30, 2022 Author Report Share Posted November 30, 2022 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. Quote Link to comment Share on other sites More sharing options...
Question
RandyHVM
I'm attempting to create a dynamic query string and for some reason an & returns amp; on the first row returned from select.
Calculation
(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, ''))
Produces
amp;sars=HFUFB1T2&age=10FLLCRC&risk=AU2YIIL9
I want the result to be
&sars=HFUFB1T2&age=10FLLCRC&risk=AU2YIIL9
Link to comment
Share on other sites
2 answers to this question
Recommended Posts
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.