In MS Access VBA there’s a routine called exportXML that allows to redirect tables and/or queries to be output as XML.
It’s not particularly intuitive or efficient, but it’s what’s been used here for a while.
I’m looking at porting these to Xojo and I wondered, before I tried anything, if there’s any built-in export to XML from a SQL recordset.
I imagine not, from a quick search, but it’s one of those thing you’d rather not reinvent the wheel.
This is a reference to the typical way of doing this in Access VBA: http://www.brileigh.com/microsoft-access/export-xml-data-from-microsoft-access-part-2-using-vba/
Essentially tables with relations or queries can be pushed through the exportXML routines, and even include nested containers as needed.
I used the Einhugur Excel Writer to do this - A routine that took a RecordSet and a FolderItem as params and populated the XLS sheet with the data from the RecordSet. It is easy enough to get column names and types from the RecordSet.
I have Einhugur. I’ll check it out, although I need the output in XML proper, rather than XLS.
Sorry my mistake, I read XLS instead of XML. If you are using SQL Server it can return the result set as XML:
USE AdventureWorks2012
GO
SELECT Cust.CustomerID,
OrderHeader.CustomerID,
OrderHeader.SalesOrderID,
OrderHeader.Status
FROM Sales.Customer Cust
INNER JOIN Sales.SalesOrderHeader OrderHeader
ON Cust.CustomerID = OrderHeader.CustomerID
FOR XML AUTO
[quote=406275:@James Dooley]Sorry my mistake, I read XLS instead of XML. If you are using SQL Server it can return the result set as XML:
USE AdventureWorks2012
GO
SELECT Cust.CustomerID,
OrderHeader.CustomerID,
OrderHeader.SalesOrderID,
OrderHeader.Status
FROM Sales.Customer Cust
INNER JOIN Sales.SalesOrderHeader OrderHeader
ON Cust.CustomerID = OrderHeader.CustomerID
FOR XML AUTO
[/quote]
Ah, nice. I’ll look into this.