CODEWITHSUNDEEP
sqlsql-serverxml
Conrad Jagger
Conrad Jagger

Reputation: 153

SQL Server - contains an invalid XML identifier as required by FOR XML;

I'm running this query and getting below mentioned error. Can anyone help?

Column name 'Middle Name' contains an invalid XML identifier as required by FOR XML; ' '(0x0020) is the first character at fault.

SELECT 
    Username as [LastName],
    '' AS [Middle Name],
    '' AS Birthdate,
    '' AS [SSN],
    0 AS [Wage Amount]
    FROM
    Employee
     FOR XML PATH

Upvotes: 8

Views: 16967

Answers (2)

Yang C
Yang C

Reputation: 536

For the simplest case, Smith's solution works all right. Since I have constraint to keep the chars, such as space, @, ', /, etc, on my XML, finally I solved this by encoding the identifier using Base64. (Just be careful the length of the name can not depass 128 bit) Then outside where the XML would be read as input data, another small code will translate Base64 easily to original string.

CREATE FUNCTION [dbo].[ufn_string_To_BASE64]
(
    @inputString VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    RETURN (
        SELECT
            CAST(N'' AS XML).value(
                  'xs:base64Binary(xs:hexBinary(sql:column("bin")))'
                , 'VARCHAR(MAX)'
            )   Base64Encoding
        FROM (
            SELECT CAST(@inputString AS VARBINARY(MAX)) AS bin
        ) AS bin_sql_server_temp
    )
END
GO

It's important to take VARCHAR, taht will bring us shorter Base64 code.

You could add char(10), char(13) in the identifier as well.

Dynamic SQL could be help to build a temporaire table to stock intermediate data.

In my case, C# decodes the Base64 to string

if (value.StartsWith("_"))
{
    var base64Encoded = value.Substring(1).Replace('_','=');
    try
    {
        var data = System.Convert.FromBase64String(base64Encoded);
        value = Encoding.GetEncoding(1252).GetString(data);
    }
    catch (Exception e)
    {
        log.LogInformation(e.Message);
    }
}

Be care of:

  1. XML identifier could not start with numbers, so prefix with _ and remove them in c#.
  2. = is not accepted in XML identifier. That should be replace by something else like _.
  3. The Encoding in the original string when decode to string, using the right Encoding codepage, like 1252 for French char.

In real that would be more complexe than what's talking here.

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 452988

You can't have spaces in XML element or attribute names. Use

SELECT Username AS [LastName],
       ''       AS [MiddleName],
       ''       AS Birthdate,
       ''       AS [SSN],
       0        AS [WageAmount]
FROM   Employee
FOR XML PATH

Upvotes: 17

Related Questions