如何将数据从SQL Server 2005导入和导出为XML格式？

I am very confused about how SQL Server 2005 supports importing and exporting of XML files. I was thinking that BCP was the way to go. However after reading all of the documentation it seems like there is no way to just take a regular table and export it in XML format. The reason that I am asking is that I am working with a client that will be sending me data updates in XML format. We both have the same database with identical structures. He has sent me example XML files. I was assuming that there would be some very simple way to import the data files but I can't figure it out. Can anyone help me understand how to take an XML file and import the data in to an existing table? It should be simple because the XML file was generated from a table with the exact same structure.

我对SQL Server 2005如何支持导入和导出XML文件感到非常困惑。我以为BCP是要走的路。但是在阅读完所有文档之后,似乎没有办法只使用常规表并以XML格式导出它。我问的原因是我正在与一个客户端合作,该客户端将以XML格式向我发送数据更新。我们都有相同结构的相同数据库。他给我发了示例XML文件。我假设有一些非常简单的方法来导入数据文件,但我无法弄清楚。任何人都可以帮助我了解如何获取XML文件并将数据导入现有表吗?它应该很简单,因为XML文件是从具有完全相同结构的表生成的。

Thanks, Corey

4 个解决方案

#1

Why on earth use XML as exchange format in the first place?

为什么首先使用XML作为交换格式?

If you have the exact same data model on both sides plain text files or BCP files will perfectly fit your needs and also be way smaller in size (ie extract and load faster)!

如果您在双方都拥有完全相同的数据模型,纯文本文件或BCP文件将完全符合您的需求,并且尺寸更小(即提取和加载速度更快)!

Just because XML is "state of the art" it dosn't meen it is always the best choice! Of course if you belive that you some time in the future need to exchange data with 3rd parties not sharing the same data model or even the same platform or interface the case is of course different.

仅仅因为XML是“最先进的”它不应该是它最好的选择!当然,如果你相信未来的某个时候需要与不共享相同数据模型甚至同一平台或接口的第三方交换数据,那么情况当然是不同的。

#2

SQL Server 2005 allows to store XML data in two ways: - As a Rowset & - An XML Column

SQL Server 2005允许以两种方式存储XML数据: - 作为Rowset& - XML列

Storing XML Data in a Rowset

在行集中存储XML数据

If your client has sent you data as an XML document and you need to store this data in a database table, first you need to "shred" the XML data. SQL Server allows you to shred XML data by using the OPENXML function and its related stored procedures.

如果您的客户端已将数据作为XML文档发送,并且您需要将此数据存储在数据库表中,则首先需要“粉碎”XML数据。 SQL Server允许您使用OPENXML函数及其相关的存储过程来分解XML数据。

Shredding XML document involves the following tasks:

碎化XML文档涉及以下任务:

i) Parse the XML document SQL server 2005 provides the sp_xml_preparedocument stored procedure to parse the xml document. This stored procedure reads the xml document and parses it with the MSXML parser. The parsed document is an internal tree representation of various nodes in the xml doc such as elements,attributes,text and comments.

i)解析XML文档SQL Server 2005提供了sp_xml_preparedocument存储过程来解析xml文档。此存储过程读取xml文档并使用MSXML解析器解析它。解析的文档是xml doc中各种节点的内部树表示,例如元素,属性,文本和注释。

ii)Retrieve the rowset from the tree. Now you need to extract the data from the available xml data. You use openxml function for this purpose and to generate an in-memory rowset from the parsed data. Syntax: openxml(idoc int[in],rowpattern nvarchar[in],[flags byte[in]]) [WITH (SchemaDeclaration | TableName)]
idoc- specifies the doc handle of the internal tree representation of xml doc rowpattern- specifies the XPath pattern used to identify the nodes to be processed as rows. flags- indicates the mapping that should be used between xml data and relational rowset, and how the spill-over column should be filled. It is optional and can have 0,1,2,3 to use default mapping, to retrieve attribute values, to retrieve element values and to retrieve both values respectively. SchemaDeclaration- specifies the rowset schema declaration for the columns to be returned by using a combination of column names, data types and patterns. TableName- specifies the table name that can be given instead of SchemaDeclaration, if a table with the desired schema already exists and no patterns are required.

ii)从树中检索行集。现在,您需要从可用的xml数据中提取数据。为此目的使用openxml函数,并从解析的数据生成内存中的行集。语法:openxml(idoc int [in],rowpattern nvarchar [in],[flags byte [in]])[WITH(SchemaDeclaration | TableName)] idoc-指定xml doc rowpattern的内部树表示的doc句柄 - 指定XPath模式用于标识要作为行处理的节点。 flags-表示应在xml数据和关系行集之间使用的映射,以及如何填充溢出列。它是可选的,可以使用0,1,2,3来使用默认映射,检索属性值,检索元素值以及分别检索这两个值。 SchemaDeclaration-使用列名,数据类型和模式的组合为要返回的列指定行集模式声明。 TableName-指定可以给出的表名而不是SchemaDeclaration,如果已存在具有所需模式的表且不需要模式。

iii)Store the data from the rowset. You can use the rowset created by openxml to store the data, in the same way that you would sue any other rowset. You can insert the rowset data into permanent tables in a database.

iii)存储行集中的数据。您可以使用openxml创建的行集来存储数据,就像您起诉任何其他行集一样。您可以将行集数据插入数据库中的永久表中。

iv)Clear the memory. You need to release the memory where you stored the rowset. For this, you use the sp_xml_removedocument stored procedure.

iv)清除记忆。您需要释放存储行集的内存。为此,您使用sp_xml_removedocument存储过程。

For example, following is the data available in an XML doc:

例如,以下是XML文档中可用的数据:

    DECLARE @Doc int
    DECLARE @XMlDoc nvarchar(1000)
    SET @XMLDoc = N'<ROOT>
    <Customer CustomerID="JHO1" Name="Jack">
      <Order OrderID="1001" CustomerID="JH01"
             OrderDate="2009-07-04T00:00:00">
      <OrderDetail ProductID="11" Quantity="12"/>
      <OrderDetail ProductID="22" Quantity="10"/>
     </Order>
    </Customer>
<Customer CustomerID="SG01" Name="Steve">
     <Order OrderID="1002" CustomerID="SG01"
            OrderDate="2009-08-16T00:00:00">
    <OrderDetail ProductID="32" Quantity="3"/>
   </Order>
  </Customer>
</ROOT>'

To view this xml data in a rowset, you need to execute the following statements:

要在行集中查看此xml数据,您需要执行以下语句:

1. Create internal representation of xml document EXEC sp_xml_preparedocument @Doc OUTPUT, @XMLDoc

1.创建xml文档的内部表示形式EXEC sp_xml_preparedocument @Doc OUTPUT,@ XMLDoc

2. Execute the following query to store the data in a table using OPENXML function:

2.执行以下查询以使用OPENXML函数将数据存储在表中:

INSERT INTO CustomerDetails
 SELECT *
 FROM openxml (@Doc, '/ROOT/Customer' , 1)
      WITH (CustomerID varchar(10),
            Name varchar(20) )

The data will be displayed as in the table:

数据将显示在表格中:

CustomerID | Name    |
___________|_________|
JH01       | Jack    |
           |         | 
SG01       | Steve   |
___________|_________|

3. Remove the internal tree from the memory by executing

3.执行以从内存中删除内部树

EXEC sp_xml_removedocument @Doc

You're done now.

你现在完成了。

I think this method would help rather than the other one, ie, storing xml data as XML Column.

我认为这种方法比其他方法更有帮助,即将xml数据存储为XML列。

#3

Importing XML into SQL Server

将XML导入SQL Server

Search XML Column in SQL

在SQL中搜索XML列

You have to parse the XML as into a table like format. Remember: xml is just text so you have to tell SQL Server what datatypes are etc.

您必须将XML解析为格式化的表格。请记住:xml只是文本,因此您必须告诉SQL Server有哪些数据类型等。

There is no one shot magic bullet INSERT MyTable FROM File = 'bob.xml'

没有一个魔术弹INSERT MyTable FROM File ='bob.xml'

#4

I assume the client is using FOR XML to wrap the relational data as an xml document? There is no automagic way to shred the xml back to the original relational structure, but you should be able to do it programatically if the original xml generation also uses the XMLSCHEMA directive. If the client includes this, then an xml schema will be generated that describes which column (and its type) is represented where in the xml instance. I started playing with this approach awhile back but never finished it.

我假设客户端使用FOR XML将关系数据包装为xml文档?没有自动方法将xml切换回原始关系结构,但如果原始xml生成也使用XMLSCHEMA指令,则应该能够以编程方式执行此操作。如果客户端包含此项,那么将生成一个xml架构,该架构描述哪个列(及其类型)在xml实例中的位置。我开始玩这种方法一段时间但从未完成它。

#1