XML Results to Object is mapping Null

[AlejandroFerrandiz]

Describe the bug

I'm calling a Stored Proc that return XML and I'm trying to map that to an Object but is mapping all null.

Steps to reproduce

My Object

public class MyResponse
    {
        public string Result { get; set; }

        public List<Error> Errors { get; set; }
    }

public class Error
    {
        public string RowNbr { get; set; }

        public string Description { get; set; }
    }

Stored proc

declare @response xml
...
select  @response as Response;

Stored proc call

var result = (await connection.SingleAsync<MyResponse>("[dbo].MyStoredProc", 
                    new { InputData = myInput})
                    .ConfigureAwait(false));

result.Result  == null
retult.Errors  == null

if I replace MyResponse by string

var result = (await connection.SingleAsync<string>("[dbo].MyStoredProc", 
                    new { InputData = myInput})
                    .ConfigureAwait(false));

var myResonse = Deserialize<MyResponse >(result); // <-------------- this is fine

...

public T Deserialize<T>(string input) where T : class
        {
            System.Xml.Serialization.XmlSerializer ser = new System.Xml.Serialization.XmlSerializer(typeof(T));

            using (StringReader sr = new StringReader(input))
            {
                return (T)ser.Deserialize(sr);
            }
        }

result == <MyResponse><Result>Error</Result><Errors><Error><RowNbr>1</RowNbr><Description>BC5081</Description></Error></Errors></MyResponse>

Expected behavior

result.Result  == "Error"
retult.Errors  == new List<Error> { new Error {RowNbr = "1", Description ="BC5081"}}

• Dotnet version: netstandard2.0
• Database: SQL Server
• Library version: [6.2.9]

[Jaxelr]

Take a look at the XML Tests since the usage you are describing is not gonna give return you the behavior you expect.

I will try and take some time to add some query result scenarios to the wiki page

[jonwagner]

I added a test case to make sure that SingleAsync performed XML Parsing. It works fine.

The issue is that you are expecting Insight to consider the first COLUMN as the root object. Instead, Insight treats the ROW as the root object, even when you are selecting a Single.

The returned recordset is:

Row 0
    Column Response = xml

To parse this properly you would need to return it into an object like this:

class RecordsetWithResponse {
     public MyResponse Response;
}

....

.SingleAsync<RecordsetWithResponse>(....)

Then Insight will map Row 0 to a new RecordsetWithResponse, and then parse the Response xml column into your object structure.

Note that XML parsing is not implemented for ExecuteScalar, as IDbCommand.ExecuteScalar automatically converts the XML to a string and we have no way of detecting that.