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

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

Это запрос, который я использую для FOR XML PATH:

SELECT 
    [View].name AS "@VName", [Module].definition AS "@VDefinition"
FROM 
    sys.views AS [View] 
INNER JOIN 
    sys.sql_modules AS [Module] ON [Module].object_id = [View].object_id
FOR XML PATH ('View'), TYPE

Я прочитал, что я должен использовать CDATA для использования текста FOR XML EXPLICIT, Тем не менее, вывод, когда я запускаю следующий запрос и просматриваю данные XML, он также содержит эти символы. Мне нужно, чтобы текст был простым текстом без этих символов.

Это мой запрос:

SELECT  
    1 AS Tag,
    0 AS Parent,
    NULL AS [Database1!1],      
    NULL AS [StoredProcedure!2!VName],
    NULL AS [StoredProcedure!2!cdata]

UNION ALL

SELECT  
    2 AS Tag,
    1 AS Parent,        
    NULL,
    [StoredProcedure].name as [StoredProcedure!2!!CDATA],
    [Module].definition as [StoredProcedure!2!!CDATA]
FROM 
    sys.procedures AS [StoredProcedure] 
INNER JOIN 
    sys.sql_modules [Module] ON [StoredProcedure].object_id = [Module].object_id
WHERE 
    [StoredProcedure].name NOT LIKE '%diagram%'
FOR XML EXPLICIT

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

В идеале я хотел бы использовать FOR XML PATH но если это невозможно, я буду использовать FOR XML EXPLICIT,

Источник

3 ответа

Решение

Если вы хотите хранить данные со специальными символами в 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 &lt;ups, angular brackets! &gt; And Ampersand &amp;&amp;&amp;

Но это...

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)

Источник

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

Да, потому что так работает XML. Для более ясного примера, предположим, что ваш спрок содержал этот текст:

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>

Как вы заметили, нет &#xD; / &#xA; / &quot;/ etc и символы NewLine представлены в виде новой строки

Источник
Другие вопросы по тегам