XmlReader from stored procedure with for xml raw, root returns nothing-CodePudding

Where I run the procedure below in SSMS it works fine and returns nicely formatted XML, as shown below. When I call in from .NET, however, it returns an empty zero-length string.

exec usp_posStrategyGet

returns the following result

<root><row>stuff</row></root>

Below is the code in .NET. This code has been working for years it only stopped working when I deployed the code and database to a new Windows 10 development environment which makes me think there is something wrong with the new system, but I deployed it to another Windows 10 dev environment and the same thing happened.

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Xml;
using System.Text;

public static string posStrategyGetXml()
{
    string ffdconx = ConfigurationManager.AppSettings["ffdconx"];
    SqlConnection conn = new SqlConnection(ffdconx);

    SqlCommand cmd = new SqlCommand("usp_posStrategyGet");
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Connection = conn;

    conn.Open();
    
    XmlReader myXmlReader = cmd.ExecuteXmlReader();
    myXmlReader.MoveToContent();

    string myXmlNodeString;
    StringBuilder myXmlStringBuilder = new StringBuilder();

    while (!myXmlReader.EOF)
    {
        myXmlNodeString = myXmlReader.ReadOuterXml();
        myXmlStringBuilder = myXmlStringBuilder.Append(myXmlNodeString);
    }

    string myXmlString = myXmlStringBuilder.ToString();

    myXmlReader.Close();
    conn.Close();

    return myXmlString;
}

and here is the simple procedure that I'm calling

CREATE proc [dbo].[usp_posStrategyGet]
as
set nocount on

select ghandiType, c.team, g.subpos
, CONVERT(CHAR(10),startdate,126) startdate
, CONVERT(CHAR(10),enddate,126) enddate
,  playerAffected
from ghandi_x_team g
join tblTeamCity c
on g.qs_tmid = c.qs_tmid
where 1=1
and enddate >=dbo.udf_getNextGameDate (getdate())
order by team, ghandiType, startdate, enddate

for xml raw,root

CodePudding user response：

FOR XML has a weird output. If , TYPE is specified, or it is put into a subquery or variable, then the whole XML is output as a single value.

But if , TYPE is not specified, and FOR XML is top-level (no subqueries), then the XML is split into separate nvarchar rows of 2033 characters each.

Using ExecuteXmlReader is supposed to fix that, by concatenating it all together. But it is possible that something is making that not happen properly.

You can therefore fix this by either putting it into a subquery

SELECT (
    SELECT ...
    ...
    FOR XML ...
)

Or you can add , TYPE, which will return an xml typed value.

If you are just reading it as a string then probably best to go with the first option, and you can now remove most of that C# code.

public static string posStrategyGetXml()
{
    string ffdconx = ConfigurationManager.AppSettings["ffdconx"];
    using (SqlConnection conn = new SqlConnection(ffdconx))
    using (SqlCommand cmd = new SqlCommand("usp_posStrategyGet", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        conn.Open();
        var myXmlString = (string)comm.ExecuteScalar();
        return myXmlString;
    }
}

Note also correct use of using to dispose the connection and command.