How to handle dynamic XMLNAMESPACE

• dndaughtery

  SSChampion

  Points: 11290

  More actions

  April 3, 2019 at 1:29 pm

  #2685063

  I have a situation where an XMLNAMESPACE has a URL that needs to be dynamic. Its being used with an insert into a table from an XML parameter passed in my stored procedure as the source.

  Setting a variable and placing it in the XMLNAMESPACE generates an error

  ;WITH XMLNAMESPACES(DEFAULT 'http://www.microsoft.org/2005/Atom', @Base_Namespace, 'http://schemas.microsoft.com/ado/2007/08/dataservices' AS d, 'http://schemas.microsoft.com/ado/2007/08/dataservices/metadata' as m)

  I tried building the Entire Insert Statement concatenating the dynamic url but then I get an error saying that the sp_ExecSQL needs the XML parameter defined.

  Is there a way to make the URL dynamic in the XMLNameSpace?

• dndaughtery

  SSChampion

  Points: 11290

  More actions

  April 3, 2019 at 5:46 pm

  #2703444

  Figured out to do it. I passed the XML in as a parameter

  EXEC sp_ExecuteSQL @Dynamic_Insert, N'@XMLResponse XML', @XMLResponse = @XMLResponse