title: Returning a value from store procedure and outputing in XML
body: <p>I have created this store procedure that returns the primary key of the table. I am calling this store procedure in a web service and passing through data from an XML document. I want to output the returned primary key in XML format on the web service to confirm the data was update in the database. The code for the store procedure: </p>
  
  <pre><code>Create PROCEDURE [dbo].[updateGamePlay]
      @GamePlayID int,
      @ParticipantID int,
      @GameVersionID int,
      @GameID int,
      @GameScenarioID int,
      @Start DateTime,
      @End DateTime,
      @Success varchar(10)
      AS
      UPDATE GamePlay
      SET 
      ParticipantID = @ParticipantID,GameVersionID = @GameVersionID,GameID = @GameID,GameScenarioID = @GameScenarioID,StartDateTime = @Start,EndDateTime = @End,Success = @Success
      WHERE GamePlayID = @GamePlayID
      RETURN SCOPE_IDENTITY(); 
  </code></pre>
  
  <p>The webservice assigns the data in the XML to local variables and inserts it into the database. I want to get the return type and display it in XML on the web service. </p>
  
  <pre><code>[WebMethod]
          public void AddGamePlayData()
          {
              XmlDocument xd = new XmlDocument();
              xd.Load(@"C:\Users\bradleya\Documents\Visual Studio 2010\Projects\Web Services\Web Services\addGame.xml");
              XmlNode documentNode = xd.SelectSingleNode("/GamePlayData/GamePlay");
  
              int ParticipantID = Convert.ToInt32(documentNode.SelectSingleNode("ParticipantID").InnerText);
              int GameVersionID = Convert.ToInt32(documentNode.SelectSingleNode("GameVersionID").InnerText);
              int GameID = Convert.ToInt32(documentNode.SelectSingleNode("GameID").InnerText);
              int GameSenarioID = Convert.ToInt32(documentNode.SelectSingleNode("GameScenarioID").InnerText);
              DateTime Start = Convert.ToDateTime(documentNode.SelectSingleNode("Start").InnerText);
              DateTime End = Convert.ToDateTime(documentNode.SelectSingleNode("End").InnerText);
              Boolean success = Convert.ToBoolean(documentNode.SelectSingleNode("Success").InnerText);
  
              SqlConnection oConn = new SqlConnection();
              oConn.ConnectionString = @"Data Source=SNICKERS\SQLEXPRESS;Initial Catalog=VervePhaseOne;Integrated Security=True";
              oConn.Open();
              SqlCommand cmd = new SqlCommand();
              cmd.Connection = oConn;
              cmd.CommandType = CommandType.StoredProcedure;
              cmd.CommandText = "addGamePlay";
              cmd.Parameters.Add(new SqlParameter("@ParticipantID", SqlDbType.Int));
              cmd.Parameters["@ParticipantID"].Value = ParticipantID;
              cmd.Parameters.Add(new SqlParameter("@GameVersionID", SqlDbType.Int));
              cmd.Parameters["@GameVersionID"].Value = GameVersionID;
              cmd.Parameters.Add(new SqlParameter("@GameID", SqlDbType.Int));
              cmd.Parameters["@GameID"].Value = GameID;
              cmd.Parameters.Add(new SqlParameter("@GameScenarioID", SqlDbType.Int));
              cmd.Parameters["@GameScenarioID"].Value = GameSenarioID;
              cmd.Parameters.Add(new SqlParameter("@Start", SqlDbType.DateTime));
              cmd.Parameters["@Start"].Value = Start;
              cmd.Parameters.Add(new SqlParameter("@End", SqlDbType.DateTime));
              cmd.Parameters["@End"].Value = End;
              cmd.Parameters.Add(new SqlParameter("@success", SqlDbType.Bit));
              cmd.Parameters["@success"].Value = success;
              cmd.ExecuteNonQuery();
  
          }
  </code></pre>
  
  <p>I have tried to change the return type to object and use the following code however its not working:</p>
  
  <pre><code>DataSet oDS = new DataSet();
              SqlDataAdapter oCMD = new SqlDataAdapter(cmd);
              oCMD.Fill(oDS, "GamePlay");
              return oDS.GetXml();
  </code></pre>
  
  <p>---EDIT---</p>
  
  <p>The error when I use the fix I thought should work is that the XML should contain a integer as thats what should return however it returns this XML instead:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;anyType xmlns="http://tempuri.org/" xmlns:d1p1="http://www.w3.org/2001/XMLSchema-instance" d1p1:type="q1:string" xmlns:q1="http://www.w3.org/2001/XMLSchema"&gt;
  &lt;NewDataSet /&gt;
  &lt;/anyType&gt;
  </code></pre>
  
  <p>Thanks in advance</p>