Solved

Xml Get subelements to Datagridview

Posted on 2013-05-23
7
236 Views
Last Modified: 2013-05-25
I have this code but only return the first line of each element InvoiceNum. For ex. Invoicenum 154 has two lines, but i can get only one line, the first. How can get all lines from each invoice.

<Documents>
	   <Sales>
		<Total>81678.600000</Total>
		<Invoice>
			<InvoiceNum>153</InvoiceNum>
			<Date>2013-03-01</Date>
			<Customer>2589</Customer>
			<Line>
				<ProductId>AT.02-2584</ProductId>
				<Description>Product 1</Description>
				<Quantity>1.000</Quantity>
				<UnitPrice>265.140000</UnitPrice>
			</Line>
		</Invoice>		
		<Invoice>
			<InvoiceNum>154</InvoiceNum>
			<Date>2013-03-01</Date>
			<Customer>2589</Customer>
			<Line>
				<ProductId>AT.02-2584</ProductId>
				<Description>Product 1</Description>
				<Quantity>1.000</Quantity>
				<UnitPrice>265.140000</UnitPrice>
			</Line>
                        <Line>
				<ProductId>AT.02-2599</ProductId>
				<Description>Product 2</Description>
				<Quantity>1.000</Quantity>
				<UnitPrice>85.100000</UnitPrice>
			</Line>
		</Invoice>	
	</Sales>
</Documents>

Open in new window

filepath1 = "C:\Users\Documents\WorkP\March2013.xml"
       Dim XmlDoc As XDocument = RemoveXmlNamespace(XDocument.Load(filepath1))
        Dim query = From xe In XmlDoc.Descendants("Invoice")
              Where xe.Element("InvoiceNo").Value = "154"
              Select New With {
                 .Factura = xe.Element("InvoiceNo").Value,
                 .Cliente = xe.Element("CustomerID").Value,
                 .Produto = xe.Element("Line").Element("ProductCode").Value
              }
        DataGridView1.DataSource = query.ToArray

Open in new window

0
Comment
Question by:rflorencio
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 
LVL 42

Accepted Solution

by:
sedgwick earned 500 total points
ID: 39190578
here:
filepath1 = "C:\Users\Documents\WorkP\March2013.xml"
       Dim XmlDoc As XDocument = RemoveXmlNamespace(XDocument.Load(filepath1))
        Dim query = From xe In XmlDoc.Descendants("Invoice")
              Where xe.Element("InvoiceNo").Value = "154"
              Select New With {
                 .Factura = xe.Element("InvoiceNo").Value,
                 .Cliente = xe.Element("CustomerID").Value,
                 .Produto = xe.Element("Line").Elements("ProductCode").Select(n=>n.Value)
              }
        DataGridView1.DataSource = query.ToArray

Open in new window

0
 
LVL 42

Expert Comment

by:sedgwick
ID: 39190581
look at line 8, instead of :
.Produto = xe.Element("Line").Element("ProductCode").Value

Open in new window

i used
.Produto = xe.Element("Line").Elements("ProductCode").Select(n=>n.Value)

Open in new window

so it returns for each line in the invoicenum, the ProductCode value.
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 39190586
now that i look at the xml there's no ProductCode element inside Line element.
did u mean u have ProductId?
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

Author Comment

by:rflorencio
ID: 39191053
I have two xml files one with ProductCode and another with ProductId. I'm a little confused (with Linq and Collections), what should put where is "n"  in the code ".Select(n=>n.Value) ".
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 39191137
I made a mistake, it should be:
 
.Produto = xe.Elements("Line").Select(n=>n.Element("ProductCode").Value)

Open in new window

this little linq code means loop through line elements and for each get productcode value.
The reason u got only the 1st one is cause u called xe.Element("Line") and not xe.Elements("Line").
xe.Element("Line") return the first one if exists.
0
 

Author Comment

by:rflorencio
ID: 39191242
Sorry. Something is wrong with me. With the code provided ".Produto = xe.Elements("Line").Select(n=>n.Element("ProductCode").Value)", an error occurs saying "n is not declared".
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 39191297
Sorry its c# i need to convert it to vb.net
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question