Хранение текста хранимой процедуры в типе данных XML в SQL Server

Если вы хотите хранить данные со специальными символами внутри XML, есть два варианта (плюс шуточный вариант)

• побег
• CDATA
• просто отметим: преобразование всего в base64 или подобное тоже сработает :-)

Дело в том, что вам это не нужно!

Единственная причина для CDATA (по крайней мере, для меня) — это содержимое, созданное вручную (копирование и вставка). Всякий раз, когда вы создаете свой XML автоматически, вы должны полагаться на неявно применяемое экранирование.

Почему вас беспокоит, как данные выглядят в XML?

Если вы прочтете это правильно (а не с помощью SUBSTRING или других строковых методов), вы получите его обратно в исходном виде.

Попробуй это:

DECLARE @TextWithSpecialCharacters NVARCHAR(100)=N'€ This is' + CHAR(13) + 'strange <ups, angular brackets! > And Ampersand &&&';

SELECT @TextWithSpecialCharacters FOR XML PATH('test');

возвращается

€ This is
strange <ups, angular brackets! > And Ampersand &&&

Но это...

SELECT (SELECT @TextWithSpecialCharacters FOR XML PATH('test'),TYPE).value('/test[1]','nvarchar(100)');

...возвращается

€ This is
strange <ups, angular brackets! > And Ampersand &&&

Microsoft решила даже не поддерживать это с помощью FOR XML (кроме EXPLICIT, который вызывает головную боль...)

Прочитайте два связанных ответа (от меня :-) о CDATA)

• https://stackoverflow.com/a/38547537/5089204
• https://stackoverflow.com/a/39034049/5089204 (с дополнительными ссылками...)

Когда я использую FOR XML PATH, текст в хранимой процедуре содержит сериализованные символы данных, такие как и для CRLF и и т. д.

Да, потому что именно так работает XML. Чтобы взять более ясный пример, предположим, что ваш sproc содержит этот текст:

IF @someString = '<' THEN

затем, чтобы сохранить его в XML, должна применяться какая-то кодировка, поскольку у вас не может быть голого < в середине вашего XML (надеюсь, вы понимаете, почему).

Тогда реальный вопрос заключается не в том, «как мне остановить кодирование моего текста, когда я сохраняю его как XML», а скорее (как вы догадываетесь):

Или когда я анализирую тип данных xml для воссоздания хранимой процедуры, могу ли я десериализовать ее, чтобы в ней не было этих символов?

Да, это подход, на который вы должны обратить внимание.

Вы не знаете, как вы получаете текст из XML в данный момент. Главное, что нужно помнить, это то, что вы не можете (или, скорее, не должны) рассматривать XML как «текст с дополнительными битами» — вы должны использовать методы, которые понимают XML.

Если вы извлекаете текст в самом T-SQL, используйте различные параметры XQuery. Если на C#, используйте любую из различных библиотек XML. Просто не выполняйте операцию с подстрокой и ожидайте, что это сработает...

Пример, если вы извлекаете в T-SQL:

DECLARE @someRandomText nvarchar(max) = 'I am some arbitrary text, eg a sproc definition.

I contain newlines

And arbitrary characters such as < > & 

The end.';

-- Pack into XML

DECLARE @asXml xml = ( SELECT @someRandomText FOR XML PATH ('Example'), TYPE );

SELECT @asXml;


-- Extract

DECLARE @textOut nvarchar(max) = ( SELECT @asXml.value('.', 'nvarchar(max)') ) ;

SELECT @textOut;

Но вы можете найти много руководств о том, как получить значения из данных в формате xml; это просто пример.

    SELECT 
        1 as Tag,  
        0 as Parent,    
        [View].name AS 'StoredProcedure!1!Name', 
        [Module].definition AS 'StoredProcedure!1!Definition!cdata'     
    FROM sys.views AS [View] 
    INNER JOIN sys.sql_modules AS [Module] ON [Module].object_id = [View].object_id
    FOR XML EXPLICIT 

Пример вывода Adventureworks2012:

    <StoredProcedure Name="vStoreWithContacts">
      <Definition><![CDATA[
    CREATE VIEW [Sales].[vStoreWithContacts] AS 
    SELECT 
        s.[BusinessEntityID] 
        ,s.[Name] 
        ,ct.[Name] AS [ContactType] 
        ,p.[Title] 
        ,p.[FirstName] 
        ,p.[MiddleName] 
        ,p.[LastName] 
        ,p.[Suffix] 
        ,pp.[PhoneNumber] 
        ,pnt.[Name] AS [PhoneNumberType]
        ,ea.[EmailAddress] 
        ,p.[EmailPromotion] 
    FROM [Sales].[Store] s
        INNER JOIN [Person].[BusinessEntityContact] bec 
        ON bec.[BusinessEntityID] = s.[BusinessEntityID]
        INNER JOIN [Person].[ContactType] ct
        ON ct.[ContactTypeID] = bec.[ContactTypeID]
        INNER JOIN [Person].[Person] p
        ON p.[BusinessEntityID] = bec.[PersonID]
        LEFT OUTER JOIN [Person].[EmailAddress] ea
        ON ea.[BusinessEntityID] = p.[BusinessEntityID]
        LEFT OUTER JOIN [Person].[PersonPhone] pp
        ON pp.[BusinessEntityID] = p.[BusinessEntityID]
        LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
        ON pnt.[PhoneNumberTypeID] = pp.[PhoneNumberTypeID];
    ]]></Definition>
    </StoredProcedure>
    <StoredProcedure Name="vStoreWithAddresses">
      <Definition><![CDATA[
    CREATE VIEW [Sales].[vStoreWithAddresses] AS 
    SELECT 
        s.[BusinessEntityID] 
        ,s.[Name] 
        ,at.[Name] AS [AddressType]
        ,a.[AddressLine1] 
        ,a.[AddressLine2] 
        ,a.[City] 
        ,sp.[Name] AS [StateProvinceName] 
        ,a.[PostalCode] 
        ,cr.[Name] AS [CountryRegionName] 
    FROM [Sales].[Store] s
        INNER JOIN [Person].[BusinessEntityAddress] bea 
        ON bea.[BusinessEntityID] = s.[BusinessEntityID] 
        INNER JOIN [Person].[Address] a 
        ON a.[AddressID] = bea.[AddressID]
        INNER JOIN [Person].[StateProvince] sp 
        ON sp.[StateProvinceID] = a.[StateProvinceID]
        INNER JOIN [Person].[CountryRegion] cr 
        ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
        INNER JOIN [Person].[AddressType] at 
        ON at.[AddressTypeID] = bea.[AddressTypeID];
    ]]></Definition>

Как вы заметили, символы 
 / 
 / "/ etc отсутствуют, а символы новой строки представлены как новая строка.