DataTable to Xdocument Custom

i have a datatable that looks like the following

type                cname   ctable      text          allowgroupping
StringFilter         AAA     Table1      Good,Bad      Yes
StringFilter         BBB     Table2      Ugly          No
StringFilter         CCC     Table3      Lucky         Yes

and from that table i want to produce the following XML

<Filters Date ="25.07.2012 22:50">
<StringFilter cname="AAA" ctable="Table1" allowgroupping="Yes">Good,Bad</StringFilter>
<StringFilter cname="BBB" ctable="Table2" allowgroupping="No">Ugly</StringFilter >
<StringFilter cname="CCC" ctable="Table3" allowgroupping="Yes">Lucky</StringFilter >
</Filters>

the type column represents the tag
the cname and ctable and allowgroupping are attributes
column text represents the inner text

can you please help me achive this using linq and xDocument?

回答1:

Add a reference to System.Data.DataSetExtensions

using System.Data.DataSetExtensions;

var XDocument doc = new XDocument();
var root =  new XElement("Filters");

var items = dt.Rows.AsIEnumberable().Select(row=> new XElement("StringFilter",             new XAttribute("cname",(string) row["cname"]),
    /*additional attributes here*/
     (string) row["text"]  ));

root.Add(items);
doc.Add(root);

回答2:

@CamBruce has the correct answer.
The System.Data.DataSetExtensions is unnecessary
if you use AsEnumerable instead of AsIEnumberable.
Below is a working example with Child-Elements:

//Build DataTable for the purposes of this Example:
DataTable dt = new DataTable();
//Add Columns:
dt.Columns.Add("WidgetType");//The default Type is "string".
dt.Columns.Add("WidgetID", typeof(int));
dt.Columns.Add("WidgetName");
dt.Columns.Add("WidgetPrice", typeof(decimal));
dt.Columns.Add("SubWidget");
//Add Rows:
dt.Rows.Add("Watch",  1, "Dial",     .50, "Gear");
dt.Rows.Add("Tablet", 2, "Screen", 14.99, null);
dt.Rows.Add("Watch",  3, "Strap",   1,    "Buckle");

//Prep XML Objects:
XDocument xDoc = new XDocument();
//xDoc.Declaration = new XDeclaration("1.0", "utf-16", null);//Optional: SQL-Server already stores XML using UTF-16.  The default for XDocument is also UTF-16 when the Declaration is null. - 07/26/2018 - MCR.
XElement xRoot = new XElement("Root");
xRoot.SetAttributeValue("Date", string.Format("{0:MM/dd/yyyy hh:mm tt}", DateTime.Now));//Optional: Add Attribute to Root.

//Populate XML from DataTable:
xRoot.Add(dt.AsEnumerable()
            //.Where(r => r.Field<string>("WidgetType") == "Watch")//Optional: Add Filter.  This works.
            .Select(r => new XElement(r.Field<string>("WidgetType"),//Add Row Element
                                      //r.Field<decimal>("WidgetPrice"),//Optional: Populate Element Value.
                                      new XAttribute("WidgetID",   r.Field<int>("WidgetID")),//Optional: Add Attribute.
                                      new XAttribute("WidgetName", r.Field<string>("WidgetName")),//Optional: Add Attribute.
                                      new XElement("SubWidget", r.Field<string>("SubWidget"))//Optional: Add Child-Element.
                                     )
                   )
         );

//Finish assembling the XML:
xDoc.Add(xRoot);

//View the XML Data:
string sDoc = xDoc.ToString();//View Indented-XML as a string.

//View the XML as it would appear in a Standalone File:
System.IO.StringWriter sw = new System.IO.StringWriter();
xDoc.Save(sw);
string sWrite = sw.ToString();//This adds the XML Declaration to the string output.

sDoc String:

<Root Date="07/26/2018 04:07 AM">
  <Watch WidgetID="1" WidgetName="Dial">
    <SubWidget>Gear</SubWidget>
  </Watch>
  <Tablet WidgetID="2" WidgetName="Screen">
    <SubWidget />
  </Tablet>
  <Watch WidgetID="3" WidgetName="Strap">
    <SubWidget>Buckle</SubWidget>
  </Watch>
</Root>

sWrite String:

<?xml version="1.0" encoding="utf-16"?>
<Root Date="07/26/2018 04:07 AM">
  <Watch WidgetID="1" WidgetName="Dial">
    <SubWidget>Gear</SubWidget>
  </Watch>
  <Tablet WidgetID="2" WidgetName="Screen">
    <SubWidget />
  </Tablet>
  <Watch WidgetID="3" WidgetName="Strap">
    <SubWidget>Buckle</SubWidget>
  </Watch>
</Root>

Use this to pass XDocument as an Xml Parameter Value to a SQL Server Sproc:

cmd.Parameters.Add("@WidgetXml", SqlDbType.Xml).Value
 = new System.Data.SqlTypes.SqlXml(xDoc.CreateReader());

If writing to a File, I'd use xDoc.Save(FileName).
If you want to preview exactly the how the XML will appear in a File, then use StringWriter.
If you want a basic/simple view of your data elements while debugging / troubleshooting,
then you could use xDoc.ToString() when stepping through your code, but note that it may be missing a few things like the Declaration.

I'm no XML expert, but I think StringWriter and xDoc.Save(FileName) should add the additional/necessary information to your XML Output (even beyond the XML Declaration - depending on which Properties you set in your XML Variables).
Maybe someone could weigh in on their past experience in the comments.