Xml Raw Data Returned From Sql Server 2005 Not Correct When Calling From .net

I have a stored procedure that returns XML raw data FOR XML RAW something like the following format:

Solution 1:

reader.Read() and reader.ReadOuterXml() being in the same loop would skip one line. Try this :

using (XmlReader reader = cmd.ExecuteXmlReader())
            {
               reader.Read(); //For initial first read.while (!reader.EOF)
                {

                    results.Root.Add(XElement.Parse(reader.ReadOuterXml()));
                    count += 1;

                }
            }

Solution 2:

What about if you have the query data put into a DataSet, then write that data set to XML?

publicGetXmlFromDataSet()
{
    DataSetmyDataSet=newDataSet();
    DbDataAdaptermyDataAdapter= DatabaseFactory.CreateDataAdapter();
    DbConnectionDatabaseConnection=newDatabaseConnection(/*put appropriate values here*/);
    XmlTextWritermyXmlTextWriter=newXmlTextWriter(/*put appropriate values here*/)

    myDataDapter.SelectCommand = DatabaseFactory.CreateCommand();
    myDataDapter.SelectCommand.CommandType = CommandType.StoredProcedure;
    myDataAdapter.CommentText = "EXEC MyStoredProc";
    myDataAdapter.SelectCommand.Connection = DatabaseConnection;
    myDataAdapter.Fill(myDataSet);
    myDataSet.WriteXml(myXmlWriter);
}

Share