Reputation: 575
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
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
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, '&', '&')
set @rs = replace(@rs, '''', ''')
set @rs = replace(@rs, '"', '"')
set @rs = replace(@rs, '>', '>')
set @rs = replace(@rs, '<', '<')
Return( @rs)
end
Upvotes: 4
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: ><&
Upvotes: 3
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
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 & Noble" /><q shop="Marx & Spencer" />
If you just want to encode an existing value, use:
SELECT 'Barnes & Noble'
FOR XML PATH('')
---
Barnes & Noble
Upvotes: 6
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