locked
Strip XML tags, add an space between results RRS feed

  • Question

  • I found a function on the internet which works really fast and doesn't require an UDF or CLR that removes the tags in an XML field.  The problem I have with the function I've found is that it concatinates the values with no spaces or characters in between.  I'm not very good with XML and especially not good with XML and SQL server, so can some-one help me out (I have literly tried for hours on my own) adjusting this so that it out puts the text with an space between values?

    ALTER function [dbo].[StripTags]( @text varchar(max) ) returns varchar(max) as
    begin
        declare @textXML xml
        declare @result varchar(max)
        set @textXML = REPLACE( @text, '&', '' );
        with doc(contents) as
        (
            select chunks.chunk.query('.') from @textXML.nodes('/') as chunks(chunk)
        )
        select @result = contents.value('.', 'varchar(max)') from doc
        return @result
    end

    GO



    George P Botuwell, Programmer

    Thursday, August 15, 2013 9:07 PM

Answers

  • I don't care much for this particular function because it is taking a string parameter instead of xml and replaces the ampersand entity references with an empty string, altering the intended value.  You might try the example below. 

    ALTER FUNCTION [dbo].[StripTags]( @xml xml) RETURNS varchar(max) AS
    BEGIN
    
    RETURN 
    	(SELECT STUFF(
    		(SELECT 
    			' ' + element.element_text.value('.', 'varchar(MAX)')
    		FROM   @xml.nodes('/') chunks(chunk)
    		CROSS APPLY chunks.chunk.nodes('//text()') AS element(element_text)
    		FOR XML PATH ('')
    		), 1, 1, '')
    	)
    END;
    GO
    

    I suggest you post SQL XML questions to the SQL Server XML forum where the XML experts hang out: http://social.msdn.microsoft.com/Forums/en-US/home?forum=sqlxml


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Friday, August 16, 2013 11:59 AM

All replies

  • Can you provide some sample xml and expected results please?

    Shredding XML in SQL Server is easy with the methods of the built-in xml datatype, eg .value, .query, .nodes, .exist.  A simple example:

    DECLARE @xml XML = '<Users>
    	<User name="wBob"></User>
    	<User name="George"></User>
    </Users>'
    
    
    SELECT u.c.value('@name', 'VARCHAR(100)') AS userName
    FROM @xml.nodes('Users/User') u(c)

    Friday, August 16, 2013 11:04 AM
  • I don't care much for this particular function because it is taking a string parameter instead of xml and replaces the ampersand entity references with an empty string, altering the intended value.  You might try the example below. 

    ALTER FUNCTION [dbo].[StripTags]( @xml xml) RETURNS varchar(max) AS
    BEGIN
    
    RETURN 
    	(SELECT STUFF(
    		(SELECT 
    			' ' + element.element_text.value('.', 'varchar(MAX)')
    		FROM   @xml.nodes('/') chunks(chunk)
    		CROSS APPLY chunks.chunk.nodes('//text()') AS element(element_text)
    		FOR XML PATH ('')
    		), 1, 1, '')
    	)
    END;
    GO
    

    I suggest you post SQL XML questions to the SQL Server XML forum where the XML experts hang out: http://social.msdn.microsoft.com/Forums/en-US/home?forum=sqlxml


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Friday, August 16, 2013 11:59 AM
  • Can you provide some sample xml and expected results please?

    Shredding XML in SQL Server is easy with the methods of the built-in xml datatype, eg .value, .query, .nodes, .exist.  A simple example:

    DECLARE @xml XML = '<Users>
    	<User name="wBob"></User>
    	<User name="George"></User>
    </Users>'
    
    
    SELECT u.c.value('@name', 'VARCHAR(100)') AS userName
    FROM @xml.nodes('Users/User') u(c)

    I thought of that.  It would have been pretty involved to include an example, since the example contains sensitive data, is quite large/complicated, and not easily sanitized.

    George P Botuwell, Programmer

    Friday, August 16, 2013 2:11 PM
  • I don't care much for this particular function because it is taking a string parameter instead of xml and replaces the ampersand entity references with an empty string, altering the intended value.  You might try the example below. 

    ALTER FUNCTION [dbo].[StripTags]( @xml xml) RETURNS varchar(max) AS
    BEGIN
    
    RETURN 
    	(SELECT STUFF(
    		(SELECT 
    			' ' + element.element_text.value('.', 'varchar(MAX)')
    		FROM   @xml.nodes('/') chunks(chunk)
    		CROSS APPLY chunks.chunk.nodes('//text()') AS element(element_text)
    		FOR XML PATH ('')
    		), 1, 1, '')
    	)
    END;
    GO

    I suggest you post SQL XML questions to the SQL Server XML forum where the XML experts hang out: http://social.msdn.microsoft.com/Forums/en-US/home?forum=sqlxml


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Dan, This did the tick, exactly what I was looking for...

    George P Botuwell, Programmer

    Friday, August 16, 2013 2:12 PM
  • Dan, This did the tick, exactly what I was looking for...

    Glad it helped.  Below is a slightly improved version without the CROSS APPLY:

    ALTER FUNCTION [dbo].[StripTags]( @xml xml) RETURNS varchar(max) as
    BEGIN
    
    RETURN 
    	(SELECT STUFF(
    		(SELECT 
    			' ' + element.element_text.value('.', 'varchar(MAX)')
    		FROM @xml.nodes('//text()') AS element(element_text)
    		FOR XML PATH ('')
    		), 1, 1, '')
    	);
    END;
    GO


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, August 17, 2013 4:37 AM