微软BI 之SSIS 系列 - XML Task 之 Validate 文件验证,XSLT 样式转换,XPath 元素查找三大功能

浏览: 3845

开篇介绍

在 ETL 项目中处理 XML 大概有这么几种常见情况:

  1. 上游程序或者第三方返回程序传递给 BI 程序就是 XML 格式的文件,通常情况下这种文件格式双方约定好了就不会更改,那么我们需要在加载 XML 文件的数据之前验证一下这个 XML 格式的数据文件是否符合我们之前的定义。如果满足验证,我们就处理,不满足那么我们就不处理或者以错误输入为由记入到错误日志中。
  2. 上游的 XML 文件作为输入,我们在 BI 中不处理其中的数据,而是将此 XML 文件按照一定的格式转换成另外的一个 XML 文件输出,这样下游程序将能够容易的处理 BI ETL 的输出。
  3. 上游的 XML 文件输出了很多很多内容,但是我们可能只需要很少的一部分数据导入的数据库,并且在导入之前备份这一小部分数据,相当于从源 XML 文件中剥离出一部分数据单独备份。

实际上 XML Task 包含了很多功能,比如有对比 XML 文件异同,找差异;在 XML 中查询数据;合并 XML 文件等等。

先来大致解释一下下面这几个配置选项,Operation Type - XML Task 操作的方式:

  1. Diff - 用来比较两个 XML 文档,使用源 XML 文档作为基础文档,然后和第二个文档进行比较并检查出他们之间的异同,并记录不同的地方到一个文档中。
  2. Merge - 合并两个 XML 文档,使用源 XML 文档作为基础文档,将第二个文档合并到第一个基础文档中,并且能够指定 Merge 的地方。
  3. Patch - 
  4. Validate  - 通过 DTD (Document Type Definition) 和 XSD (XML Schema definition) 来验证和检查文档。
  5. XPath - 执行 XPath 查询和计算。
  6. 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。

来解释一下下面这些配置的作用 -

  1. Input 
    1. Operation - Validate 验证文档
    2. SourceType - File Connection (Direct Input, Variable) 指向要验证的 XML 文件,需要创建链接管理器。Direct Input 是直接在 Source 出放入 XML 文件的内容,而 Variable 则是需要保存 XML 文件中的内容。
    3. Source - 文件连接管理名称
  2. Output - SaveOperationResult 是否保存操作结果
  3. OperationResult
    1. DestinationType - Variable (File Connection) 执行的结果要么就是成功 True,要们就是失败 False。结果要么写入变量中,要么就写入到指定的结果文件中,写入的内容就是 True/False。
    2. Destination - 将由 DestinationType 决定是选择变量还是文件。
    3. OvewriteDestination - 是否重写覆盖掉已存在的内容或者文件。
  4. Second Operand
    1. SecondOperandType - File Connection (Direct Input, Variable) 指向要 XSD 或者 DTD 文件,需要创建链接管理器。
    2. SecondOperand - XSD 或者 DTD 文件。
  5. Validation Options 
    1. ValidationType - XSD 或者 DTD
    2. 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]

  1. Input 仍然是 SALES_ORDER_1.xml 文件
  2. Output 输出是 XPATH_OUTPUT.xml 文件
  3. SecondOperand 是 XPATH 的表达式
  4. PutResultInOneNode 一般选中 True,否则查询出去的内容就是一个 Segment 而非一个 XML 格式的内容。
  5. XPathOperation 通常选 Node list ,查询出去的内容还是一个节点的集合形式。另外有两种,一种是 Evaluation,一种是 Value。

先展示 Node List 的结果,自动的在 XML 文件前面加了一个 ResultRootNode 节点,所有 LineTotal 都大于 1000。

如果选 Values,则这里的 PutResultInOneNode 应该选为 False。

所有的节点上的 Value 值单独抽取出来显示在文件中,这种方式用的很少很少,一般单独抽取某一列转换成文本文件的时候可以考虑使用这种方式。

Evaluation 就没有什么作用了,就是返回一个XPath 函数的名称。

以下是文件中的内容-

所以对于 XPATH 在 XML Task 中的使用,其实就取决于 XPATH 的语法有多熟练,越熟练那么利用 XML Task 解决的有关 XML 的问题就会越多。

更多有关 XML 相关的文章与资源

  1. XPath 教程
  2. XSLT 教程
  3. DTD 教程
  4. 微软BI 之SSIS 系列 - XML Task 中XSLT 样式表转换错误记录
推荐 0
本文由 BIWORK 创作,采用 知识共享署名-相同方式共享 3.0 中国大陆许可协议 进行许可。
转载、引用前需联系作者,并署名作者且注明文章出处。
本站文章版权归原作者及原出处所有 。内容为作者个人观点, 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台,并不用于任何商业目的,如果有任何问题,请及时联系我们,我们将根据著作权人的要求,立即更正或者删除有关内容。本站拥有对此声明的最终解释权。

0 个评论

要回复文章请先登录注册