开篇介绍
在 ETL 项目中处理 XML 大概有这么几种常见情况:
- 上游程序或者第三方返回程序传递给 BI 程序就是 XML 格式的文件,通常情况下这种文件格式双方约定好了就不会更改,那么我们需要在加载 XML 文件的数据之前验证一下这个 XML 格式的数据文件是否符合我们之前的定义。如果满足验证,我们就处理,不满足那么我们就不处理或者以错误输入为由记入到错误日志中。
- 上游的 XML 文件作为输入,我们在 BI 中不处理其中的数据,而是将此 XML 文件按照一定的格式转换成另外的一个 XML 文件输出,这样下游程序将能够容易的处理 BI ETL 的输出。
- 上游的 XML 文件输出了很多很多内容,但是我们可能只需要很少的一部分数据导入的数据库,并且在导入之前备份这一小部分数据,相当于从源 XML 文件中剥离出一部分数据单独备份。
实际上 XML Task 包含了很多功能,比如有对比 XML 文件异同,找差异;在 XML 中查询数据;合并 XML 文件等等。
先来大致解释一下下面这几个配置选项,Operation Type - XML Task 操作的方式:
- Diff - 用来比较两个 XML 文档,使用源 XML 文档作为基础文档,然后和第二个文档进行比较并检查出他们之间的异同,并记录不同的地方到一个文档中。
- Merge - 合并两个 XML 文档,使用源 XML 文档作为基础文档,将第二个文档合并到第一个基础文档中,并且能够指定 Merge 的地方。
- Patch -
- Validate - 通过 DTD (Document Type Definition) 和 XSD (XML Schema definition) 来验证和检查文档。
- XPath - 执行 XPath 查询和计算。
- XSLT - 执行 XML 文档的转换。
在 XML Task 中,像 Diff, Merge 和 Patch 都是需要两个参与者的,第一个参与者就是源 XML 文档,第二个参与者也是 XML 文档,但是它自身的内容将会取决于第一个参与者指定的参与类型。比如说, Diff 操作是用来对比两个文档的,因此第二个 XML 文档就是另外的一个。
XML Task 中可以使用变量或者文件连接管理器作为它的源,当然这个源中是包含了 XML 的数据在其中的。当然作为输出来说,输出的内容也是可以保存为变量或者保存为文件的。
那么我们今天先来介绍一下在 XML Task 中比较常用的三大功能 - Validation 文件验证,XSLT 样式表转换,XPATH 表达式查询!
使用 DTD (文档类型定义) 或 XSD(XML 架构定义) 验证 XML 文档
新建一个包,并拖放一个 XMLTask - XML_VALIDATION
简单来说,你的 Operation Type 选择的不同,那么下面的配置选项就会变得不同,这里我们选择的是 Validate。
来解释一下下面这些配置的作用 -
- Input
- Operation - Validate 验证文档
- SourceType - File Connection (Direct Input, Variable) 指向要验证的 XML 文件,需要创建链接管理器。Direct Input 是直接在 Source 出放入 XML 文件的内容,而 Variable 则是需要保存 XML 文件中的内容。
- Source - 文件连接管理名称
- Output - SaveOperationResult 是否保存操作结果
- OperationResult
- DestinationType - Variable (File Connection) 执行的结果要么就是成功 True,要们就是失败 False。结果要么写入变量中,要么就写入到指定的结果文件中,写入的内容就是 True/False。
- Destination - 将由 DestinationType 决定是选择变量还是文件。
- OvewriteDestination - 是否重写覆盖掉已存在的内容或者文件。
- Second Operand
- SecondOperandType - File Connection (Direct Input, Variable) 指向要 XSD 或者 DTD 文件,需要创建链接管理器。
- SecondOperand - XSD 或者 DTD 文件。
- Validation Options
- ValidationType - XSD 或者 DTD
- FailOnValidationFail - 验证失败的时候是让这个 Task 也失败还是继续往下执行。
这里的三个文件链接管理器和定义的变量如下所示 -
SALES_ORDER.xsd 文件内容是根据 SALES_ORDER_1.xml 文件来的,它是正确的 XSD 格式。
<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="SalesOrder">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="SalesOrderDetail">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="SalesOrderID" type="xs:unsignedInt" />
<xs:element minOccurs="0" name="SalesOrderDetailID" type="xs:unsignedInt" />
<xs:element minOccurs="0" name="OrderQty" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="ProductID" type="xs:unsignedShort" />
<xs:element minOccurs="0" name="UnitPrice" type="xs:decimal" />
<xs:element minOccurs="0" name="UnitPriceDiscount" type="xs:decimal" />
<xs:element minOccurs="0" name="LineTotal" type="xs:decimal" />
<xs:element minOccurs="0" name="rowguid" type="xs:string" />
<xs:element minOccurs="0" name="ModifiedDate" type="xs:dateTime" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
用这个 XSD 文档验证 SALES_ORDER_1.xml 的内容肯定是可以通过验证的。
但是 SALES_ORDER_2.xml 的格式很明显不符合 XSD 文档的中对 SALES ORDER XML 文件的定义。
至少少了两列内容,并且列顺序还不完全相同
<xs:element minOccurs="0" name="rowguid" type="xs:string" />
<xs:element minOccurs="0" name="ModifiedDate" type="xs:dateTime" />
因此对于 SalesOrder1.xml 验证是可以通过的,但是对于 SalesOrder2.xml 验证是失败的,我们把验证的结果保存到了 PV_VALIDATION_RESULT 变量中。
再添加一个 Script Task 用来展示一下 PV_VALIDATION_RESULT 的结果。
public void Main()
{
// TODO: Add your code here
MessageBox.Show(Dts.Variables["User::PV_VALIDATION_RESULT"].Value.ToString());
Dts.TaskResult = (int)ScriptResults.Success;
}
保存并执行,当 Input 的 Source 为 Sales_Order_1.xml 时,是可以通过验证的,这就意味着后面的程序可以去处理这个 XML 文件了。
当 Input 的 Source 改为 Sales_Order_2.xml 时,验证是不能通过的,这样后面的程序就不用处理这个 XML 文件,并且可以加写 Log 记录等。
使用 XML 样式表对 XML 文件进行格式转换
首先了解一下需求,就拿上面的 XML 文件 SALES_ORDER_1.xml 的内容来说,我们可以看一下它的 XML 结构与格式。
现在需求变了,我们需要对这种格式进行转换,转换成如下结构 -
<?xml version="1.0" encoding="utf-8"?>
<TSSalesOrder>
<TSSalesOrderDetail>
<OrderID DetailID="110562">71774</OrderID>
<ProductID UnitPrice="356.8980" UnitPriceDiscount="0.0000">836</ProductID>
<LineTotal>356.898000</LineTotal>
</TSSalesOrderDetail>
<TSSalesOrderDetail>
<OrderID DetailID="110563">71774</OrderID>
<ProductID UnitPrice="356.8980" UnitPriceDiscount="0.0000">822</ProductID>
</TSSalesOrderDetail>
<TSSalesOrderDetail>
<OrderID DetailID="110567">71776</OrderID>
<ProductID UnitPrice="63.9000" UnitPriceDiscount="0.0000">907</ProductID>
<LineTotal>63.900000</LineTotal>
</TSSalesOrderDetail>
可以看得出来这个结构变复杂了 (通常情况下,我们对 XML 文件格式进行转换一般都是从复杂转换成简单的,但是这里是为了顺延案例流程的需求,由简单到复杂变化了)。
那么这种转换还是用的非常多的,在这里需要使用到 XSL (Extensible Stylesheet Language 扩展样式表语言,简称 XML 样式表) 和 XSLT 就是样式表转换 Transformation,关于 XSLT 内容,请查看 XSLT 教程。
为了实现这中转换,先需要设计好 XSLT 样式表转换文件 SALES_ORDER_TRANSFORMATION.xlst -
注意到 TSSalesOrder 和 TSSalesOrderDetail 的变化 (PS: TS 表示我们的天善 >_>)
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" indent="yes"/>
<xsl:template match="/">
<TSSalesOrder>
<xsl:for-each select="SalesOrder/SalesOrderDetail">
<TSSalesOrderDetail>
<OrderID>
<xsl:attribute name="DetailID">
<xsl:value-of select="SalesOrderDetailID"/>
</xsl:attribute>
<xsl:value-of select="SalesOrderID"/>
</OrderID>
<ProductID>
<xsl:attribute name="UnitPrice">
<xsl:value-of select="UnitPrice"/>
</xsl:attribute>
<xsl:attribute name="UnitPriceDiscount">
<xsl:value-of select="UnitPriceDiscount"/>
</xsl:attribute>
<xsl:value-of select="ProductID"/>
</ProductID>
<LineTotal><xsl:value-of select="LineTotal"/></LineTotal>
</TSSalesOrderDetail>
</xsl:for-each>
</TSSalesOrder>
</xsl:template>
</xsl:stylesheet>
新建一个 XML Task,并按照如下图所示完成 OperationType = XSLT 的配置
保存并执行包,包运行的结果将会把 SALES_ORDER_1.xml 文件按照 SALES_ORDER_TRANSFORMATION.xlst 样式转换表转换之后输出到 TRANSFORMATION.xml 文件中。
<?xml version="1.0" encoding="utf-8"?>
<TSSalesOrder>
<TSSalesOrderDetail>
<OrderID DetailID="110562">71774</OrderID>
<ProductID UnitPrice="356.8980" UnitPriceDiscount="0.0000">836</ProductID>
<LineTotal>356.898000</LineTotal>
</TSSalesOrderDetail>
<TSSalesOrderDetail>
<OrderID DetailID="110563">71774</OrderID>
<ProductID UnitPrice="356.8980" UnitPriceDiscount="0.0000">822</ProductID>
</TSSalesOrderDetail>
<TSSalesOrderDetail>
<OrderID DetailID="110567">71776</OrderID>
<ProductID UnitPrice="63.9000" UnitPriceDiscount="0.0000">907</ProductID>
<LineTotal>63.900000</LineTotal>
</TSSalesOrderDetail>
<TSSalesOrderDetail>
<OrderID DetailID="110616">71780</OrderID>
<ProductID UnitPrice="218.4540" UnitPriceDiscount="0.0000">905</ProductID>
<LineTotal>873.816000</LineTotal>
</TSSalesOrderDetail>
<TSSalesOrderDetail>
<OrderID DetailID="110617">71780</OrderID>
<ProductID UnitPrice="461.6940" UnitPriceDiscount="0.0000">983</ProductID>
<LineTotal>923.388000</LineTotal>
</TSSalesOrderDetail>
使用 XPATH 抽取数据
XPATH 是一种表达式语言,可以通过 XPATH 查找 XML 文件中的内容,比如返回某一个节点,符合条件的节点集合,值等等。
那么比如说,就 SALES_ORDER_1.xml 中我需要查找所有 Line Total > 1000 的元素集合,按照 XPATH 语法查询可以这样来写:
SalesOrder/SalesOrderDetail[LineTotal > 1000]
- Input 仍然是 SALES_ORDER_1.xml 文件
- Output 输出是 XPATH_OUTPUT.xml 文件
- SecondOperand 是 XPATH 的表达式
- PutResultInOneNode 一般选中 True,否则查询出去的内容就是一个 Segment 而非一个 XML 格式的内容。
- XPathOperation 通常选 Node list ,查询出去的内容还是一个节点的集合形式。另外有两种,一种是 Evaluation,一种是 Value。
先展示 Node List 的结果,自动的在 XML 文件前面加了一个 ResultRootNode 节点,所有 LineTotal 都大于 1000。
如果选 Values,则这里的 PutResultInOneNode 应该选为 False。
所有的节点上的 Value 值单独抽取出来显示在文件中,这种方式用的很少很少,一般单独抽取某一列转换成文本文件的时候可以考虑使用这种方式。
Evaluation 就没有什么作用了,就是返回一个XPath 函数的名称。
以下是文件中的内容-
所以对于 XPATH 在 XML Task 中的使用,其实就取决于 XPATH 的语法有多熟练,越熟练那么利用 XML Task 解决的有关 XML 的问题就会越多。