Export SQL Server as XML

Databases
1

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.

2

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.

3

I have Einhugur. I’ll check it out, although I need the output in XML proper, rather than XLS.

4

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
5

[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.