ssis导入xml_使用SSIS包将XML文档导入SQL Server表

ssis导入xml

This article guides you through importing XML documents into SQL tables using SSIS packages.

本文指导您使用SSIS包将XML文档导入到SQL表中。

XML简介 (Introduction to XML)

Extensible Markup Language(XML) is a simplified markup language. It is an open-source W3C standard language and mostly used to store and transport data. It was derived from the Standard Generalized Markup Language (SGML). It is different from the HTML but has some similarities with it.

可扩展标记语言（XML）是一种简化的标记语言。它是一种开放源代码的W3C标准语言，主要用于存储和传输数据。它源自标准通用标记语言（SGML）。它不同于HTML，但有一些相似之处。

HTML is static while XML is in dynamic in nature
HTML是静态的，而XML是动态的
In HTML, we use pre-defined tags while XML uses user-defined tags
在HTML中，我们使用预定义标签，而XML使用用户定义标签
XML is a case sensitive language while HTML is not
XML是区分大小写的语言，而HTML不是
HTML focuses on the data presented while XML focus is on storing and transporting data
HTML专注于呈现的数据，而XML专注于存储和传输数据

XML Schema Definition(XSD) is used to validate, format and describe an XML file. A document should be validated against the XSD to be a valid XML file. An XML document is valid if the XSD defines the tags, attributes for it. XSD is also a recommendation of W3C (World Wide Web Consortium)

XML模式定义（XSD）用于验证，格式化和描述XML文件。应对照XSD验证文档为有效的XML文件。如果XSD定义了标签及其属性，则XML文档有效。 XSD也是W3C（万维网联盟）的推荐

Sometimes, we work with the various data format and gets the requirement to load the data into SQL Server. Source of data might be a different relational database, Excel, CSV, HTML.

有时，我们使用各种数据格式，并且需要将数据加载到SQL Server中。数据源可能是不同的关系数据库，Excel，CSV，HTML。

We might get a requirement to load XML data into SQL Server tables. In the article, Working with XML Data in SQL Server, we talked about SQL Server data into XML formats. I encourage you to go through this article to understand data conversion in XML format from SQL tables.

我们可能需要将XML数据加载到SQL Server表中。在文章“ 在SQL Server中使用XML数据”中，我们讨论了将SQL Server数据转换为XML格式。我鼓励您阅读本文以了解从SQL表以XML格式进行的数据转换。

SQL Server Integration package (SSIS Package) is an ETL (Extract-Transform-Load) tool to work with complex data structures, transform and load into SQL Server or any other destinations. In this article, we will explore the process of using an SSIS package to load XML files into SQL Server tables.

SQL Server集成软件包（SSIS软件包）是一种ETL（提取，转换，加载）工具，用于处理复杂的数据结构，转换并加载到SQL Server或任何其他目标中。在本文中，我们将探讨使用SSIS包将XML文件加载到SQL Server表中的过程。

样本数据和先决条件 (Sample data and prerequisites)

In this article, we use the following XML to upload into SQL Server. I downloaded this sample XML from the Microsoft Docs.

在本文中，我们使用以下XML上载到SQL Server。我从Microsoft Docs下载了此示例XML。

Install Visual Studio 2019 with SQL Server Data Tools. You can go through Microsoft docs for download and install it
使用SQL Server数据工具安装Visual Studio 2019。您可以浏览Microsoft文档以下载并安装它

You should gain basic knowledge of SSIS packages before proceeding with this article. In case you are a beginner, you can go through SSIS articles on SQLShack.

在继续本文之前，您应该了解SSIS软件包的基本知识。如果您是初学者，则可以阅读有关SQLShack的SSIS文章。

使用SSIS包将XML数据导入SQL Server的步骤 (Steps to import XML data into SQL Server using an SSIS package)

Launch the Visual Studio 2019 and create an integration service project. You should choose an appropriate project name and SSIS package. It is not a requirement but a best practice to recognize projects with their names.

启动Visual Studio 2019并创建一个集成服务项目。您应该选择适当的项目名称和SSIS包。识别带有名称的项目不是必需的，而是最佳实践。

In the SSIS package, add a Data Flow task in the control flow area. Right-click on it and rename the data flow task.

在SSIS包中，在控制流区域中添加一个数据流任务 。右键单击它，然后重命名数据流任务。

As you know, we have a source data in the XML format. For this purpose, we use an XML Source task from the SSIS package toolbox. Drag this XML Source in the Data Flow. SSIS adds the task in the data flow task created earlier.

如您所知，我们有XML格式的源数据。为此，我们使用SSIS包工具箱中的XML Source任务。将此XML源拖到数据流中。 SSIS将任务添加到先前创建的数据流任务中。

For the source configuration, double-click on the XML Source, and you get an XML Source editor.

对于源配置，双击XML Source，您将获得XML Source编辑器。

We have the following configuration option in the source editor.

在源代码编辑器中，我们具有以下配置选项。

资料存取模式 (Data Access Mode)

By default, you get the data access mode as XML File Location. It is the appropriate option for us because of our source data in an XML file. We can choose other options – XML file from variable and XML data from the variable.

默认情况下，您获得的数据访问模式为XML文件位置。由于我们的源数据在XML文件中，因此这是适合我们的选项。我们可以选择其他选项-变量中的XML文件和变量中的XML数据。

XML位置 (XML Location)

In the XML location, browse to the XML file directory. Once you select the XML file, you get an error that we have not defined the XML schema (XSD).

在XML位置中，浏览到XML文件目录。选择XML文件后，您会得到一个错误，我们尚未定义XML模式（XSD）。

使用内联架构 (Use inline schema)

If we have an XML schema defined in the XML file itself, we can use this option. You should verify your XML file if the XML file contains XSD or not.

如果我们在XML文件本身中定义了XML模式，则可以使用此选项。如果XML文件包含XSD，则应验证XML文件。

XSD位置 (XSD Location)

If we have a separate file for the XML definition, we can browse to the file and select the XSD file. We do not have an XSD file in my demo. In most cases, if we get an XML file from any external source, you also get an appropriate XSD file.

如果我们有一个单独的XML定义文件，则可以浏览到该文件并选择XSD文件。我的演示中没有XSD文件。在大多数情况下，如果我们从任何外部来源获得XML文件，那么您还将获得适当的XSD文件。

产生XSD (Generate XSD)

SSIS provides you with an option to generate the XSD file from the XML file. This option works fine, however, for a complex XML file, and you should supply the XSD file separately in the XSD Location.

SSIS为您提供了一个从XML文件生成XSD文件的选项。但是，对于复杂的XML文件，此选项可以正常工作，您应该在XSD位置中单独提供XSD文件。

Let’s click on the Generate XSD option. It generates the XSD in the same folder as of the XML file. You can specify a separate location as well, if required. It is better to generate the file in the source location to avoid any conflict.

让我们单击“生成XSD”选项。它在与XML文件相同的文件夹中生成XSD。如果需要，您也可以指定一个单独的位置。最好在源位置生成文件以避免任何冲突。

Let’s open the XSD file in a suitable program such as Edit Plus or Visual Studio. In the following image, we see that it has a definition for each data column along with their data type. We should be careful in making changes to this XSD because XML validation will fail in case of any consistency issue between XML and XSD files.

让我们在合适的程序（例如Edit Plus或Visual Studio）中打开XSD文件。在下图中，我们看到它为每个数据列及其数据类型定义了一个定义。我们对此XSD进行更改时应谨慎，因为如果XML和XSD文件之间存在任何一致性问题，XML验证将失败。

Click OK, and it reports the following warning messages. We can ignore this warning message. It does not stop us from importing the data. To resolve this error, you can specify the minimum and maximum length in the XSD file for each column. You should know XML language knowledge for making changes in XML and XSD files.

单击确定，它将报告以下警告消息。我们可以忽略此警告消息。它不会阻止我们导入数据。要解决此错误，您可以在XSD文件中为每一列指定最小和最大长度。您应该了解用于更改XML和XSD文件的XML语言知识。

Click OK, and you see the data columns available in the XML file and XSD. If we want to stop the operation, click on Cancel.

单击“确定”，您会看到XML文件和XSD中可用的数据列。如果我们要停止操作，请单击“取消”。

Click OK, and you can see the XML source task is configured successfully. We do not get a red cross icon once a task configuration completes.

单击“确定”，您可以看到XML源任务已成功配置。任务配置完成后，我们不会出现红叉图标。

We want to insert data into the SQL Server table. For this, add an OLE DB Destination task and configure it with your instance details.

我们想将数据插入到SQL Server表中。为此，添加一个OLE DB Destination任务，并使用您的实例详细信息对其进行配置。

If you have an existing SQL table, you can select the table from the drop-down. Otherwise, click on New and specify a table name. SSIS automatically detects the data columns and their data types. You can change the data type and its value in the Create Table script as well if required. You should configure an appropriate data type and its value to avoid any data truncation.

如果您有现有SQL表，则可以从下拉列表中选择表。否则，单击新建并指定表名称。 SSIS自动检测数据列及其数据类型。如果需要，您也可以在“ 创建表”脚本中更改数据类型及其值。您应该配置适当的数据类型及其值，以避免任何数据截断。

Once you click OK, it shows the destination SQL table.

单击“确定”后，它将显示目标SQL表。

Now, click on Mappings. Here, we can define and verify the source XML file and destination SQL table column mappings. It is a mandatory step to validate the column mappings. We cannot proceed with the SSIS package without validating the mappings.

现在，单击映射。在这里，我们可以定义和验证源XML文件和目标SQL表列映射。这是验证列映射的必需步骤。如果没有验证映射，我们将无法继续使用SSIS软件包。

Click OK after column mapping is done, it configures the OLE DB destination task. We can see below the configured SSIS package to import an XML file, as shown below.

列映射完成后，单击“确定”，它将配置OLE DB目标任务。我们可以在下面看到配置好的SSIS包以导入XML文件，如下所示。

Let’s execute this package, and it successfully transferred three rows from the XML Source to the SQL table.

让我们执行这个包，它成功地将三行从XML Source传输到SQL表。

Connect to the SQL instance in the SSMS and verify the data in the SQL table.

连接到SSMS中SQL实例，并验证SQL表中的数据。

SSIS XML源高级编辑器 (SSIS Advanced Editor for XML Source)

We can go to the Advanced Editor of XML source to configure various properties of source data. Right-click on the XML source -> Show Advanced Editor.

我们可以转到XML源的高级编辑器来配置源数据的各种属性。右键单击XML源->显示高级编辑器。

It opens the following Advanced Editor for XML source.

它将打开以下XML高级编辑器源。

Navigate to Input and Output properties. It gives you column properties for both source and output data for individual columns.

导航到“输入和输出”属性。它为您提供了各个列的源数据和输出数据的列属性。

Sometimes, we received error due to invalid data types. In this case, we can go to the respective column and modify the data type from a drop-down list.

有时，由于无效的数据类型，我们收到了错误消息。在这种情况下，我们可以转到相应的列并从下拉列表中修改数据类型。

结论 (Conclusion)

We can quickly load data from an XML file into the SQL Server tables using SSIS packages. You should have a proper XML document and its definition in an XSD format file. You can generate an XSD file from SSIS, but it does not work correctly for complex XML data. You should also configure the error handling in SSIS packages to avoid package failures and investigate it in case of any issues quickly.

我们可以使用SSIS包将数据从XML文件快速加载到SQL Server表中。您应该以XSD格式的文件获得正确的XML文档及其定义。您可以从SSIS生成XSD文件，但不适用于复杂的XML数据。您还应该在SSIS软件包中配置错误处理，以避免软件包失败，并在出现任何问题时Swift进行调查。

翻译自: https://www.sqlshack.com/import-xml-documents-into-sql-server-tables-using-ssis-packages/