It's just Emanuele showing you what you can do in the Microsoft Data Platform

Cast any string directly to XML with auto-escaping characters in SQL Server

C

Recently I had to look up the definition for a bunch of SQL objects and didn’t want to manually retrieve them manually in SSMS (with Create Scripts) or Visual Studio (by searching the object name in my TFS repository).

Since lazyness and automation are the basis of a well done engineering work, I wanted to create a list, where I could basically click on the object that I needed and see the definition right away, without any tool or having to code something externally, of course.

The issue

DMVs and XML came to the rescue here, since everybody remembers that a XML field in SSMS (or AZDS) is clickable and opens up the XML Code in a new window; BUT, you cannot cast everything right away in XML, as some characters needs to be escaped, and most of those characters are often used in object definitions (<,>,etc…)

Here is what happens if you try to cast something to XML directly

How to deal with it

By first creating a XML document with the FOR XML statement, and then casting it as XML in the result set, we obtain what we were looking for:

SELECT s.name AS Schema_Name
    ,  o.name AS Object_Name
    ,  CAST((SELECT m.definition FOR XML PATH('')) as XML) Object_Definition
    ,  o.type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o
    ON m.object_id = o.object_id
INNER JOIN sys.schemas s
    ON o.schema_id = s.schema_id
It’s as easy as this
Of course it works in SSMS too

As you can see in the ouput XML, special XML characters are escaped automatically, no need to REPLACE(REPLACE(REPLACE(REPLACE
Enjoy!

About the author

Emanuele Meazzo

4 comments

  • How might you apply your solution to the following:

    SELECT
    [ColumnName] = [X2].[N].value(‘local-name(.)’, ‘NVARCHAR(128)’),
    [ColumnValue] = [X2].[N].value(‘text()[1]’, ‘NVARCHAR(MAX)’)
    FROM ( SELECT TOP 1 * FROM [Centers] FOR XML AUTO, ELEMENTS, TYPE ) AS X1(X)
    CROSS APPLY X1.X.nodes(‘//*[text()]’) AS X2(N);

    • Nevermind… simply changing select top 1 * with select top 1 col1 = string_escape(col1, ‘json’)….

      • DECLARE
        @Sql varchar(max) = ”,
        @ColumnList varchar(max) = ”,
        @TableSchema varchar(100) = ‘dbo’,
        @TableName varchar(max) = ‘TaxInfos’,
        @ExecSql nvarchar(max) = ”;

        SELECT @ColumnList = @ColumnList + CONCAT(‘, ‘, [COLUMN_NAME], ‘ = ‘, ‘STRING_ESCAPE(CAST(‘, [COLUMN_NAME], ‘ AS VARCHAR(MAX)),”json”)’)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE [TABLE_NAME] = ‘TaxInfos’
        SELECT @Sql = CONCAT(‘SELECT TOP 1’, SUBSTRING(@ColumnList, 2, LEN(@ColumnList)), ‘ FROM ‘, @TableSchema, ‘.’, @TableName)

        SELECT @ExecSql = CONCAT(‘
        SELECT
        [ColumnName] = [X2].[N].value(”local-name(.)”, ”NVARCHAR(128)”),
        [ColumnValue] = [X2].[N].value(”text()[1]”, ”NVARCHAR(MAX)”)
        FROM (‘,
        @Sql,
        ‘ FOR XML AUTO, ELEMENTS, TYPE ) AS X1(X) CROSS APPLY X1.X.nodes(”//*[text()]”) AS X2(N)’)

        EXECUTE sp_executesql @ExecSql;

It's just Emanuele showing you what you can do in the Microsoft Data Platform

Emanuele Meazzo

My Social Profiles

If you're interested in following me around in the social world, here are some URLs for you

Find Us

Address
123 Main Street
London EC1 4UK

Hours
Monday—Friday: 9:00AM–5:00PM
Saturday & Sunday: 11:00AM–3:00PM

About This Site

This may be a good place to introduce yourself and your site or include some credits.