ExecuteXmlReader Fails on Specific JSON from SQL Server

Question

I have an existing working process which receives generated JSON from store procedures in SQL Server using the for JSON directive. But upon receiving a specific text in the column data, there is a failure by ExecuteXmlReader in the Read operation.

Exception

XmlException: '=' is an unexpected token. The expected token is ';'. Line 1, position 94

Which if I controlled the output, I would most likely put it in a CDATA section.

Data Returned from SQL Server

JSON_F52E2B61-18A1-11d1-B105-00805F49916B
{"photoId":1000000007,"photoType":"image\/gif","photoUrl":"https:\/\/slack-imgs.com\/?c=1&url=https%3A%2F%2Fmedia0.giphy.com%2Fmedia%2F3o84U9arAYRM73AIvu%2Fgiphy-downsized.gif" }

Ultimate JSON String Which Should be Returned by Read

{
    "photoId": 1000000007,
    "photoType": "image/gif",
    "photoUrl": "https://slack-imgs.com/?c=1&url=https%3A%2F%2Fmedia0.giphy.com%2Fmedia%2F3o84U9arAYRM73AIvu%2Fgiphy-downsized.gif",
    "isActive": true
}

URL saved to Table

https://slack-imgs.com/?c=1&url=https%3A%2F%2Fmedia0.giphy.com%2Fmedia%2F3o84U9arAYRM73AIvu%2Fgiphy-downsized.gif

Ultimately this is a SQL Server 2016 change but I will need a fix sooner than what can be provided from Microsoft. So, is there a work around to handle this either by SQL or C# .Net code?

Oddly enough when one clicks on the table column value JSON in SSMS, it gives the same error.

Can you clarify your client code? I'm confused about the role of ExecuteXmlReader(), as usually FOR JSON queries are read using ExecuteReader(). — David Browne - Microsoft
– David Browne - Microsoft, Commented Jun 7, 2018 at 22:17

David Browne - Microsoft · Accepted Answer · 2018-06-07 22:42:58Z

2

I'll go ahead and propose that you are using the wrong APIs to read the FOR JSON query results. Here's a little helper class that implements a SqlCommand.ExecuteJsonReader() extension method.

    static class SqlJsonUtils
    {

        public static Newtonsoft.Json.JsonReader ExecuteJsonReader(this SqlCommand cmd)
        {
            var rdr = cmd.ExecuteReader();
            var jr = new Newtonsoft.Json.JsonTextReader(new SqlJSONReader(rdr));
            return jr;

        }

        class SqlJSONReader : System.IO.TextReader
        {
            SqlDataReader rdr;
            string currentLine = "";
            int currentPos = 0;
            public SqlJSONReader(SqlDataReader rdr)
            {
                this.rdr = rdr;
            }
            public override int Peek()
            {
                return GetChar(false);
            }
            public override int Read()
            {
                return GetChar(true);
            }
            public int GetChar(bool Advance)
            {
                while (currentLine.Length == currentPos)
                {
                    if (!rdr.Read())
                    {
                        return -1;
                    }
                    currentLine = rdr.GetString(0);
                    currentPos = 0;
                }
                int rv = (int)currentLine[currentPos];
                if (Advance) currentPos += 1;
                return rv;
            }

            public override void Close()
            {
                rdr.Close();
            }

        }


    }

answered Jun 7, 2018 at 22:42

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

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

3 Comments

ΩmegaMan Over a year ago

Does this handle the issue of the paging at 2033 characters? As per Microsoft "If you use ExecuteReader or BeginExecuteReader to access XML data, SQL Server will return any XML results greater than 2,033 characters in length in multiple rows of 2,033 characters each. To avoid this behavior, use ExecuteXmlReader or BeginExecuteXmlReader to read FOR XML queries. For more information, see article Q310378, "PRB: XML Data Is Truncated When You Use SqlDataReader," in the Microsoft Knowledge Base at support.microsoft.com."

David Browne - Microsoft Over a year ago

Yes! SqlJsonReader.GetChar(bool) will advance the DataReader as necessary. That's why I originally wrote this code.

ΩmegaMan Over a year ago

Thank you for the code. I have marked yours as the answer because it gave me what I needed to read the stream (esque) of rows properly. (Felt like I was doing .Net 1...but I digress). I have posted my final code for anyone learning who may run into this. Thanks!

ΩmegaMan · Accepted Answer · 2019-02-17 19:18:03Z

I have since made this a downloadable NUGET package. See SQLJSONReader with updates for async.

I took David's advice and marked his as the answer, but I needed the whole raw JSON.

So I added removed the call to to Newtonsoft.Json.JsonTextReader because it wouldn't return me just a string and modified David's extension class to return the whole JSON by calling ReadAll.

Note that it uses Newtonsoft's JsonConvert.DeserializeObject.

Code

public static SqlJSONReader ExecuteJsonReader(this SqlCommand cmd)
{
    var rdr = cmd.ExecuteReader();
    return new SqlJSONReader(rdr);
}

public class SqlJSONReader : System.IO.TextReader
{
    private SqlDataReader SqlReader   { get; set; }
    private string CurrentLine        { get; set; }
    private int CurrentPostion        { get; set; }

    public SqlJSONReader(SqlDataReader rdr)
    {
        CurrentLine = "";
        CurrentPostion = 0;
        this.SqlReader = rdr;
    }
    public override int Peek()
    {
        return GetChar(false);
    }
    public override int Read()
    {
        return GetChar(true);
    }
    public int GetChar(bool Advance)
    {
        while (CurrentLine.Length == CurrentPostion)
        {
            if (!SqlReader.Read())
            {
                return -1;
            }
            CurrentLine = SqlReader.GetString(0);
            CurrentPostion = 0;
        }
        var rv = CurrentLine[CurrentPostion];
        if (Advance) 
            CurrentPostion += 1;

        return rv;
    }

    public string ReadAll()
    {
        var sbResult = new StringBuilder();

        if (SqlReader.HasRows)
        {
            while (SqlReader.Read())
                sbResult.Append(SqlReader.GetString(0));

        }
        else
            return string.Empty;

        // Clean up any JSON escapes before returning
        return  JsonConvert.DeserializeObject(sbResult.ToString()).ToString();
    }

    public override void Close() { SqlReader.Close(); }
}

Usage

using (SqlConnection conn = new SqlConnection(connectionString))
    using (SqlCommand cmd = conn.CreateCommand())
{

    cmd.CommandText = "exec [dbo].[GetPhoto] @PhotoId=4";
    conn.Open();
    var rdr = cmd.ExecuteJsonReader();

    string jsonResult = rdr.ReadAll();

    conn.Close();

}

Collectives™ on Stack Overflow

ExecuteXmlReader Fails on Specific JSON from SQL Server

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related