How can I automate exporting of tables into proper XML files from MSSQL or Access?

Question

We have a customer requesting data in XML format. Normally this is not required as we usually just hand off an Access database or csv files and that is sufficient. However in this case I need to automate the exporting of proper XML from a dozen tables.

If I can do it out of SQL Server 2005, that would be preferred. However I can't for the life of me find a way to do this. I can dump out raw xml data but this is just a tag per row with attribute values. We need something that represents the structure of the tables. Access has an export in xml format that meets our needs. However I'm not sure how this can be automated. It doesn't appear to be available in any way through SQL so I'm trying to track down the necessary code to export the XML through a macro or vbscript.

Any suggestions?

Answer 1

There's an outline here of a macro used to export data from an access db to an xml file, which may be of some use to you.

Const acExportTable = 0

Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "C:\Scripts\Test.mdb"

'Export the table "Inventory" to test.xml
objAccess.ExportXML acExportTable,"Inventory","c:\scripts\test.xml"

Answer 2

The easiest way to do this that I can think of would be to create a small app to do it for you. You could do it as a basic WinForm and then just make use of a LinqToSql dbml class to represent your database. Most of the time you can just serialize those objects using XmlSerializer namespace. Occasionally it is more difficult than that depending on the complexity of your database. Check out this post for some detailed info on LinqToSql and Xml Serialization: http://www.west-wind.com/Weblog/posts/147218.aspx

Hope that helps.

Answer 3

Look into using FOR XML AUTO. Depending on your requirements, you might need to use EXPLICIT.

As a quick example:

SELECT
    *
FROM
    Customers
INNER JOIN Orders ON Orders.CustID = Customers.CustID
FOR XML AUTO

This will generate a nested XML document with the orders inside the customers. You could then use SSIS to export that out into a file pretty easily I would think. I haven't tried it myself though.

Answer 4

If you want a document instead of a fragment, you'll probably need a two-part solution. However, both parts could be done in SQL Server.

It looks from the comments on Tom's entry like you found the ELEMENTS argument, so you're getting the fields as child elements rather than attributes. You'll still end up with a fragment, though, because you won't get a root node.

There are different ways you could handle this. SQL Server provides a method for using XSLT to transform XML documents, so you could create an XSL stylesheet to wrap the result of your query in a root element. You could also add anything else the customer's schema requires (assuming they have one).

If you wanted to leave some fields as attributes and make others elements, you could also use XSLT to move those fields, so you might end up with something like this:

<customer id="204">
    <firstname>John</firstname>
    <lastname>Public</lastname>
</customer>