CODEWITHSUNDEEP
sql-serverxml
Gais
Gais

Reputation: 575

Encode xml column values as xml in sql server

I have a method that is taking vales directly from the database, building a string of xml and then writing the xml to a file.

This is fine until I get special characters eg "'", "<", "&" etc.

Does anyone know of something in Sql Server that would allow me to encode the values as i select them; for example;

select encode(service_status) from myTable

Thanks.

Upvotes: 2

Views: 13315

Answers (6)

Andrew
Andrew

Reputation: 635

My variant:

CREATE FUNCTION dbo.fn_XmlEncode (@STR varchar(200))
RETURNS varchar(200)
AS
BEGIN
    IF CHARINDEX('&', @STR) > 0
    BEGIN
       DECLARE @POS1 int, @POS2 int
       SET @POS1 = CHARINDEX('&', @STR)
       WHILE @POS1 > 0 BEGIN
           IF SUBSTRING(@STR, @POS1, 5) <> '&'
              SET @STR = LEFT(@STR, @POS1 - 1) + '&' 
                  + case when @POS1 < LEN(@STR) THEN SUBSTRING(@STR, @POS1 + 1, LEN(@STR) - @POS1) ELSE '' END
           SET @POS2 = CHARINDEX('&', SUBSTRING(@STR, @POS1 + 5, LEN(@STR)))
           IF @POS2 = 0 BREAK
           SET @POS1 = @POS1 + 4 + @POS2
       END
    END
    WHILE CHARINDEX('<', @STR) > 0
       SET @STR = REPLACE(@STR, '<', '<')
    WHILE CHARINDEX('>', @STR) > 0
       SET @STR = REPLACE(@STR, '>', '>')
    RETURN @STR
END
GO
-- Tests
SELECT dbo.fn_XmlEncode('&&'), dbo.fn_XmlEncode('&<&>"&&')

Upvotes: 0

matt
matt

Reputation: 41

you don't need the CLR either just do it in sql...

create function [dbo].[fn_XMLEscape]( @s varchar(max)) returns varchar(max)
as 
begin
  declare @rs varchar(max) 
  set @rs = @s
  set @rs = replace(@rs, '&', '&amp;')
  set @rs = replace(@rs, '''', '&apos;')
  set @rs = replace(@rs, '"', '&quot;')
  set @rs = replace(@rs, '>', '&gt;')
  set @rs = replace(@rs, '<', '&lt;')
  Return( @rs)
end

Upvotes: 4

ahains
ahains

Reputation: 1912

If you have >= sql 2005, I think it may be easiest to stuff your value into an xml element and then pull it back out. This will entitize anything that needs encoding.

declare @x xml, @str varchar(8000), @encStr varchar(8000)
set @x = '<a/>'
set @str = '><&'

set @x.modify(
    'insert text{sql:variable("@str")}
    as first into (/a)[1]')

set @encStr = CAST(@x.query('/a/text()') as varchar(8000))
select @encStr
--returns: &gt;&lt;&amp;

Upvotes: 3

John Saunders
John Saunders

Reputation: 161831

If the calling application is building what you return into XML, then it is up to the calling application to encode the data. If you want to return XML from SQL Server, then it would be up to you, and the "FOR XML" answer by Quassnoi is correct.

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425863

Use FOR XML clause.

It can build XML from multiple values automatically:

WITH  q AS (
        SELECT  'Barnes & Noble' AS shop
        UNION ALL
        SELECT  'Marks & Spencer'
        )
SELECT  *
FROM    q
FOR XML AUTO, TYPE

---
<q shop="Barnes &amp; Noble" /><q shop="Marx &amp; Spencer" />

If you just want to encode an existing value, use:

SELECT  'Barnes & Noble'
FOR XML PATH('')

---
Barnes &amp; Noble

Upvotes: 6

Kirtan
Kirtan

Reputation: 21685

If you are using SQL Server version 2005/2008, then you are in luck, as you can create your own ENCODE function using CLR Functions.

A really good article can be found here.

Upvotes: 0

Related Questions