Here is another example where xml is created form a database query. In this case “for xml” clause is not used in the query since the format of the xml file needs to be a custom one. otherwise the xml document could have been created using only few code lines.
[WebMethod]
public XmlDocument Get_CC4(string CC)
{
CC = “KSMG01″ + CC.Trim();
//YEAR = YEAR.Trim();
//PERIOD = PERIOD.Trim();
SqlCommand cmd = new SqlCommand();
cmd.Connection = new SqlConnection(“server=xx;database=xx;Integrated Security=SSPI”);
cmd.CommandType = CommandType.Text;
cmd.CommandText = ” SELECT SUBSTRING(KSTAR,7,4) AS AC,” +
” WKGBTR AS AMT” +
” FROM costcenter” +
” WHERE GJAHR = ’2008′ AND OBJNR = ‘” + CC + “‘ AND PERIO <= ’03′ ” +
” ORDER BY KSTAR”;
cmd.Connection.Open();
SqlDataReader xr = cmd.ExecuteReader();
XmlDataDocument xd = new XmlDataDocument();
//create XML declaration section
//XmlNode xn = xd.CreateNode(XmlNodeType.XmlDeclaration, “”, “”);
//xd.AppendChild(xn);
XmlElement xe0, xe1, xe2, xe31, xe32;
//create the root element
xe0 = xd.CreateElement(“data”);
xd.AppendChild(xe0);
xe1 = xd.CreateElement(“variable”);
xe1.SetAttributeNode(“name”, “”);
xe1.SetAttribute(“name”, “”, “cc”);
xe0.AppendChild(xe1);
XmlText xt;
xe2 = xd.CreateElement(“row”);
xe1.AppendChild(xe2);
xe31 = xd.CreateElement(“column”);
xt = xd.CreateTextNode(“ac”);
xe31.AppendChild(xt);
xe2.AppendChild(xe31);
xe32 = xd.CreateElement(“column”);
xt = xd.CreateTextNode(“amt”);
xe32.AppendChild(xt);
xe2.AppendChild(xe32);
while (xr.Read())
{
xe2 = xd.CreateElement(“row”);
xe1.AppendChild(xe2);
xe31 = xd.CreateElement(“column”);
xt = xd.CreateTextNode(xr.GetString(0));
xe31.AppendChild(xt);
xe2.AppendChild(xe31);
xe32 = xd.CreateElement(“column”);
xt = xd.CreateTextNode(Convert.ToString(xr.GetSqlDecimal(1)));
xe32.AppendChild(xt);
xe2.AppendChild(xe32);
}
xr.Close();
cmd.Connection.Close(); ;
//DataSet xdd = xdaSet;
return xd;
}
