如何自动将表导出到MSSQL或Access中的正确XML文件？

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.

我们有客户以XML格式请求数据。通常这不是必需的,因为我们通常只是交出Access数据库或csv文件,这就足够了。但是在这种情况下,我需要自动从十几个表中导出适当的XML。

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.

如果我能从SQL Server 2005中完成,那将是首选。但是我不能为我的生活找到办法做到这一点。我可以转储原始的xml数据,但这只是每行带有属性值的标记。我们需要一些代表表格结构的东西。 Access具有xml格式的导出,可满足我们的需求。但是我不确定这是如何实现自动化的。它似乎没有通过SQL以任何方式提供,所以我试图通过宏或vbscript来追踪必要的代码来导出XML。

Any suggestions?

4 个解决方案

#1

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

考虑使用FOR XML AUTO。根据您的要求,您可能需要使用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.

这将生成一个嵌套的XML文档,其中包含客户内部的订单。然后,您可以使用SSIS将其导出到文件中,我认为很容易。我自己没有尝试过。

#2

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.

如果您想要一个文档而不是一个片段,您可能需要一个由两部分组成的解决方案。但是,这两个部分都可以在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.

它从Tom的条目的评论中看起来就像你找到了ELEMENTS参数一样,所以你将字段作为子元素而不是属性。但是,你仍然会得到一个片段,因为你不会得到一个根节点。

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

有不同的方法可以解决这个问题。 SQL Server提供了一种使用XSLT转换XML文档的方法,因此您可以创建一个XSL样式表来将查询结果包装在根元素中。您还可以添加客户架构所需的任何其他内容(假设它们有一个)。

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:

如果您想将某些字段保留为属性并创建其他元素,您还可以使用XSLT移动这些字段,因此最终可能会出现以下情况:

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

#3

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.

这里有一个用于将数据从访问数据库导出到xml文件的宏的大纲,这可能对您有用。

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"

#4

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

我能想到的最简单的方法是创建一个小应用程序来为您完成。您可以将其作为基本的WinForm,然后只使用LinqToSql dbml类来表示您的数据库。大多数情况下,您可以使用XmlSerializer命名空间序列化这些对象。有时,根据数据库的复杂程度,它比这更困难。查看这篇文章,了解有关LinqToSql和Xml序列化的一些详细信息:http://www.west-wind.com/Weblog/posts/147218.aspx

Hope that helps.

希望有所帮助。

#1