FOR XML PATH(”): Escaping “special” characters

by

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

More Related Contents:

  1. T-SQL: Opposite to string concatenation – how to split string into multiple records [duplicate]
  2. Check if table exists in SQL Server
  3. Sql Server string to date conversion
  4. Insert multiple rows WITHOUT repeating the “INSERT INTO …” part of the statement?
  5. Does T-SQL have an aggregate function to concatenate strings? [duplicate]
  6. Parameter Sniffing (or Spoofing) in SQL Server
  7. How to get Time from DateTime format in SQL?
  8. SQL Server String or binary data would be truncated
  9. Datatype for storing ip address in SQL Server
  10. How to change identity column values programmatically?
  11. Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?
  12. How much size “Null” value takes in SQL Server
  13. best way to convert and validate a date string
  14. How FOR XML PATH(”) works when concatenating rows
  15. SQL Server ORDER BY date and nulls last
  16. How to remove accents and all chars a..z in sql-server?
  17. Find non-ASCII characters in varchar columns using SQL Server
  18. WHERE clause on SQL Server “Text” data type
  19. SQL Server error on update command – “A severe error occurred on the current command”
  20. Aggregate bitwise-OR in a subquery
  21. SQL server schema and default schema
  22. Check if a string contains a substring in SQL Server 2005, using a stored procedure
  23. Cannot find either column “dbo” or the user-defined function or aggregate “dbo.Splitfn”, or the name is ambiguous
  24. Does query plan optimizer works well with joined/filtered table-valued functions?
  25. What are row, page and table locks? And when they are acquired?
  26. @@ERROR and/or TRY – CATCH
  27. SQL Server – error when creating stored procedure
  28. Why use the INCLUDE clause when creating an index?
  29. TSQL Try / Catch within Transaction or vice versa?
  30. “select * from table” vs “select colA, colB, etc. from table” interesting behaviour in SQL Server 2005

Leave a Comment