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? JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

Answer 1

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.