execute powershell from SSMS

I am trying to execute as certain xp_cmdshell code:

declare @url varchar(250) = 'https://samples.openweathermap.org/data/2.5/weather?q=London,uk&appid=439d4b804bc8187953eb36d2a8c26a02'
declare @c varchar(1000) = N'powershell.exe -noprofile -executionpolicy bypass '
  + N'-command (Invoke-WebRequest -Uri '''+@url+'''-UseBasicParsing).content'
    print @c
exec xp_cmdshell @c

but this is the error that I get:

The string is missing the terminator: '.
    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : TerminatorExpectedAtEndOfString
 
'appid' is not recognized as an internal or external command,
operable program or batch file

when I try an execute the same thing in powershell I get the expected response(see image)

(Invoke-WebRequest -Uri 'https://samples.openweathermap.org/data/2.5/weather?q=London,uk&appid=439d4b804bc8187953eb36d2a8c26a02'-UseBasicParsing).content

Powershell

The expected output from the xp_cmdshell should be a json

Answer

The error message implies that your command line is being executed via cmd.exe, where & is a metacharacter.

To use & verbatim – as is your intent – either the URL of which & is a part must be enclosed in "..." (cmd.exe only recognizes double quotes) or you must ^-escape the &:

declare @url varchar(250) = 'https://samples.openweathermap.org/data/2.5/weather?q=London,uk^&appid=439d4b804bc8187953eb36d2a8c26a02'
-- ...

When cmd.exe parses the command line, it recognizes ^& as an escaped & to be used verbatim, and removes ^, the escape character, before passing the argument on.