ALTER PROCEDURE dbo.WriteToXml
@var varchar(max),-- Root of elements
@tbl varchar(100) -- tablename
--@ResultVar XML output
AS
SET NOCOUNT ON
declare @ss as nvarchar(max)
declare @root as nvarchar(max)
declare @FName xml
declare @ResultVar as XmL
SET NOCOUNT ON
set @root = ''''+ 'MainRootName'+''''
set @ss = 'select * from '+ @tbl +' where IsActive = 1 FOR XML PATH(';
set @ss += @var;
set @ss+='),ROOT(';
set @ss+= @root;
set @ss += ')'
print(@ss)
print(convert(nvarchar(max),@ResultVar))
exec( @ss)
select @ResultVar as Result
return
---------------------------------------------------------
Coding in .cs Page::
protected void CreateXML()
{
XmlDocument xml = new XmlDocument();
DataSet ds = new DataSet();
SqlConnection con = new SqlConnection(SqlConnection);
SqlCommand cmd = new SqlCommand("WriteToXml", con);
cmd.CommandType = CommandType.StoredProcedure;
string var = "'" + DDXMLList.SelectedItem.Text + "'";
cmd.Parameters.AddWithValue("@var", var);
cmd.Parameters.AddWithValue("@tbl", DDXMLList.SelectedItem.Value);
try
{
con.Open();
string val = cmd.ExecuteScalar().ToString();
con.Close();
xml.LoadXml(val);
XmlNode node;
node = xml.DocumentElement;
foreach (XmlNode node1 in node.ChildNodes)
{
foreach (XmlNode node2 in node1.ChildNodes)
{
string val1 = node2.InnerText;
node2.InnerText = val1;
}
}
string path = Path + DDXMLList.SelectedItem.Text + ".xml";
if (Directory.Exists(Server.MapPath(Path)) == false)
{
lblmsg.Text = "Directory Not avaliable ";
Directory.CreateDirectory(Server.MapPath(Path));
}
if (File.Exists(Server.MapPath(path)) == false)
{
FileStream fs = null;
fs = File.Create(Server.MapPath(path));
fs.Close();
}
xml.Save(Server.MapPath(path));
lblmsg.Text = "XML File Generated";
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
finally
{
con.Close();
}
}
--------------------------------
If any Query Please Inform me
