>>>>I urgently need an help to generate an excel file from the XML file I attached.
>>>>The problem is when the same invoice in XML has more than one row.
>>>>I do not know how to write in the current code a While loop so that while the invoice does not change it adds a new line.
>>>>I ask for your help and attach the XML file and the current excel file it produce, and also the excel i need to produce.
>>>>
>>>
>>>Luís,
>>>
>>>Without using transformations or schemas, you can peek the different pieces of the invoice information and assemble them to produce the final XLS lines.
>>>
>>>The SELECTs and BROWSEs are for checking purposes only. Replace them with code that will mount the line.
>>>
>>Hello António,
>>
>>I have a problem because i need to pass to my excel file the content of TAG :
>>ShipTo/Address
>>I need information for TAGs, StreetName + AddressDetail + City + PostalCode + Region + Country.
>>And Also the content of TAG Tax With information of : TaxCountryRegion + TaxPercentage.
>>
>>I try to do some changes on your code but without sucess.
>>
>
>Luís,
>
>What was inserted to gather the information that you require is preceded by a "NEW" comment.
>
>You must understand what is being done or else you'll need to come back here whenever you require extra data from the XML, and that does not make much sense.
>
>I hope that this extended version will help you with that. In any case, I prefer to discuss the process and help you understand how to extend and adapt to your requirements.
>

>
>Hello António,
>
>Your are almost there Tax is OK, but Address are in one column :
>Rua dos Barbeitos n 335 Hab 1.1 4400-377 Vila Nova de Gaia
>i need to create these columns in Excel:
> StreetName + AddressDetail + City + PostalCode + Region + Country
>Theses columns are chields of Address TAG.
>
>Please save my day..!!
>
>Best regards,
>Luis

Luís,

I'm almost there?

As I said, you have to make an effort to understand what the code is doing.

It fetches whatever data is stored in specific compost elements of the XML document at the invoice and detail levels with a DOM SelectNodes() method call, transfers the data to a temporary cursor with XMLTOCURSOR(), and SCATTERs to a buffer which is only a memory representation of the cursor and, by extension, of the XML element.

When all the required information is retrieved, it can be GATHERed into a row of the final cursor.

The other address components you require are already in the address buffer. As soon as you create the fields in the cursor, at the first statement of the code (quote; "just complete with the rest"), they too will be transferred to the final result as addressdetail already was.

Do that, and you'll get there...

Hi António,

I fully understand your point of view in the sense that I have to understand the behavior of the code that sent me, without this I always tread problems.
I think I finally understood and attached your code that I changed.
This is the change I made to the code:

* this is just a subset of the line, just complete with the rest
* NEW: added AddressDetail and TaxPercentage
CREATE CURSOR xlsOutput (InvoiceNo Varchar(32), SystemEntryDate Datetime, ;
	productCode Varchar(50), productDescription Varchar(200), ;
	taxpayable Double, grosstotal Double, ;
	taxpercentage Double, addressdetail Varchar(200), streetname varchar(100), City varchar(30), Postalcode varchar(60))

LOCAL XML AS MSXML2.DOMDocument60

m.XML = CREATEOBJECT("MSXML2.DOMDocument.6.0")

m.XML.Async = .F.
m.XML.Load(GETFILE())

LOCAL Invoices AS MSXML2.IXMLDOMNodeList
LOCAL Invoice AS MSXML2.IXMLDOMNode
LOCAL DocTotals AS MSXML2.IXMLDOMNode
LOCAL Details AS MSXML2.IXMLDOMNodeList
LOCAL Detail AS MSXML2.IXMLDOMNode

* NEW: Address and TAX information in the XML file
LOCAL Addresses AS MSXML2.IXMLDOMNodeList
LOCAL Address AS MSXML2.IXMLDOMNode
LOCAL Tax AS MSXML2.IXMLDOMNode
LOCAL StreetName AS MSXML2.IXMLDOMNode

LOCAL FromHeader AS Object
LOCAL FromTotals AS Object
LOCAL FromDetail AS Object


* NEW: Address and TAX information VFP buffers
LOCAL FromAddress AS Object
LOCAL FromTax AS Object
LOCAL FromStreet AS Object

m.XML.Setproperty("SelectionNamespaces", 'xmlns:saft="urn:OECD:StandardAuditFile-Tax:PT_1.04_01"')

m.Invoices = m.XML.Selectnodes("//saft:SourceDocuments/saft:SalesInvoices/saft:Invoice")

FOR EACH m.Invoice IN m.Invoices

	XMLTOCURSOR("<vfp>" + m.Invoice.xml + "</vfp>", "tmpInvoiceHeader")
	SCATTER NAME FromHeader

	m.DocTotals = m.Invoice.selectNodes("saft:DocumentTotals").item(0)

	XMLTOCURSOR("<vfp>" + m.DocTotals.xml + "</vfp>", "tmpInvoiceTotals")
	SCATTER NAME FromTotals

	* NEW: select Address node from the invoice and store the data in a VFP buffer
	m.Address = m.Invoice.selectNodes("saft:ShipTo/saft:Address").item(0)	
	
	XMLTOCURSOR("<vfp>" + m.Address.xml + "</vfp>", "tmpInvoiceShipTo")
	SCATTER NAME FromAddress

	m.Details = m.Invoice.selectNodes("saft:Line")
	FOR EACH m.Detail IN m.Details

		XMLTOCURSOR("<vfp>" + m.Detail.xml + "</vfp>", "tmpInvoiceDetail")
		SCATTER NAME FromDetail

		* NEW: select tax information from the detail and store the data in a VFP buffer
		m.Tax = m.Detail.selectNodes("saft:Tax").item(0)
		XMLTOCURSOR("<vfp>" + m.Tax.xml + "</vfp>", "tmpTax")
		SCATTER NAME FromTax
		
 
		SELECT xlsOutput
		APPEND BLANK
		GATHER NAME m.FromHeader
		GATHER NAME m.FromTotals
		GATHER NAME m.FromDetail

		* NEW: fetch data from VFP buffers
		GATHER NAME m.FromAddress
		GATHER NAME m.FromTax		

	ENDFOR

ENDFOR

SELECT xlsOutput
BROWSE

I just wanted to ask you when I should use MSXML2.IXMLDOMNodeList, I think it will be when I have more than one row of the same document to be able to consider all rows belonging to it.
This is the code t

Thank you very much for your commitment and great help.