Dapper Deserializing XML

Solution 1:

No, currently it will not attempt to do anything special with this, except assign it to a string property that matches the column name. For now, I would suggest simply: separate the "get the data" and "deserialize the data into objects" steps. It is perhaps something that could be considered as an additional feature, but it isn't something that exists currently.

Solution 2:

I think Dapper has already supports XML data type as of v1.50.5 (or earlier version), it can convert XML data type to XmlDocument, XDocument or XElement.

It did convert XML data type to XElement in my code.

Sample Code on 3/5/2021

A stored procedure that returns data of type XML:

CREATE PROCEDURE spGetCarInformation
AS
DECLARE @Cfg XML
SET @Cfg = '<Configuration>
    <A>111</A>
    <B>222</B>
</Configuration>'

SELECT 1 AS Id, 'Test' AS Name, @Cfg AS [Configuration]

Code Sample:

/* Program.cs */

using System;
using System.Linq;
using System.Xml.Linq;
using Microsoft.Data.SqlClient;
using Dapper;

namespace ConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            /* query XML data from database */
            using var connection = new SqlConnection("Data Source=; Initial Catalog=; User ID=; Password=");
            Car car = connection.Query<Car>("EXEC spGetCarInformation").First();
            Console.WriteLine(car.Name);
            Console.WriteLine(car.Configuration.Element("A").Value);
            Console.WriteLine(car.Configuration.Element("B").Value);

            /* Insert XML data into database */
            car = new Car
            {
                Id = 2,
                Name = "New Car",
                Configuration = new XElement
                (
                    "Configuration",
                    new XElement("A", "333"),
                    new XElement("B", "444")
                )
            };

            string cmdText = @"CREATE TABLE #Car
(
    Id INT,
    Name NVARCHAR(128),
    Configuration XML
)

INSERT INTO #Car
VALUES
    (@Id, @Name, @Configuration)
    
SELECT * FROM #Car

DROP TABLE #Car";

            Car result = connection.Query<Car>(cmdText, car).First();
            Console.WriteLine(result.Name);
            Console.WriteLine(result.Configuration.Element("A").Value);
            Console.WriteLine(result.Configuration.Element("B").Value);
        }
    }

    class Car
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public XElement Configuration { get; set; }
    }
}

Output:

Nuget Packages added in project:

<PackageReference Include="Dapper" Version="2.0.78" />
<PackageReference Include="Microsoft.Data.SqlClient" Version="2.1.2" />

I tested the code on .NET 5, but should work on .Net Framework 4.7.2+ and System.Data.SqlClient as well.