6

I need to dig into a logs table with a schema similar to this:

CREATE TABLE t (
  id int PRIMARY KEY,
  data varchar(max)
);

Column data stores a XML text received from a web service in this format:

This is a reduced version

<?xml version="1.0" encoding="UTF-8"?>
<PARAM>
  <TAB DIM="30" ID="ZC3D2_1" SIZE="5">
    <LIN NUM = "1">
      <FLD NAME = "ZDOC" TYPE = "Char">Ferran López</FLD>
    </LIN>
  </TAB>
</PARAM>

When I try to cast this text to XML I get next error:

XML parsing: line xx, character 48, illegal xml character

It can be solved by removing the <xml> tag, or at least, the encoding attribute.

NOTE: It works fine if there is no special characters like ó, even if I don't remove <xml> tag.

Question

Is there a way to convert it to XML without replacing or removing <xml> tag?

CAST(REPLACE(data, 'encoding="UTF-8"', '') as XML)

db<>fiddle here

UPDATE

The server collation is: Latin1_General_BIN

But even if I try to change the collation to my usual servers collation, it doesn't work.

SELECT
  id, 
  CAST((data COLLATE Latin1_General_CI_AS) as XML)
FROM
  t;
5

What's happening here is:

  1. The XML type stores data internally as UTF-16 Little Endian (most of the time, at least). It doesn't matter what the source encoding is, the end-result will be UTF-16 LE (and no <xml> tag, hence no encoding="...").
  2. When converting a string to XML:
    1. It's the bytes of the string that get converted, not the characters (will explain the difference in a moment)
    2. NVARCHAR data is assumed to be UTF-16 LE. If there is an <xml> tag and it contains the encoding attribute, the only valid value is "UTF-16".
    3. VARCHAR data is assumed to be in the 8-bit code page associated with the collation of the data when there is no <xml> tag, or if one exists but there is no encoding attribute. Else the data will be interpreted as being encoded in the code page specified in the encoding attribute (even though it is encoded in the code page associated with the collation of the data).
  3. Your data is most likely encoded as Windows code page 1252 (this is determined by the collation of the column that the data resides in, not the collation of the instance or even the database, but since you mention that the instance is using Latin1_General_BIN, it's safe-enough to assume for the moment that the column is using the same collation).
  4. The code point for the ó character in code page Windows-1252 is: 0xF3.
  5. The <xml> tag, however, is declaring that the XML data is encoded as UTF-8.
  6. In UTF-8, 0xF3 must be followed by three bytes, each being between 0x80 and 0xBF, yet in your data it's followed by a p, which has a value of 0x70. Hence you get the "illegal xml character" error (because the encoding="UTF-8" tells the conversion function that the bytes are valid UTF-8 bytes; the conversion doesn't see the ó character).

Your options are:

  1. Ideally, the column would be converted to XML and the encoding attribute of the <xml> tag, or the entire <xml> tag itself, would be removed on the way in. AND, the XML datatype can save space if there are repeating element and/or attribute names as it creates a dictionary (lookup list) of names internally and records the structure using the ID values.

  2. Set the [data] column to use a UTF-8 collation (new in SQL Server 2019, so not an option for you)

  3. Set the [data] column to be NVARCHAR, and remove the encoding attribute of the <xml> tag, or the entire <xml> tag.

  4. Convert the incoming string into UTF-8 bytes. So the ó character is two bytes in UTF-8: 0xC3B3, which appear as ó in Windows-1252.

    DECLARE @Good VARCHAR(MAX) = '<?xml version="1.0" encoding="UTF-8"?><a>hell'
            + CONVERT(VARCHAR(MAX), 0xC3B3)
            + '</a>';
    SELECT @Good, CONVERT(XML, @Good)
    -- <?xml version="1.0" encoding="UTF-8"?><a>helló</a>
    --
    -- <a>helló</a>
    

NOTES:

  • Simply removing the encoding attribute of the <xml> tag, or the entire <xml> tag, is not an option. Sure, it will work in this particular case, but it won't work in all cases due to the column being VARCHAR and UTF-8 collations not being available in SQL Server 2014. Hence, any Unicode characters not available in Windows code page 1252 will be converted to ? or ?? (depending on BMP character or Supplementary Character):
    DECLARE @Test VARCHAR(MAX) = '<test>ó - ☢ - 🌝</test>';
    SELECT @Test, CONVERT(XML, @Test);
    -- <test>ó - ? - ??</test>
    --
    -- <test>ó - ? - ??</test>
    
  • Do NOT simply change the collation of the column to a different locale / culture. While that might get rid of the error, it would only accomplish that by silently getting rid of the data that was causing the error. For example:
    DECLARE @Data NVARCHAR(MAX) = N'ó';
    SELECT CONVERT(VARCHAR(MAX), @Data COLLATE Latin1_General_BIN) AS [Latin1_General],
        CONVERT(VARCHAR(MAX), @Data COLLATE Latin1_General_BIN) COLLATE
                 Cyrillic_General_CI_AS AS [Cyrillic];
    /*
    Latin1_General    Cyrillic
    ó                 o
    */
    
    "Cyrillic" uses a different code page than "Latin1_General", and the ó character is not available on the Cyrillic code page. But, there is a "Best Fit" mapping which is why we end up with an o instead of a ?.
  • You, and anyone working on SQL Server 2008 or newer, really should be using the _100_ level collations. Additionally, anyone working on SQL Server 2012 or newer should ideally be using the _100_ level collation that ends with _SC (for Supplementary Characters). Finally, when needing a binary collation on SQL Server 2005 or newer, use one ending in _BIN2 (see my post here as to why).
  • This issue has nothing to do with whether the query is ad hoc or in a stored procedure (T-SQL or SQLCLR).
  • Thanks Solomon, by now I converted the column to XML and I removed <xml> line. I'll try to encode the source, when I have more time. – McNets Oct 28 '20 at 8:50
  • @McNets You're welcome! I'm not sure what you mean about "encoding the source". If the data coming in is correct, then there's nothing more to do. And the data looked correct before. Just because the XML document stated that the encoding was UTF-8 doesn't mean that the data was UTF-8 when it was received by SQL Server. But, if you want to do a more thorough test, just pass in the characters (from the app) in the XML in the first code block in the NOTES section. And, I just updated to clarify a few things, so please review the changes. – Solomon Rutzky Oct 28 '20 at 19:05
  • Data comes from a web service called from a C# app. I'm not sure if the server returns it correctly encoded as utf-8. I suppose the answer is yes, it is properly encoded, But I could try to use Encoding.UTF8 before to send i to SQL server. – McNets Oct 28 '20 at 19:28
  • @McNets How the data is encoded outside of SQL Server is irrelevant, so long as each step in the process understands the encoding of data coming in and going out, even if those are two different encodings. If the C# app is connecting to SQL Server via ADO.NET, then you don't need to do anything. The data within .NET (string, XmlDocument, etc) is already UTF-16, and most (all?) drivers for connecting to SQL Server via TDS use UTF-16 to transfer the data regardless of the encoding prior to hitting that connectivity code. If the app is using XmlDocument, it shouldn't output the encoding. – Solomon Rutzky Oct 28 '20 at 21:01
  • Ok Solomon, understood. Thanks again. – McNets Oct 28 '20 at 21:07
7

Your XML stored in a varchar(max) column should look like this.

<?xml version="1.0" encoding="UTF-8"?>
<PARAM>
  <TAB DIM="30" ID="ZC3D2_1" SIZE="5">
    <LIN NUM = "1">
      <FLD NAME = "ZDOC" TYPE = "Char">Ferran López</FLD>
    </LIN>
  </TAB>
</PARAM>

The ó should be represented with a double byte value ó.

If you don't have a UTF-8 encoded string stored in your column, the right way to go about this is to remove the encoding from the XML before you convert the value to the XML datatype.

  • The code uses a stored procedure to insert the text. Could it be encoded inside the stored procedure? – McNets Oct 26 '20 at 22:07
  • @McNets I guess with CLR but not in T-SQL. If you can't fix it on the side that calls the procedure you should remove the encoding from the XML declaration before storing the XML in the table. You could argue that the XML sent to you as string parameter to the stored procedure is invalid XML and needs to be fixed where it is created. – Mikael Eriksson Oct 26 '20 at 22:10
6

I think you have a deeper problem. UTF-8 allow for more characters than the regular non-Unicode collations in SQL server. So, to be safe you should either use SQL Server 2019 which has UTF-8 collations (and I sunderstand if that isn't doable/desirable for many reasons) os use (try) nvarchar instead of varchar.

If you are afraid of storage increase going from varchar to nvarchar, you can possibly use row compression. But that requires Enterprise Edition prior to SQL Server 2016.

  • The only SQL version "approved" for this ERP is 2014. – McNets Oct 26 '20 at 15:16
  • 1
    @McNets and Tibor: Row Compression (including Unicode compression) is not going to work on a MAX type. Page compression will sometimes get you some savings, but not a substantial amount. Also, NVARCHAR is not an option if the encoding="UTF-8" portion of the <xml> tag cannot be removed as having it in there will cause an error when converting that to XML. – Solomon Rutzky Oct 27 '20 at 21:18
  • Good catch regarding the MAX type Solomon! So, since UTF-8 has a broader span of characters than a varchar allow for, the only safe way would be to removing the encoding portion and use nvarchar, as I can see... – Tibor Karaszi Oct 28 '20 at 10:18
4

use a compatible collation for your varchar

CREATE TABLE t (
  id int PRIMARY KEY,
  data varchar(max) COLLATE Latin1_General_100_CI_AI_SC_UTF8
);

INSERT INTO t VALUES
(1, N'<?xml version="1.0" encoding="UTF-8"?>
<PARAM>
  <TAB DIM="30" ID="ZC3D2_1" SIZE="5">
    <LIN NUM = "1">
      <FLD NAME = "ZDOC" TYPE = "Char">Ferran López</FLD>
    </LIN>
  </TAB>
</PARAM>
')
GO
SELECT
  id, 
  CAST(data as XML)
FROM
  t;
GO
id | (No column name)                                                                                                            
-: | :---------------------------------------------------------------------------------------------------------------------------
 1 | <PARAM><TAB DIM="30" ID="ZC3D2_1" SIZE="5"><LIN NUM="1"><FLD NAME="ZDOC" TYPE="Char">Ferran López</FLD></LIN></TAB></PARAM>

db<>fiddle here

  • It says that collation Latin1_General_100_CI_AI_SC_UTF8 is not valid. – McNets Oct 26 '20 at 15:11
  • it is only to show that you need the right collation. – nbk Oct 26 '20 at 15:14
  • @McNets the problems exist since vb 6 you have always to convert utf8 to unicode first, before storing it. Or switch to something more recent like 2017/2019 – nbk Oct 26 '20 at 15:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.