在SQL Server中以XML数据类型存储存储存储过程的文本

I need to store the text of all of the stored procedures in a database into an XML data type. When I use, FOR XML PATH, the text within in the stored procedure contains serialized data characters like 
 and 
 for CRLF and ", etc. I need the text to stored in the xml structure without these characters because the text will need to be used to recreate the stored procedure.

我需要将数据库中所有存储过程的文本存储到XML数据类型中。当我使用XML路径时，存储过程中的文本包含像 这样的序列化数据字符;和& # xA;对于CRLF和“;”等等，我需要不带这些字符的文本存储在xml结构中，因为需要使用这些文本重新创建存储过程。

This is the query that I use for FOR XML PATH:

这是我用于XML路径的查询:

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

I read that I should use CDATA for the text using FOR XML EXPLICIT. However, the output of the when I run the following query and view the XML data, it contains those characters also. I need the text to be in plain text without these characters.

我读到我应该使用CDATA作为XML显式的文本。但是，当我运行以下查询并查看XML数据时，它的输出也包含这些字符。我需要文本是纯文本，没有这些字符。

This is my query:

这是我的查询:

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    

How can I store the text of a the stored procedures that is in plain text? Or when I parse the xml data type to recreate the stored procedure can I deserialize it so that it does not have those characters?

如何存储纯文本中存储过程的文本?或者当我解析xml数据类型以重新创建存储过程时，我可以反序列化它，使它没有这些字符吗?

Ideally, I would like to use FOR XML PATH but if that is not possible I will use FOR XML EXPLICIT.

理想情况下，我希望使用XML路径，但如果不可能，我将使用XML显式。

3 个解决方案

#1

If you want to store data with special characters within XML, there are two options (plus a joke option)

如果希望在XML中存储具有特殊字符的数据，有两个选项(外加一个笑话选项)

• escaping
• 逃离
• CDATA
• CDATA
• just to mention: Convert everything to base64 or similar would work too :-)
• 只需提一下:把所有东西转换成base64或类似的也可以:-)

The point is: You do not need this!

The only reason for CDATA (at least for me) is manually created content (copy'n'paste or typing). Whenever you build your XML automatically, you should rely on the implicitly applied escaping.

CDATA(至少对我来说)的唯一原因是手工创建内容(复制'n'paste或键入)。无论何时自动构建XML，都应该依赖隐式应用的转义。

Why does it bother you, how the data is looking within the XML?

为什么它会困扰您，数据在XML中的样子?

If you read this properly (not with SUBSTRING or other string based methods), you will get it back in the original look.

如果您正确地阅读了这个(不是使用子字符串或其他基于字符串的方法)，您将会在原始的外观中得到它。

Try this:

试试这个:

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

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

returns

返回

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

But this...

但这…

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

...returns

回报……

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

Microsoft decided not even to support this with FOR XML (except EXPLICIT, which is a pain in the neck...)

微软甚至决定不支持对XML的这种支持(除了显式的，这是非常麻烦的…)

Read two related answers (by me :-) about CDATA)

阅读关于CDATA的两个相关答案(作者:-)

• https://*.com/a/38547537/5089204
• https://*.com/a/38547537/5089204
• https://*.com/a/39034049/5089204 (with further links...)
• https://*.com/a/39034049/5089204(进一步联系…)

#2

When I use, FOR XML PATH, the text within in the stored procedure contains serialized data characters like and for CRLF and ", etc.

当我使用XML路径时，存储过程中的文本包含序列化数据字符，如CRLF和"等。

Yes, because that's how XML works. To take a clearer example, suppose your sproc contained this text:

是的，因为这就是XML的工作方式。举个更清楚的例子，假设您的sproc包含以下文本:

IF @someString = '<' THEN

then to store it in XML, there must be some kind of encoding applied, since you can't have a bare < in the middle of your XML (I hope you can see why).

然后，要将它存储在XML中，必须应用某种编码，因为在XML中间不能有一个空的<(我希望您能明白为什么)。

The real question is then not 'how do I stop my text being encoded when I store it as XML', but rather (as you guess might be the case):

真正的问题不是“当我将文本存储为XML时，如何阻止它被编码”，而是(正如您所猜测的那样):

Or when I parse the xml data type to recreate the stored procedure can I deserialize it so that it does not have those characters?

或者当我解析xml数据类型以重新创建存储过程时，我可以反序列化它，使它没有这些字符吗?

Yes, this is the approach you should be looking at.

是的，这是你应该研究的方法。

You don't how us how you're getting your text out of the XML at the moment. The key thing to remember is that you can't (or rather shouldn't) treat XML as 'text with extra bits' - you should use methods that understand XML.

您不知道如何从XML中获取文本。要记住的关键是，您不能(或者不应该)将XML视为“带有额外字节的文本”——您应该使用理解XML的方法。

If you're extracting the text in T-SQL itself, use the various XQuery options. If in C#, use any of the various XML libraries. Just don't do a substring operation and expect that to work...

如果要提取T-SQL本身中的文本，请使用各种XQuery选项。如果在c#中，使用任何不同的XML库。不要做子字符串操作，并期望它可以工作。

---

An example, if you are extracting in T-SQL:

一个例子，如果你在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;

But you can find many many tutorials on how to get values out of xml-typed data; this is just an example.

但是您可以找到许多关于如何从xml类型数据中获取值的教程;这只是一个例子。

#3

    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 

Sample of the output from Adventureworks2012:

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>

As you note there are no 
 / 
 / "/ etc and NewLine characters is represented as new line

正如你所注意到的，没有 / & # xA;/“;”/等等，换行字符表示为新行