In SQL Server we can retrieve results of a Transact SQL query as XML by using FOR XML clause as demonstrated below.

Database table named Persons.

Transact SQL query

SELECT 
	* 
FROM 
	Persons 
WHERE 
	LastName = 'Doe' 
FOR 
	XML PATH ('Person'), ROOT ('Persons')

Results of the Transact SQL query

<Persons>
    <Person>
        <Key>1</Key>
        <FirstName>John</FirstName>
        <LastName>Doe</LastName>
    </Person>
    <Person>
        <Key>2</Key>
        <FirstName>Jane</FirstName>
        <LastName>Doe</LastName>
    </Person>
    <Person>
        <Key>3</Key>
        <FirstName>James</FirstName>
        <LastName>Doe</LastName>
    </Person>
    <Person>
        <Key>4</Key>
        <FirstName>Jena</FirstName>
        <LastName>Doe</LastName>
    </Person>
</Persons>

We can use following C# code to read the results of Transact SQL query that uses FOR XML clause.

public static string RetrieveXml()
{
    var xml = string.Empty;
    var databaseConnectionString = "Server=localhost;Database=Playground;Trusted_Connection=True;";
    var sqlQuery = "SELECT * FROM Persons WHERE LastName = 'Doe' FOR XML PATH('Person'), ROOT('Persons')";

    using (var connection = new SqlConnection(databaseConnectionString))
    {
        connection.Open();

        using (var command = new SqlCommand(sqlQuery, connection) { CommandTimeout = 0 })
        using (var xmlReader = command.ExecuteXmlReader())
        {
            var document = XDocument.Load(xmlReader);

            xml = document.ToString();
        }
    }

    return xml;
}

XDocument.Load requires that the XmlReader's ReadState be Interactive. If XmlReader's ReadState is not Interactive then XDocument.Load will try to make XmlReader's ReadState Interactive. Normally this should work without any errors however if the SQL query doesn't return any results i.e. XML returned by the SQL query is empty then XDocument.Load will throw "The XmlReader state should be Interactive." exception. Following code adds some logic to check for XmlReader's ReadState that will make sure that we don't receive "The XmlReader state should be Interactive." exception.

public static string RetrieveXml()
{
    var xml = string.Empty;
    var databaseConnectionString = "Server=localhost;Database=Playground;Trusted_Connection=True;";
    var sqlQuery = "SELECT * FROM Persons WHERE LastName = 'Doe' FOR XML PATH('Person'), ROOT('Persons')";

    using (var connection = new SqlConnection(databaseConnectionString))
    {
        connection.Open();

        using (var command = new SqlCommand(sqlQuery, connection) { CommandTimeout = 0 })
        using (var xmlReader = command.ExecuteXmlReader())
        {
            if (xmlReader.ReadState == ReadState.Initial)
            {
                xmlReader.MoveToContent();
            }

            if (xmlReader.ReadState == ReadState.Interactive)
            {
                var document = XDocument.Load(xmlReader);

                xml = document.ToString();
            }
        }
    }

    return xml;
}