MSSQL方式快速生成XML文件

介绍

在许多商业应用程序中，XML作为互操作性的选择，已经成为一种普遍使用的格式。Microsoft SQL Server提供了一种生成XML文件的快速方法。MSSQL的XML功能允许你将基于关系的数据集合转成XML。

使用XML PATH语法生成XML

XML PATH命令实现了基于表的生成XML负载。将其用于GROUP BY从句中的列（用于对生成列进行分组），并将结果与STUFF函数结合使用。

1. 创建表并插入数据

以一个简单的客户表为例。

CREATE TABLE Customers
(
    id int,
    name varchar(50),
    country varchar(50),
    age int
)
 
INSERT INTO Customers VALUES(1,'Bob','USA',30);
INSERT INTO Customers VALUES(2,'Alice','France',25);
INSERT INTO Customers VALUES(3,'John','Spain',21);
INSERT INTO Customers VALUES(4,'Lisa','Germany',26);

2. 生成XML

以下SQL代码将返回一个XML负载，其结构是在国家层和顾客层进行嵌套，并将所有顾客列列出在国家层：

SELECT 
    country AS '@Country', 
    (SELECT 
        id as '@Id',
        name AS 'Name',
        age AS 'Age'
    FROM Customers 
    WHERE Customers.country = CustomersOuter.country
    FOR XML PATH('Customer'), TYPE)
FROM Customers CustomersOuter
GROUP BY country
FOR XML PATH('Country'), ROOT('Countries')

运行该查询将返回以下结果：

<Countries>
   <Country Country="France">
      <Customer Id="2">
         <Name>Alice</Name>
         <Age>25</Age>
      </Customer>
   </Country>
   <Country Country="Germany">
      <Customer Id="4">
         <Name>Lisa</Name>
         <Age>26</Age>
      </Customer>
   </Country>
   <Country Country="Spain">
      <Customer Id="3">
         <Name>John</Name>
         <Age>21</Age>
      </Customer>
   </Country>
   <Country Country="USA">
      <Customer Id="1">
         <Name>Bob</Name>
         <Age>30</Age>
      </Customer>
   </Country>
</Countries>

3. 添加CDATA

如果需要将文本数据插入XML中，请使用CDATA部分。CDATA保持文本内容原样不被编码。

下面的代码将将“Jim's Bar”文本值保留原样并插入XML负载：

SELECT 
    country AS '@Country',
    (SELECT 
        id as '@Id',
        name AS 'Name',
        age AS 'Age',
        '' AS 'Notes'
    FROM Customers 
    WHERE Customers.country = CustomersOuter.country
    FOR XML PATH('Customer'), TYPE)
FROM Customers CustomersOuter
GROUP BY country
FOR XML PATH('Country'), ROOT('Countries')

结论

MSSQL提供了一种快速生成XML文件的方法，这在许多商用应用程序中是非常有用的。使用XML PATH语法生成XML非常方便，并且MSSQL还支持CDADA标记以处理文本值。