Solved

Xml Get subelements to Datagridview

Posted on 2013-05-23
7
229 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
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!

 

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

Suggested Solutions

Title # Comments Views Activity
VB.Net - MemoryMappedFiles - Confirm receipt 2 36
vb.net class 3 28
How to seperate number and letters from a string 3 23
DataGridView / get bound table name? 8 36
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

735 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