在SQL Server中以XML数据类型存储存储过程的文本 [英] Storing the text of a stored procedure in an XML data type in SQL Server

问题描述

我需要将数据库中所有存储过程的文本存储为XML数据类型.当我使用FOR XML PATH时，存储过程中的文本包含序列化的数据字符，例如CRLF和"的
和
等.我需要将文本存储在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.

这是我用于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数据时，其输出也包含这些字符.我需要文本为不带这些字符的纯文本.

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.

这是我的查询:

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数据类型以重新创建存储过程时，我可以反序列化它以便没有那些字符吗?

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?

理想情况下，我想使用FOR XML PATH，但是如果不可能，我将使用FOR XML EXPLICIT.

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

推荐答案

如果要在XML内存储带有特殊字符的数据 ，则有两个选项(加上一个笑话选项)

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

• 转义
• CDATA
• 仅需提一下:将所有内容转换为base64或类似的方法也可以:-)

• escaping
• CDATA
• just to mention: Convert everything to base64 or similar would work too :-)

CDATA的唯一原因(至少对我而言)是手动创建的内容(复制或粘贴或键入内容).每当您自动构建XML时，都应依靠隐式应用的转义.

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.

为什么会打扰您，数据在XML中的外观 ?

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

如果您正确阅读了此内容(而不是使用SUBSTRING或其他基于字符串的方法)，则可以通过原始的 look 将其恢复.

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

尝试一下:

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除外，这很麻烦...)

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

阅读有关CDATA的两个相关答案(我:-))

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

• https://stackoverflow.com/a/38547537/5089204
• https://stackoverflow.com/a/39034049/5089204 (还有其他链接...)

• https://stackoverflow.com/a/38547537/5089204
• https://stackoverflow.com/a/39034049/5089204 (with further links...)

这篇关于在SQL Server中以XML数据类型存储存储过程的文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！