4.6.3 生成Excel支持的XML表格数据

4.6.3 生成Excel支持的XML表格数据

Excel支持一种名为“XML表格”的存储格式，这种存储格式以xml的形式存储数据和格式，即有利于数据交换，也有利于数据的显示和查看。

下面通过一个示例来演示如何把数据库中的表变成“XML表格”。在进行这个处理之前，首先要根据处理需求生成一个模板数据，最后在这个模板数据中添加数据表中的实际数据。

1．生成模板数据

在以下的示例中，准备将系统目录视图sys.tables中的name和type两个列输出到Excel文件中。故首先需要生成一个包含name和type列的Excel表格，并根据自己的喜好设置好显示格式。设置完成后，单击【文件】Ú【另存为】命令，打开【另存为】对话框，在【保存类型】中选择“XML表格(*.xml)”，将设置好的模板保存为“XML表格”，如图4-1所示。

图4-1 设置Excel模板

通过“记事本”或者XML编辑工具（如可以用Manger Studio打开）打开这个文件查看其内容，这个文件的内容也是本次示例所的模板数据。

2．分析模板数据

在步骤3和T-SQL脚本中，变量@template的初始值即为步骤1所生成的模板数据（XML实例）。在这个XML实例中，需要关注的是/Workbook/Worksheet/Table路径下的内容（以粗体字表示的部分），此路径下的内容需要用数据表中的实际数据替换。而从这个结构来看，用FOR XML的PATH模式就可以轻松实现。

解决了数据生成的问题后，剩下的问题就是如何把根据表数据生成的XML实例插入到模板指定的位置中，虽然可以通过sql:variable函数获取XML实例外的变量中的数据，但由于这个函数不支持xml类型的变量，所以无法通过直接的方法将生成的XML实例插入到模板中。折衷的方法依然是通过FOR XML的PATH模式重构整个XML模板数据，在重构中将“根据表数据生成的XML实例”放到指定的位置中。

3．根据模板数据生成XML表格

根据第1步准备的模板数据，结合第2步的分析，对于本示例的需求，其最终的T-SQL脚本如下。其中赋予@template变量的XML实例是第1步生成的文件的内容。

DECLARE @template xml
SET @template = N'<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>zj</Author>
<LastAuthor>zj</LastAuthor>
<Created>2007-03-19T08:00:14Z</Created>
<Company>zjcxc</Company>
<Version>11.8107</Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>13590</WindowHeight>
<WindowWidth>19200</WindowWidth>
<WindowTopX>0</WindowTopX>
<WindowTopY>285</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Center"/>
<Borders/>
<Font ss:FontName="宋体" x:CharSet="134" ss:Size="12"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s21">
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="宋体" x:CharSet="134" ss:Size="12" ss:Color="#FF0000"/>
<Interior ss:Color="#99CCFF" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s22">
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
<Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="1" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25" ss:FullColumns="1" ss:FullRows="1">
<Column ss:Width="69.75"/>
<Column ss:Width="143.25"/>
<Row ss:AutoFitHeight="0">
<Cell ss:StyleID="s21">
<Data ss:Type="String">name</Data>
</Cell>
<Cell ss:StyleID="s21">
<Data ss:Type="String">type</Data>
</Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Unsynced/>
<Selected/>
<DoNotDisplayGridlines/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>1</ActiveRow>
<ActiveCol>1</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>'

-- 检索数据行数
DECLARE
@ExpandedRowCount int
SELECT
@ExpandedRowCount = COUNT(*)
FROM sys.tables

-- 查询生成的xml结果
DECLARE @re xml
;WITH
XMLNAMESPACES(
DEFAULT 'urn:schemas-microsoft-com:office:spreadsheet',
'urn:schemas-microsoft-com:office:office' as o,
'urn:schemas-microsoft-com:office:excel' as x,
'urn:schemas-microsoft-com:office:spreadsheet' as ss,
'http://www.w3.org/TR/REC-html40' as html
)
SELECT @re = (
SELECT
[*] = @template.query('/Workbook/*[local-name()!="Worksheet"]'),
[Worksheet/@ss:Name] = 'Sheet1',
[Worksheet/Table/@ss:ExpandedColumnCount] = 2,
[Worksheet/Table/@ss:ExpandedRowCount] = @ExpandedRowCount + 100,
[Worksheet/Table/@ss:FullColumns] = 1,
[Worksheet/Table/@ss:FullRows] = 1,
[Worksheet/Table/@ss:DefaultColumnWidth] = 54,
[Worksheet/Table/@ss:DefaultRowHeight] = 14.25,
[Worksheet/Table] = @template.query('
/Workbook/Worksheet/Table/Row[1]'),
[Worksheet/Table] = (
SELECT
[@ss:AutoFitHeight] = 0,
[Cell/@ss:StyleID] = 's22',
[Cell/Data/@ss:Type] = 'String',
[Cell/Data] = name,
NULL,
[Cell/@ss:StyleID] = 's22',
[Cell/Data/@ss:Type] = 'String',
[Cell/Data] = type
FROM sys.tables
FOR XML PATH('Row'), TYPE, ROOT('Table')
).query('/Table/*'),
[Worksheet] = @template.query('
/Workbook/Worksheet/*[local-name()="WorksheetOptions"]')
FOR XML PATH('Workbook')
)
-- 添加指令
SET @re.modify('
insert <?mso-application progid="Excel.Sheet"?>
as first into /')

-- 需要注意的是:由于SQL Server 的xml 类型不接受以'xml' 开头的XML 声明和处理指令
-- 故在写入这个XML结果到文件的时候, 应该手工在文件的最前面加上一行, 内容如下:
-- <?xml version="1.0"?>
-- 如果不加上此行, 则不是Excel 支持的标准的"xml 表格" 文件

-- 显示结果
SELECT @re
使用上述示例时，需要特别注意的一个问题是指令的问题，由于SQL Server的xml类型不接受以“xml”开头的XML声明和处理指令，故无法将“<?xml version="1.0"?>”加入到最终的XML文档中，这会导致文件无法正确显示。如果要解决此问题，可以在写入数据到文件时，手工在文件的最前面加入“<?xml version="1.0"?>”。或者是将结果以nvarchar(max)返回，并在返回数据的最前面加上“<?xml version="1.0"?>”。

另外，由于本示例最主要的目的还是演示XML的应用，故未考虑这种处理的通用性，感兴趣的读者可以自行改进这个处理。

理解本小节示例，对于综合应用XQuery和FOR XML查询和一定的好处。例如，使用XML DML的insert方法，无法在xml数据中插入来自xml变量的数据，但有时这是需要的。综合使用XQuery和FOR XML查询可以做到这一点。下面的示例演示这样一种处理，在表的xml列中存储有类似下面的xml数据；由于业务处理的需要，需要随时在此列中增加xml数据。

<rows>
<row>aa</row>
</rows>

对于这个需求，使用XML DML的insert方法当然无法实现，因为无法在XML DML的insert方法中引用xml类型的变量。要处理这个问题，可以先分解列中的xml数据，然后使用FOR XML将xml数据重新组合起来。下面的示例演示如何实现这种处理。

-- 演示表, 此表的col 列为xml 类型
DECLARE @tb TABLE(
id int PRIMARY KEY,
col xml
)

-- 增加演示数据
INSERT @tb(
id, col)
VALUES(1, '
aa
')

-- 更新操作
DECLARE 
@id int,
@xmldata xml

-- 要更新的记录的ID及需要增加的xml数据
SELECT 
@id = 1, 
@xmldata = '
new data1
new data
'

-- 更新操作
UPDATE A SET
col = B.col
FROM @tb A
OUTER APPLY(
SELECT col = (
SELECT
T.c.query('row'),
@xmldata.query('/rows/row')
FROM A.col.nodes('/rows')T(c)
FOR XML PATH(''), ROOT('rows'), TYPE
)
)B

-- 显示处理结果
SELECT * FROM @tb

通过前面的很多应用，相信读者对于处理的理解应该不成问题，上述示例的执行后，col列中的xml数据结果如下。从结果可以看到，使用XQuery结合FOR XML的方法，可以很好地融合两个（或者更多）的xml数据。

<rows>
<row>aa</row>
<row id="1">new data1</row>
<row id="2">new data</row>
</rows>