在SQL Server中使用XML数据

XML (eXtensible Markup Language) is one of the most common formats used to share information between different platforms. Owing to its simplicity and readability, it has become the de-facto standard for data sharing. In addition, XML is easily extendable.

XML（可扩展标记语言）是用于在不同平台之间共享信息的最常见格式之一。 由于其简单性和可读性，它已成为数据共享的实际标准。 另外，XML易于扩展。

In this article, we will see how we can work with XML in SQL Server. We will see how to convert tables in SQL into XML, how to load XML documents into SQL Server and how to create SQL tables from XML documents.

在本文中，我们将看到如何在SQL Server中使用XML。 我们将看到如何将SQL中的表转换为XML，如何将XML文档加载到SQL Server中以及如何从XML文档创建SQL表。

Let’s first generate some dummy data. We will use this data to create XML documents. Execute the following script:

让我们首先生成一些虚拟数据。 我们将使用此数据来创建XML文档。 执行以下脚本：

CREATE DATABASE Showroom
 
Use Showroom
CREATE TABLE Car  
(  
  CarId int identity(1,1) primary key,  
  Name varchar(100),  
  Make varchar(100),  
  Model int ,  
  Price int ,  
  Type varchar(20)  
)  
    
insert into Car( Name, Make,  Model , Price, Type)
VALUES ('Corrolla','Toyota',2015, 20000,'Sedan'),
('Civic','Honda',2018, 25000,'Sedan'),
('Passo','Toyota',2012, 18000,'Hatchback'),
('Land Cruiser','Toyota',2017, 40000,'SUV'),
('Corrolla','Toyota',2011, 17000,'Sedan'),
('Vitz','Toyota',2014, 15000,'Hatchback'),
('Accord','Honda',2018, 28000,'Sedan'),
('7500','BMW',2015, 50000,'Sedan'),
('Parado','Toyota',2011, 25000,'SUV'),
('C200','Mercedez',2010, 26000,'Sedan'),
('Corrolla','Toyota',2014, 19000,'Sedan'),
('Civic','Honda',2015, 20000,'Sedan')

In the script above, we created a Showroom database with one table Car. The Car table has five attributes: CarId, Name, Make, Model, Price, and Type. Next, we added 12 dummy records in the Car table.

在上面的脚本中，我们使用一个表Car创建了一个Showroom数据库。 Car表具有五个属性：CarId，名称，品牌，型号，价格和类型。 接下来，我们在Car表中添加了12条虚拟记录。

从SQL表转换为XML (Converting into XML from SQL tables)

The simplest way to convert data from SQL tables into XML format is to use the FOR XML AUTO and FOR XML PATH clauses.

将数据从SQL表转换为XML格式的最简单方法是使用FOR XML AUTO和FOR XML PATH子句。

SQL SERVER中的FOR XML AUTO (FOR XML AUTO in SQL SERVER)

The FOR XML AUTO clause converts each column in the SQL table into an attribute in the corresponding XML document.

FOR XML AUTO子句将SQL表中的每一列转换为相应XML文档中的属性。

Execute the following script:

执行以下脚本：

USE Showroom
SELECT * FROM Car
FOR XML AUTO

In the console output you will see the following:

在控制台输出中，您将看到以下内容：

Click on the link and you will see the following document in a new query window of SQL Server management studio:

单击链接，您将在SQL Server Management Studio的新查询窗口中看到以下文档：

You can see that for each record an element Car has been created in the XML document, and for each column, an attribute with the same name has been added to each element in the XML document.

您可以看到，对于每个记录，都在XML文档中创建了一个元素Car，对于每个列，具有相同名称的属性已添加到XML文档中的每个元素。

SQL SERVER中的FOR XML PATH (FOR XML PATH in SQL SERVER)

The FOR XML AUTO class creates an XML document where each column is an attribute. On the other hand, the FOR XML PATH will create an XML document where each record is an element and each column is a nested element for a particular record. Let’s see this in action:

FOR XML AUTO类创建一个XML文档，其中每一列都是一个属性。 另一方面，FOR XML PATH将创建一个XML文档，其中每个记录是一个元素，每个列是一个特定记录的嵌套元素。 让我们来看看实际情况：

USE Showroom
SELECT * FROM Car
FOR XML PATH

A snapshot of the output is as follows:

输出快照如下：

In the output, you will see a total of 12 elements (the screenshot shows only the first 4). You can see that each column name has been converted to an element. However, there is one problem; by default, the parent element name is “row”. We can change that using the following query:

在输出中，您将看到总共12个元素（屏幕截图仅显示前4个元素）。 您可以看到每个列名称都已转换为元素。 但是，有一个问题。 默认情况下，父元素名称为“行”。 我们可以使用以下查询来更改它：

USE Showroom
SELECT * FROM Car
FOR XML PATH ('Car')

In the output, you can see Car as the parent element for each sub-element. However, the document is not well-formed as there is no root element in the document. To add a root element, we need to execute the following script:

在输出中，您可以将Car作为每个子元素的父元素。 但是，该文档的格式不正确，因为该文档中没有根元素。 要添加根元素，我们需要执行以下脚本：

USE Showroom
SELECT * FROM Car
FOR XML PATH ('Car'), ROOT('Cars')

In the output, you should see “Cars” as the root element as shown below:

在输出中，您应该看到“汽车”作为根元素，如下所示：

Now suppose you want that CarId should be the attribute of the Car element rather than an element. You can do so with the following script:

现在假设您希望CarId应该是Car元素的属性，而不是元素。 您可以使用以下脚本进行操作：

USE Showroom
SELECT  CarId as [@CarID],  
    Name  AS [CarInfo/Name],  
    Make [CarInfo/Make],  
    Model [CarInfo/Model],  
    Price,  
    Type
FROM Car 
FOR XML PATH ('Car'), ROOT('Cars')

The output looks like this:

输出看起来像这样：

You can see now that CarId has become an attribute of the Car element.

现在您可以看到CarId已成为Car元素的属性。

We can add further nesting levels to an XML document. For instance, if we want Name, Make and Model elements to be nested inside another element CarInfo we can do so with the following script:

我们可以向XML文档中添加更多的嵌套级别。 例如，如果我们希望将Name，Make和Model元素嵌套在另一个CarInfo元素内，则可以使用以下脚本进行操作：

USE Showroom
SELECT  CarId as [@CarID],  
    Name  AS [CarInfo/Name],  
    Make [CarInfo/Make],  
    Model [CarInfo/Model],  
    Price,  
    Type
FROM Car 
FOR XML PATH ('Car'), ROOT('Cars')

In the output, you will see a new element CarInfo that encloses the Name, Make and Model elements as shown below:

在输出中，您将看到一个新的CarInfo元素，其中包含Name，Make和Model元素，如下所示：

Finally, if you want to convert the elements Name and Make into an attribute of element CarInfo, you can do so with the following script:

最后，如果要将元素Name和Make转换为元素CarInfo的属性，则可以使用以下脚本进行操作：

USE Showroom
SELECT  CarId as [@CarID],  
    Name  AS [CarInfo/@Name],  
    Make [CarInfo/@Make],  
    Model [CarInfo/Model],  
    Price,  
    Type
FROM Car 
FOR XML PATH ('Car'), ROOT('Cars')

The output looks like this:

输出看起来像这样：

Save the above XML document with the name Cars.xml. In the next section, we will load this XML script into the SQL Server and will see how to create a table from the XML Document.

使用名称Cars.xml保存上述XML文档。 在下一节中，我们将把此XML脚本加载到SQL Server中，并了解如何从XML文档创建表。

For those interested in further articles on FOR XML PATH see FOR XML PATH clause in SQL Serverarticle.

对于那些对FOR XML PATH的更多文章感兴趣的人，请参阅SQL Server文章中的FOR XML PATH子句 。

从XML文档创建SQL表 (Creating a SQL table from an XML document)

In the previous section, we saw how to create an XML document from the SQL table. In this section, we will see how to do the reverse i.e. we will create a table in SQL using XML documents.

在上一节中，我们了解了如何从SQL表创建XML文档。 在本节中，我们将看到相反的操作，即，我们将使用XML文档在SQL中创建一个表。

The document we will use is the document that we created in the last section. One node of the document looks like this:

我们将使用的文档是我们在上一节中创建的文档。 文档的一个节点如下所示：

使用XML属性创建SQL表 (Creating SQL table using XML attributes)

Let’s first see how we can create an SQL table using attributes. Suppose we want to create a table with two columns that contain the values from the Name and Make attributes from the CarInfo element. We can do so using the following script:

首先让我们看看如何使用属性创建SQL表。 假设我们要创建一个包含两列的表，其中包含来自CarInfo元素的Name和Make属性的值。 我们可以使用以下脚本进行操作：

DECLARE @cars xml
 
SELECT @cars = C
FROM OPENROWSET (BULK 'D:\Cars.xml', SINGLE_BLOB) AS Cars(C)
    
SELECT @cars
    
DECLARE @hdoc int
    
EXEC sp_xml_preparedocument @hdoc OUTPUT, @cars
SELECT *
FROM OPENXML (@hdoc, '/Cars/Car/CarInfo' , 1)
WITH(
    Name VARCHAR(100),
    Make VARCHAR(100)
    )
    
    
EXEC sp_xml_removedocument @hdoc

In the script above we declare an XML type variable @cars. The variable stores the result returned by the OPENROWSET function which retrieves XML data in binary format. Next using the SELECT @Cars statement we print the contents of the XML file. At this point in time, the XML document is loaded into the memory.

在上面的脚本中，我们声明一个XML类型变量@cars。 该变量存储OPENROWSET函数返回的结果，该函数以二进制格式检索XML数据。 接下来，使用SELECT @Cars语句，我们打印XML文件的内容。 此时，XML文档已加载到内存中。

Next, we create a handle for the XML document. To read the attributes and elements of the XML document, we need to attach the handle with the XML document. The sp_xml_preparedocument performs this task. It takes the handle and the document variable as parameters and creates an association between them.

接下来，我们为XML文档创建一个句柄。 要读取XML文档的属性和元素，我们需要将句柄附加到XML文档中。 sp_xml_prepare文档执行此任务。 它以句柄和document变量作为参数，并在它们之间创建关联。

Next, we use the OPENXML function to read the contents of the XML document. The OPENXML function takes three parameters: the handle to the XML document, the path of the node for which we want to retrieve the attributes or elements and the mode. The mode value of 1 returns the attributes only. Next, inside the WITH clause, we need to define the name and type of the attributes that you want returned. In our case the CarInfo element has two attributes Name, and Make, therefore we retrieve both.

接下来，我们使用OPENXML函数读取XML文档的内容。 OPENXML函数采用三个参数：XML文档的句柄，我们要为其检索属性或元素的节点的路径以及模式。 模式值1仅返回属性。 接下来，在WITH子句中，我们需要定义要返回的属性的名称和类型。 在我们的例子中，CarInfo元素具有两个属性Name和Make，因此我们同时检索了这两个属性。

As a final step, we execute the sp_xml_removedocument stored procedure to remove the XML document from the memory. In the output you will see values from the Name and Make attributes of the CarInfo element as shown below:

最后一步，我们执行sp_xml_removedocument存储过程以从内存中删除XML文档。 在输出中，您将看到CarInfo元素的Name和Make属性的值，如下所示：

使用XML元素创建SQL表 (Creating a SQL table using XML elements)

To create a SQL table using XML elements, all you have to do is to change the mode value of the OPENXML function to 2 and change the name of the attributes to the name of the element you want to retrieve.

要使用XML元素创建SQL表，您要做的就是将OPENXML函数的mode值更改为2，并将属性名称更改为要检索的元素名称。

Suppose we want to retrieve the values for the nested CarInfo, Price and Type elements of the parent Car element, we can use the following script:

假设我们要检索父Car元素的嵌套CarInfo，Price和Type元素的值，我们可以使用以下脚本：

DECLARE @cars xml
 
SELECT @cars = C
FROM OPENROWSET (BULK 'D:\Cars.xml', SINGLE_BLOB) AS Cars(C)
    
SELECT @cars
    
DECLARE @hdoc int
    
EXEC sp_xml_preparedocument @hdoc OUTPUT, @cars
SELECT *
FROM OPENXML (@hdoc, '/Cars/Car' , 2)
WITH(
    CarInfo INT,
    Price INT,
    Type VARCHAR(100)
    )
    
    
EXEC sp_xml_removedocument @hdoc

Output of the script above looks like this:

上面脚本的输出如下所示：

结论 (Conclusion)

XML is one of the most popular data formats for information exchange. In this article, we saw how we can create a document using XML from a SQL table. We also saw how to import into a table in SQL from an XML document.

XML是最流行的信息交换数据格式之一。 在本文中，我们了解了如何使用SQL表中的XML创建文档。 我们还看到了如何从XML文档导入到SQL表中。

翻译自: https://www.sqlshack.com/working-with-xml-data-in-sql-server/