Editing the RedGate_SqlPrompt_Engine_EngineOptions.xml file causes error

Bodders

I am using SQL Prompt 10.14.12.7945 on an Azure SQL Server DB in the cloud. There are 126 tables and 52 views to which I want to assign unique aliases. I know I can add them manually in the Options form, but instead I would like to generate the XML for them in VBA, and add that to the XML file : RedGate_SqlPrompt_Engine_EngineOptions.xml.

However, when I save the updated file using Notepad++ or VS2022 and then restart SSMS it comes up with this error message “Data at the root level is invalid. Line 1, position 1”.

Can anyone suggest why this is, and how to get around it, as the XML is validated.

TIA for any help

ATurner

Hi Bodders

For this example below


it is stored as 

  <UserDefinedAliases>&lt;UserDefinedAliases&gt;
  &lt;Count&gt;1&lt;/Count&gt;
  &lt;UserDefinedAlias&gt;
    &lt;ObjectName&gt;Facility&lt;/ObjectName&gt;
    &lt;Alias&gt;Fac&lt;/Alias&gt;
  &lt;/UserDefinedAlias&gt;
&lt;/UserDefinedAliases&gt;</UserDefinedAliases>

You can close SSMS, manually edited the XML directly to add another entry and then restart SSMS (just be sure to increase the count as well when you drop in another encoded XML alias chunk):

  <UserDefinedAliases>&lt;UserDefinedAliases&gt;  &lt;Count&gt;2&lt;/Count&gt;
  &lt;UserDefinedAlias&gt;
    &lt;ObjectName&gt;Facility&lt;/ObjectName&gt;
    &lt;Alias&gt;Fac&lt;/Alias&gt;
  &lt;/UserDefinedAlias&gt;
  &lt;UserDefinedAlias&gt;
    &lt;ObjectName&gt;abtest&lt;/ObjectName&gt;
    &lt;Alias&gt;abt&lt;/Alias&gt;
  &lt;/UserDefinedAlias&gt;
&lt;/UserDefinedAliases&gt;</UserDefinedAliases>

Bodders

Thanks for that post - it was useful, but I am still having the same problem. I think it may be to do with the line breaks - it seems to need them but when I add them in VBA that seems to cause the problem
Bodders

ATurner

Hi Bodders

It will need to be in that exact format, or it will not work. 
However, I cannot speak for if VBA would add these.