使用LINQ的SQL到XML [英] SQL to XML using LINQ

问题描述

我正在尝试从sql表生成XML代码。



以下是我的代码

Hi,

I am trying to generate XML code from sql table.

below is my code

string Strcon = System.Configuration.ConfigurationManager.ConnectionStrings["mssp"].ConnectionString;
            SqlConnection con = new SqlConnection(Strcon);
            SqlCommand cmd = new SqlCommand("select ID, cast(DAY(report_date) as varchar(30)) + '-' + cast(month(report_date) as varchar(10)) + '-' + cast(YEAR(report_date) as varchar(10)) as REPORT_DATE ,COMPLIANT,NON_COMPLIANT from  mssp_eps_compliance_report order by ID asc",con);
            con.Open();
            SqlDataReader dr= cmd.ExecuteReader();
            
            while(dr.Read())
            {
               str1 = str1 + dr["REPORT_DATE"].ToString() + ",\n";
            }
            src = new string[] { str1 };
             
            XElement doc = new XElement("chart",
                new XAttribute("caption", str),
                new XAttribute("showLegend", "1"),
                new XAttribute("yAxisName", "% of compliance and non-compliance"),
                new XAttribute("decimalPrecision", "0"),
                new XAttribute("legentPosition", "BOTTOM"),
                new XAttribute("showvalues", "0"),

                new XElement("categories",
                    from source in src
                    let fields = source.Split(',')
                    select new XElement("Label", fields[0]))
                    );

            doc.Save(@"D:\Sri\New folder\sample.xml");
            con.Close();

我需要这样的XML

I need my XML like this

<?xml version="1.0" encoding="utf-8"?>
<chart caption="Overlall DAT Compliance" showLegend="1" yAxisName="% of compliance and non-compliance" decimalPrecision="0" legentPosition="BOTTOM" showvalues="0">
  <categories>
    <category Label="9-12-2013" />
    <category Label="9-4-2012" />
    <category Label="2-5-2013" />
    <category Label="3-6-2010" />
    <category Label="4-7-2009" />
    <category Label="5-8-2007" />
    <category Label="6-9-2006" />
    <category Label="7-10-2006" />
    <category Label="8-11-2005" />
  </categories>
</chart>

但我只得到第一排......就像这样

But I am getting only first row.. like this

<?xml version="1.0" encoding="utf-8"?>
<chart caption="Overlall DAT Compliance" showLegend="1" yAxisName="% of compliance and non-compliance" decimalPrecision="0" legentPosition="BOTTOM" showvalues="0">
  <categories>
    <category Label="9-12-2013" />
  </categories>
</chart>

请帮助。在此先感谢

Please help. Thanks in advance

推荐答案

由于您在内部XElement中选择了字段[0]，它只返回了第一个日期。

用于添加每个item作为单独的XElement，你需要迭代split var然后创建节点

尝试设置src = str1.split（'，'）然后使用field而不是field [ 0]，因为这将保持元素而不是第一个元素

Since you selected field[0] in your inner XElement, it just returned the first date.
For adding each item as a separate XElement, u need to iterate over the split var and then create the node
Try setting the src = str1.split(',') and then using "field" instead of field[0], as this will hold the element instead of the first element

通过一种简单的方法就可以做到。

请参阅以下内容......



string lsXml =< chart caption ='Overlall DAT Compliance'showLegend ='1'yAxisName ='％and compliance and non -compliance'minitPrecision ='0'legentPosition ='BOTTOM'showvalues ='0'>;

lsXml + =< categories>;

lsXml + =< category Label ='9-12-2013'/>;

lsXml + =< category Label ='9-4-2012'/>;

ls Xml + =< category Label ='2-5-2013'/>;

lsXml + =< category Label ='3-6-2010'/>;

lsXml + =< category Label ='4-7-2009'/>;

lsXml + =< category Label ='5-8 -2007'/>;

lsXml + =< category Label ='6-9-2006'/>;

lsXml + =< ; category Label ='7-10-2006'/>;

lsXml + =< category Label ='8-11-2005'/>;

lsXml + =< / categories>;

lsXml + =< / chart>;



XmlDocument loXmlDocumment = new XmlDocument（）;

loXmlDocumment.LoadXml（lsXml）;

loXmlDocumment.Save（@D：\samplexml.xml）;

Hi,
By one simple way you can do it.
See following ...

string lsXml = "<chart caption='Overlall DAT Compliance' showLegend='1' yAxisName='% of compliance and non-compliance' decimalPrecision='0' legentPosition='BOTTOM' showvalues='0'>";
lsXml += "<categories>";
lsXml += "<category Label='9-12-2013' />";
lsXml += "<category Label='9-4-2012' />";
lsXml += "<category Label='2-5-2013' />";
lsXml += "<category Label='3-6-2010' />";
lsXml += "<category Label='4-7-2009' />";
lsXml += "<category Label='5-8-2007' />";
lsXml += "<category Label='6-9-2006' />";
lsXml += "<category Label='7-10-2006' />";
lsXml += "<category Label='8-11-2005' />";
lsXml += "</categories>";
lsXml += "</chart>";

XmlDocument loXmlDocumment = new XmlDocument();
loXmlDocumment.LoadXml(lsXml);
loXmlDocumment.Save(@"D:\samplexml.xml");

这篇关于使用LINQ的SQL到XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！