SQL 将XML导入到SQL Server

在本文中，我们将介绍如何将XML数据导入到SQL Server中。XML是一种常见的数据格式，它可以轻松地在不同的系统之间共享数据。使用SQL Server，我们可以将XML数据导入到数据库中，并进行进一步的分析和处理。

阅读更多：SQL 教程

XML数据的基本结构

在开始导入XML数据之前，让我们先了解一下XML数据的基本结构。XML由标签、元素和属性组成。标签用于定义元素，元素是XML数据的基本单元，属性则提供了关于元素的额外信息。

下面是一个简单的XML示例：

<bookstore>
  <book category="cooking">
    <title lang="en">Italian Cooking</title>
    <author>Marco Polo</author>
    <year>2010</year>
    <price>29.99</price>
  </book>
  <book category="history">
    <title lang="en">World History</title>
    <author>Christopher Columbus</author>
    <year>2005</year>
    <price>19.99</price>
  </book>
</bookstore>

在上面的示例中，<bookstore>是根元素，<book>是子元素。每个<book>元素都有一个category属性，以及title、author、year和price等子元素。

使用OPENXML导入XML数据

在SQL Server中，我们可以使用OPENXML函数将XML数据直接导入到数据库表中。

以下是一个使用OPENXML函数导入XML数据的示例：

DECLARE @xml xml

SET @xml = '
<bookstore>
  <book category="cooking">
    <title lang="en">Italian Cooking</title>
    <author>Marco Polo</author>
    <year>2010</year>
    <price>29.99</price>
  </book>
  <book category="history">
    <title lang="en">World History</title>
    <author>Christopher Columbus</author>
    <year>2005</year>
    <price>19.99</price>
  </book>
</bookstore>'

DECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, @xml

INSERT INTO Books (Category, Title, Author, Year, Price)
SELECT Category, Title, Author, Year, Price
FROM OPENXML(@idoc, '/bookstore/book', 2)
WITH (
    Category varchar(50),
    Title varchar(100),
    Author varchar(100),
    Year int,
    Price decimal(10,2)
)

EXEC sp_xml_removedocument @idoc

在上面的示例中，我们首先声明了一个XML变量@xml，并将要导入的XML数据赋给它。然后，我们使用sp_xml_preparedocument存储过程将XML文档准备为可查询的格式，并将结果保存在@idoc变量中。

接下来，我们使用OPENXML函数将XML数据导入到Books表中。在OPENXML函数中，我们指定了要导入的节点路径（/bookstore/book），以及要映射到目标表的列的数据类型。最后，我们使用sp_xml_removedocument存储过程删除已准备的XML文档。

使用XML数据类型导入XML数据

除了使用OPENXML函数外，我们还可以使用XML数据类型将XML数据导入到SQL Server中。

以下是一个使用XML数据类型导入XML数据的示例：

DECLARE @xml xml

SET @xml = '
<bookstore>
  <book category="cooking">
    <title lang="en">Italian Cooking</title>
    <author>Marco Polo</author>
    <year>2010</year>
    <price>29.99</price>
  </book>
  <book category="history">
    <title lang="en">World History</title>
    <author>Christopher Columbus</author>
    <year>2005</year>
    <price>19.99</price>
  </book>
</bookstore>'

INSERT INTO Books (Category, Title, Author, Year, Price)
SELECT
    BookData.Category.value('@category', 'varchar(50)') AS Category,
    BookData.value('(title/text())[1]', 'varchar(100)') AS Title,
    BookData.value('(author/text())[1]', 'varchar(100)') AS Author,
    BookData.value('(year/text())[1]', 'int') AS Year,
    BookData.value('(price/text())[1]', 'decimal(10,2)') AS Price
FROM @xml.nodes('/bookstore/book') AS Books(BookData)

在上面的示例中，我们声明了一个XML变量@xml，并将要导入的XML数据赋给它。

然后，我们使用XML数据类型的value方法从XML对象中提取所需的数据，并将其插入到Books表中的相应列中。在value方法中，我们使用XQuery表达式指定要提取的数据。注意，XQuery表达式用于定位XML节点，text()函数用于获取节点的文本值。