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.
This is the query that I use for 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
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.
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?
Ideally, I would like to use FOR XML PATH
but if that is not possible I will use FOR XML EXPLICIT
.