Hello. It is necessary to pull out the data from the xml-file. The incomprehensibility is that it has 2 levels of nesting (not counting the root container itself). The data from there must be placed in the DataTable table, but there are 2 levels here, so in 2 tables? The linq query itself did not cause any problems. But now how to put the data in 2 tables for me a question .. (How to process el.prices )

Code:

XDocument doc = XDocument.Load("products_OUT2.xml"); var query = from product in doc.Root.Elements("PRODUCT") select new { code = (Int32)product.Element("CODE"), name = (string)product.Element("NAME"), vendor = (string)product.Element("VENDOR"), country = (string)product.Element("COUNTRY"), vendorbarcode = (string)product.Element("VENDORBARCODE"), valid_date = (DateTime)product.Element("VALID_DATE"), qtty = (Int32)product.Element("QTTY"), packqtty = (Int32)product.Element("PACKQTTY"), prices = ( from price in product.Elements("PRICES") select new { area = (string)price.Element("PRICE").Attribute("area").Value, prc = (double)price.Element("PRICE") } ) }; foreach (var el in query) { dt.Rows.Add( el.code, el.name, el.vendor, el.country, el.vendorbarcode, el.valid_date, el.qtty, el.packqtty, el.prices = ? ); }

Xml itself:

 <?xml version="1.0" standalone="yes"?> <DocumentElement> <PRODUCT> <CODE>73</CODE> <NAME>Бессмертника цветки 30г ф/чай </NAME> <VENDOR>ООО "Алтай-Фарм"</VENDOR> <COUNTRY>РОССИЯ</COUNTRY> <VENDORBARCODE>4607102610548</VENDORBARCODE> <VALID_DATE>2019-01-01T00:00:00+07:00</VALID_DATE> <QTTY>358</QTTY> <PACKQTTY>1</PACKQTTY> <PRICES> <PRICE area="1">78.50</PRICE> <PRICE area="2">78.90</PRICE> <PRICE area="3">82.30</PRICE> <PRICE area="4">86.10</PRICE> </PRICES> </PRODUCT> <PRODUCT> <CODE>76</CODE> <NAME>Гигрометр психрометрический ВИТ-1 (0-25 град.C)</NAME> <VENDOR>Стеклоприбор</VENDOR> <COUNTRY>УКРАИНА</COUNTRY> <VENDORBARCODE>9920007031665</VENDORBARCODE> <VALID_DATE>2025-01-01T00:00:00+07:00</VALID_DATE> <QTTY>166</QTTY> <PACKQTTY>1</PACKQTTY> <PRICES> <PRICE area="1">78.50</PRICE> <PRICE area="2">78.90</PRICE> <PRICE area="3">82.30</PRICE> <PRICE area="4">86.10</PRICE> </PRICES> </PRODUCT> </DocumentElement>

  • Suppose you can invest, and then what? And what el.prices not invested? Cast to object type. Inspector see type. - nick_n_a
  • If you are given an exhaustive answer, mark it as correct (a daw opposite the selected answer). - Nicolas Chabanovsky ♦
  • And then, 2 labels are easy to insert into the SQL database. This sample of their XML corresponds exactly to my data model on the basis of which the database was generated. Just as I understood linq to xml> datatable's> database is the fastest, most reliable and flexible way to do this .. - Me_Shock

3 answers 3

Use two tables, one for PRODUCT, the other for PRICE. The key in the PRICE table is the product code.

 foreach (var el in query) { dt.Rows.Add( el.code, el.name, el.vendor, el.country, el.vendorbarcode, el.valid_date, el.qtty, el.packqtty ); foreach (var pr in el.prices) dtPrice.Rows.Add(el.code, pr.area, pr.prc); }
  • This is exactly the answer I was hoping to get !!! I just didn’t have enough " ); " after el.packqtty Made a price listing EXACTLY INSIDE product listing, but for some reason only the first entries are displayed in the dtPrice price tag . Only the first price for each item. - Me_Shock

You will first decide how it will be more convenient for the user to view and edit this data. It is possible to place everything in one table, it is possible in several.

For example, you can load all xml into DataSet one method ReadXml :

 var dataSet = new DataSet(); dataSet.ReadXml("test.xml");

In this case, there are three tables in the dataset: PRODUCT, PRICES (empty, you can not show it to the user) and PRICE (with the columns area and PRICE_text).

The easiest way to DataGridView these tables is by tying them to a DataGridView :

 dataGridView.DataSource = dataSet.Tables[0];

If any columns are not needed, you can hide them; headers can be renamed:

 dataGridView.DataSource = dataSet.Tables[2]; dataGridView.Columns["area"].Visible = false; dataGridView.Columns["PRICE_text"].HeaderText = "Price";

The problem here is that if the user adds new rows directly to the DataGridView , then storing the data using the DataSet.WriteXml method may cause the lines to appear at all where they would like. That is, a new table will appear. Therefore, this method is only suitable for displaying data without editing them.

  • Understood you.) Data will not be displayed, users will not be either, pushing xml into datatable to be pushed into the database. At this stage, I just need to duplicate the data in my database, which 1C uploads to xml. - Me_Shock

Have helped. Below is a working linq query to the xml file and insertion into 2 tables.

 public void GetPricesXml(string fullpath) { var dtPRODUCTS = new DataTable(); dtPRODUCTS.Columns.Add("CODE", typeof(Int32)); dtPRODUCTS.Columns.Add("NAME", typeof(string)); dtPRODUCTS.Columns.Add("VENDOR", typeof(string)); dtPRODUCTS.Columns.Add("COUNTRY", typeof(string)); dtPRODUCTS.Columns.Add("VENDORBARCODE", typeof(string)); dtPRODUCTS.Columns.Add("VALID_DATE", typeof(DateTime)); dtPRODUCTS.Columns.Add("QTTY", typeof(Int32)); dtPRODUCTS.Columns.Add("PACKQTTY", typeof(Int32)); // var dtPRICES = new DataTable(); dtPRICES.Columns.Add("PRODUCTSCODE", typeof(Int32)); dtPRICES.Columns.Add("AREASCODE", typeof(Int32)); dtPRICES.Columns.Add("PRICE", typeof(Double)); //----------------------------------------------------------------- XDocument doc = XDocument.Load("products_OUT2.xml"); var query = from product in doc.Root.Elements("PRODUCT") select new { code = (Int32)product.Element("CODE"), name = (string)product.Element("NAME"), vendor = (string)product.Element("VENDOR"), country = (string)product.Element("COUNTRY"), vendorbarcode = (string)product.Element("VENDORBARCODE"), valid_date = (DateTime)product.Element("VALID_DATE"), qtty = (Int32)product.Element("QTTY"), packqtty = (Int32)product.Element("PACKQTTY"), prices = product.Element("PRICES").Descendants("PRICE").Nodes().Select(price => new { area = (Int32)price.Parent.Attribute("area"), prc = (Double)price.Parent }) }; foreach (var prod in query) { dtPRODUCTS.Rows.Add( prod.code, prod.name, prod.vendor, prod.country, prod.vendorbarcode, prod.valid_date, prod.qtty, prod.packqtty ); foreach (var pp in prod.prices) { dtPRICES.Rows.Add( prod.code, pp.area, pp.prc ); } } }