sql-serverssmssql-server-2019for-xml-pathstring-agg

SQL Server Management Studio: Policy - ExecuteSql throws error if the statement string contains an empty field

I want a SQL Server Management Policy to check the values in a delimited list returned by FOR XML. The problem is that "FOR XML ('')" will throw a SQL error.

I may not post screenshots or site-specific details so I will demonstrate the error using a simple example:

  1. In SQL Server Management Studio, open Object Explorer, expand Management, expand Policy Management, expand Policies to see the existing policies.

  2. Right click Policies and choose New Policy...

  3. Give it a name: List Databases

  4. Click Check Condition and then New Condition

  5. Name the condition: List Databases

  6. Click the ellipsis button to the right of the Expression Field

  7. Type in this statement:

    ExecuteSql('String','SELECT name + '', '' FROM sys.databases FOR XML PATH')

  8. Click OK

  9. In the Value field, enter the database names separated by a comma and space: 'master, tempdb, model, msdb, database1, database2, '

  10. Operator is '='

  11. Click OK

  12. Click OK

Now right-click and evaluate the policy. It's not true because the XML has delimiters:

'<row>master, </row><row>tempdb, </row><row>model, </row><row>msdb, </row><row>database1, </row><row>database2, </row>'

I want to get rid of the </row><row> to reduce the string length to a manageable length. I get the error when I modify the query string to this:

ExecuteSql('String','SELECT name + '', '' FROM sys.databases FOR XML PATH('''')')

The error is

Exception encountered while executing policy 'List Databases'. Unclosed quotation mark after the character string ')'. Incorrect syntax near ')'. Microsoft SQL Server, Error: 105)

Why does it detect a quotation mark mismatch?

For the short lists, I added the </row><row> delimiters to the comparison string. However, I have a long list that is too long and truncates the return string with delimiters.

This is on SQL Server 2019

Solution

  • You can use FOR XML PATH('') to get text without XML tags.

    Note the extra use of a subquery and , TYPE).value to de-escape the XML.

    ExecuteSql('String','SELECT (SELECT name + '', '' FROM sys.databases FOR XML PATH(''''), TYPE).value(''text()[1]'')')

    But since you are on SQL Server 2019, you can just use STRING_AGG

    ExecuteSql('String','SELECT STRING_AGG(name, '', '') FROM sys.databases')

    Note that this version does not have a trailing ,