在 SQL Server 中以 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

如果要在XML中存储带有特殊字符的数据，则有两个选项（外加一个笑话选项）

• 逃避
• CDATA
• 只是提到：将所有内容转换为或类似的东西也可以：-）base64

关键是：你不需要这个！

唯一的原因（至少对我而言）是手动创建的内容（复制粘贴或输入）。每当自动生成 XML 时，都应该依赖于隐式应用的转义。CDATA

为什么它困扰您，数据在 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 XMLEXPLICIT

阅读两个相关答案（由我：-）关于 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 中提取数据：An example， if you are extracting in 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 类型数据中获取值的教程;这只是一个例子。