Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

FOR XML PATH(''): Escaping "special" characters

This code basically translates characters based on position in one string to the character at the same position in another string and it runs for all rows in the table.

When I run this (simplified version):

DECLARE @R           char(40)
DECLARE @U           char(40)
SET @R=' abcdefghijklmnopqrstuvwxyz!@#$%^&*()_+'+char(181)
SET @U=REVERSE(@R)

DECLARE @TestTable TABLE (RowID int identity(1,1) primary key, Unreadable  varchar(500))
INSERT INTO @TestTable VALUES ('+µt$zw!*µsu+yt!+s$xy')
INSERT INTO @TestTable VALUES ('%*!!xµpxu!(')
INSERT INTO @TestTable VALUES ('pxpµnxrµu+yµs%$t')


    ;WITH CodeValues AS
    (
    SELECT
        Number,SUBSTRING(@R,Number,1) AS R,ASCII(SUBSTRING(@U,Number,1)) AS UA
        FROM Numbers
        WHERE Number<=LEN(@R)
    )
    SELECT
        t.RowID
            ,(SELECT
                  ''+c.R
                  FROM Numbers               n
                      INNER JOIN CodeValues  c ON ASCII(SUBSTRING(t.Unreadable,n.Number,1))=c.UA
                  WHERE n.Number<=LEN(t.Unreadable) 
                  FOR XML PATH('') 
             ) AS readable
        FROM @TestTable t

I get the following:

RowID       readable
----------- ---------------------------------------
1           a&#x20;simple&#x20;translation
2           hello&#x20;world
3           wow&#x20;you&#x20;ran&#x20;this

But need:

RowID       readable
----------- ---------------------------------------
1           a simple translation
2           hello world
3           wow you ran this

Is there any way, other than REPLACE(), to have the spaces show up properly? This also happens on line breaks, in my actual code.

Can this be rewritten in a better way? I basically just used the FOR XML PATH('') to concatenate the individual row values together.

like image 969
KM. Avatar asked Jun 26 '09 21:06

KM.


People also ask

How do you escape special characters in XML?

XML escape characters There are only five: " &quot; ' &apos; < &lt; > &gt; & &amp; Escaping characters depends on where the special character is used. The examples can be validated at the W3C Markup Validation Service.

What is &# x0D?

The &#x0D is a carriage return. You can either clean up the data before inserting it, remove it, or, if you want to keep the formatting, add TYPE to the end of your SELECT: SELECT * FROM MyData FOR XML PATH(''), TYPE. Follow this answer to receive notifications.


1 Answers

The XML you get is correct. It is XML, not text, and readable as XML by an XML parser. Special characters are properly escaped, as they should be. Whatever client module you have that consumes that XML should parse it as XML, not as text, and then it will display properly.

Update:

In case is not clear, all you need to do in your query is to treat XML as XML and text as text, not mix XML as text, ie:

;WITH CodeValues AS
    (
    SELECT
        Number,SUBSTRING(@R,Number,1) AS R,ASCII(SUBSTRING(@U,Number,1)) AS UA
        FROM Numbers
        WHERE Number<=LEN(@R)
    )
, XmlValues AS (
SELECT
        t.RowID
            ,(SELECT
                  ''+c.R
                  FROM Numbers               n
                      INNER JOIN CodeValues  c ON ASCII(SUBSTRING(t.Unreadable,n.Number,1))=c.UA
                  WHERE n.Number<=LEN(t.Unreadable) 
                  FOR XML PATH(''), TYPE
             ) AS readable
        FROM @TestTable t)
SELECT x.RowId,
    x.readable.value('.', 'VARCHAR(8000)') as readable
    FROM XmlValues AS x
like image 164
Remus Rusanu Avatar answered Oct 09 '22 10:10

Remus Rusanu