Click here to Skip to main content
15,907,687 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Dear All,

I am using MDX Query to Export XML from DataTable.
It is creating XML file perfectly.
but if the cell value is null,it is not creating Node.
i.e if the Row[0][1]value=null,then it is not creating node.

Coding
C#
String connetionString = "Data Source=;provider=msolap;Connect Timeout=120;Initial Catalog=T_Analytics_database";
       AdomdConnection connection = new AdomdConnection(connetionString);
       AdomdDataAdapter adapter;


       DataTable dt=new DataTable("test");

       string sql = "SELECT  CROSSJOIN({[Time].[Year].[Year]},   {[Time].[Month].[Month]},{[Measures].[MAX Newsletter], [Measures].[OLS Application],[Measures].[Total Traded Demos], [Measures].[Total Initial Deposits],[Measures].[Webinar], [Measures].[Total Funded Accounts] })  ON COLUMNS,NON EMPTY({[Match Type].[Match Type].[Match Type]}) ON ROWS FROM [Assigned Form Submission]";
       string XMLfilPath = @"D:\Manas\Productnew.xml";

       try
       {
           connection.Open();
           adapter = new AdomdDataAdapter(sql, connection);
           adapter.Fill(dt);

           for (int i = 0; i < dt.Columns.Count; i++)
           {
               dt.Columns[i].AllowDBNull = true;
               dt.Columns[i].ColumnName = "c" + i;
           }
           dt.WriteXml(XMLfilPath);
           connection.Close();
           Response.Write("success");
       }
       catch (Exception ex)
       {
           Response.Write(ex.ToString());

       }
Posted
Updated 9-Jul-13 1:52am
v2

 
Share this answer
 
Comments
connect2manas 9-Jul-13 8:12am    
no it is not correct
Hi All,
this is the code.


C#
String connetionString = "Data Source=;provider=msolap;Connect Timeout=120;Initial Catalog=T_Analytics_database";
        AdomdConnection connection = new AdomdConnection(connetionString);
        AdomdDataAdapter adapter;          
        DataTable dt=new DataTable();     
        string sql = "SELECT  CROSSJOIN({[Time].[Year].[Year]},   {[Time].[Month].[Month]},{[Measures].[MAX Newsletter], [Measures].[OLS Application],[Measures].[Total Traded Demos], [Measures].[Total Initial Deposits],[Measures].[Webinar], [Measures].[Total Funded Accounts] })  ON COLUMNS,NON EMPTY({[Match Type].[Match Type].[Match Type]}) ON ROWS FROM [Assigned Form Submission]";
        connection.Open();
        adapter = new AdomdDataAdapter(sql, connection);
        adapter.Fill(dt);
        using (XmlWriter writer = XmlWriter.Create(@"D:\Manas\now.xml"))
        { writer.WriteStartDocument();
            writer.WriteStartElement("Root");
            for (int i = 0; i < dt.Rows.Count; i++)
            { 
                writer.WriteStartElement("Test");
                for (int j = 0; j < dt.Columns.Count; j++) 
                {                   
                    writer.WriteStartElement("c"+j);
                    string sss = dt.Rows[i][j].ToString();
                    writer.WriteValue(sss);
                    writer.WriteEndElement();
                } 
                writer.WriteEndElement();          
            }
            writer.WriteEndElement();
            writer.WriteEndDocument();
        }
 
Share this answer
 
v2
i have solved this using xmlwriter with datatable.
 
Share this answer
 
Comments
Naz_Firdouse 10-Jul-13 1:09am    
Please post your solution here...so that it will helpful for others...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900