Storing the text of a stored procedure in an XML data type in SQL Server
Asked Answered
Y

3

2

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.

Yellowstone answered 13/9, 2016 at 20:58 Comment(1)
What is the end goal? Why do you want to store text in an inherently terrible format?Wilmott
R
2

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

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

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.

Why does it bother you, how the data is looking within the 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 &lt;ups, angular brackets! &gt; And Ampersand &amp;&amp;&amp;

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...)

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

Redtop answered 14/9, 2016 at 8:24 Comment(5)
Hmm, I didn't get the 'an answer has been posted' while I was typing... Have a +1 from me for being first :)Epizootic
@Epizootic I'll pay this back, as your answer is just as convincing as mine. Mental brothers :-)Redtop
Thanks. As I told @AakashM, this will save me time. But I still have to extract it.Yellowstone
@GloriaSantin, As you want to store something within XML you will have - the one way or another - to extract it... The point is, that all XML tools do the heavy part implicitly, so you do not have to bother about what's going on internally. And btw: Thank you for the acceptance! Since you've crossed the 15 points border yourself it would be fair to vote AakashM's answer up. And if you like, you might vote on mine too :-)Redtop
Done! Yes. You were correct. I can extract a view and stored procedure from an XML data type variable and store it into a temporary table as text and it looks great. However, there are 3 stored procedures when I add them to the xml document are throwing an error when I do a check on the query. For example, one of the stored procedures throws the error incorrect syntax new the keyword Column.Yellowstone
E
2

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

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

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).

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):

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?

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.

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...


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;

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

Epizootic answered 14/9, 2016 at 8:29 Comment(1)
Thank you for this. It now seems like I should have known this. You saved me a lot of time. I will try your suggestions.Yellowstone
P
0
    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:

    <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 &#xD; / &#xA; / &quot;/ etc and NewLine characters is represented as new line

Pumphrey answered 13/9, 2016 at 23:11 Comment(1)
This approach is fine, and still I would not recommend it... CDATA is not wrong, but - at least to me and some people at Microsoft - escaping seems to be better.Redtop

© 2022 - 2024 — McMap. All rights reserved.