Encode xml column values as xml in sql server

Question

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.

Owww! Really Sad! But can't you write down a small function in C# to do the same and add it on SQL Server? — Kirtan
– Kirtan, Commented Jun 2, 2009 at 12:59
I have added a reference to an excellent article showing to go about it. — Kirtan
– Kirtan, Commented Jun 2, 2009 at 13:00
Please edit the question to say what part is done in SQL Server and what part in the calling application. It's not clear. — John Saunders
– John Saunders, Commented Jun 2, 2009 at 13:09

Quassnoi · Accepted Answer · 2009-06-02 13:09:31Z

6

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

edited Jun 2, 2009 at 13:09

answered Jun 2, 2009 at 12:17

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

gbn Over a year ago

"FOR XML" exists in SQL 2000 too. msdn.microsoft.com/en-us/magazine/cc163782.aspx

Quassnoi Over a year ago

@Mitch: I thought it was in 2005+ only, but @gbn's link changed my mind.

gbn Over a year ago

SQL 2005 added XPath/Xquery + xml datatype + generally made things better. No more "sp_xmlpreparedocument"!

Gais Over a year ago

That works if I want to build the xml directly in sql server, but I just want to encode the data - the xml is already being built within the application to which the query returns.

Ryan · Accepted Answer · 2014-06-26 19:23:48Z

4

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

edited Jun 26, 2014 at 19:23

Ryan

8,0732 gold badges32 silver badges38 bronze badges

answered Nov 1, 2010 at 20:30

matt

411 bronze badge

Comments

ahains · Accepted Answer · 2009-06-02 13:14:18Z

3

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;

answered Jun 2, 2009 at 13:14

ahains

1,91212 silver badges10 bronze badges

Comments

John Saunders · Accepted Answer · 2009-06-02 13:11:39Z

1

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.

answered Jun 2, 2009 at 13:11

John Saunders

162k26 gold badges252 silver badges403 bronze badges

Comments

Kirtan · Accepted Answer · 2009-06-02 12:58:24Z

0

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.

edited Jun 2, 2009 at 12:58

answered Jun 2, 2009 at 12:51

Kirtan

21.7k6 gold badges50 silver badges61 bronze badges

1 Comment

Gais Over a year ago

I'm not using .net :( I'm using a BPM suite based on JAVA :( :( Otherwise that would be perfect

Andrew · Accepted Answer · 2014-09-05 10:35:53Z

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('&<&>"&&')

Collectives™ on Stack Overflow

Encode xml column values as xml in sql server

6 Answers 6

4 Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related