>>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,
>
>Thanks for reply and your great coding.
>I need only just a little help, could you write the command to mount and create Excel file result for each line like my excel attach file named : saft_01-06-2019_30-06-2019_That i want produce.xml.zip ?
>
>Best regards and many thanks,
>Luis

Luís, this is a demonstration on how you can do this. You'll have to fill the gaps.

* this is just a subset of the line, just complete with the rest
CREATE CURSOR xlsOutput (InvoiceNo Varchar(32), SystemEntryDate Datetime, ;
	productCode Varchar(50), productDescription Varchar(200), ;
	taxpayable Double, grosstotal Double)

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

LOCAL FromHeader AS Object
LOCAL FromTotals AS Object
LOCAL FromDetail 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

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

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

		SELECT xlsOutput
		APPEND BLANK
		GATHER NAME m.FromHeader
		GATHER NAME m.FromTotals
		GATHER NAME m.FromDetail

	ENDFOR

ENDFOR

SELECT xlsOutput
BROWSE

Two notes: if it was a probIem I was required to solve, I would transform the XML into a CSV file (or into an XML ready to be XMLTOCURSORed()), but that's because XLST is a kind of second skin for me (regardless of being the right tool for the job, as I see it). I believe it would be more difficult for you, so this approach that I suggested is easily adaptable to your requirements. I also tried to ingest the SAFT schema into an XMLAdapter object, but since it raise an error and I don't feel like to feed the XML 4.0 beast any longer, I swiftly gave up on that.


Hello Antonio,
thank you very much for your response and for sending the code example so that I can adapt.
I think it will help me to overcome this issue.

Best regards and good weekend,
Luis