扁平化 XML 以通过 SSIS 包加载 [英] flattening XML to load via SSIS package

问题描述

我有一个来自 Lotus Notes DB 的 XML 输出.以下是 XML 结构:

I have an XML output from Lotus Notes DB. Following is the XML structure:

<Exec>
   <project id='C000253' type='Approved' >
      <DaysInOnHold>39</DaysInOnHold>
      <DaysInCompleted>0</DaysInCompleted>
      <ProjectComplexity type='1280'>Complex</ProjectComplexity>
      <ChangeRequest>
         <OnGoingAmt type='768'>-112</OnGoingAmt>
         <EAmt type='768'>123</EAmt>
      </ChangeRequest>
      <ChangeRequest>
         <ItemNbr type='768'>2</ItemNbr>
         <EAmt type='768'>321</EAmt>
      </ChangeRequest>
      <IncidentalItem>
         <ACost type='768'>0</ACost>
         <Billable type='1280'>Billable</Billable>
      </IncidentalItem>
      <IncidentalItem>
         <ACost type='768'>33</ACost>
         <Billable type='1280'>Billable</Billable>
      </IncidentalItem>
      <MaterialItem>
         <AQty type='768'>1</AQty>
         <ItemNbr type='768'>4</ItemNbr>
      </MaterialItem>
      <MaterialItem>
         <AQty type='768'>0</AQty>
         <ItemNbr type='768'>5</ItemNbr>
      </MaterialItem>
   </project>
   <project id='C110011' type='Not Approved'>
     ...
      ...
   </project>
</Exec>

当我在 SSIS 包中加载 XML 源代码时，它会为每个列名创建一个表，这当然不是很理想.

When I load the XML source in my SSIS package, it creates a table for every column name, which of-course is not very desirable.

我想以这样的方式转换它，使我能够拥有 Project 表、MaterialItem 和 ChangeRequest 表(随后将关联回单个项目条目.

I would like to transform this in such a way that it gives me the ability to have a Project table, MaterialItem and ChangeRequest tables (which would subsequently be related back to the individual project entry.

XSLT 会是一个答案吗?如果是这样，任何帮助将不胜感激.

Would XSLT be an answer? If so, any help would be appreciated.

我想我会添加所需的输出(似乎所有类型属性都导致了问题).所以似乎我需要一个可以摆脱所有属性的转换 - 特别是 type="...."

I thought I'd add in what the desired output should be (seems that all the type attributes are causing the issues). So seems that i need a transform that would get rid of all the attributes - specifically the type="...."

<Exec>
   <project id='C0001111'>
      <DaysInOnHold>33</DaysInOnHold>
      <DaysInCompleted>0</DaysInCompleted>
      <ProjectComplexity>Complex</ProjectComplexity>
      <ChangeRequest>
         <OnGoingAmt>52</OnGoingAmt>
         <EAmt>123</EAmt>
      </ChangeRequest>
      <ChangeRequest>
         <ItemNbr>2</ItemNbr>
         <EAmt>321</EAmt>
      </ChangeRequest>
      <IncidentalItem>
         <ACost>0</ACost>
         <Billable>Not Billable</Billable>
      </IncidentalItem>
      <IncidentalItem>
         <ACost>33</ACost>
         <Billable>Billable</Billable>
         </IncidentalItem>
      <MaterialItem>
         <AQty>1</AQty>
         <ItemNbr>4</ItemNbr>
      </MaterialItem>
      <MaterialItem>
         <AQty>0</AQty>
         <ItemNbr>5</ItemNbr>
      </MaterialItem>
   </project>
</Exec>

推荐答案

这对于 XSLT 来说很简单.首先，您需要阅读 XSLT 身份转换，它自己会复制所有节点和 XSLT 中的属性原样.

This is straight-forward with XSLT. Firstly, you need to read up on the XSLT identity transform which on its own will copy all the nodes and attributes in your XSLT as is.

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:template match="@*|node()">
      <xsl:copy>
        <xsl:apply-templates select="@*|node()"/>
      </xsl:copy>
   </xsl:template>
</xsl:stylesheet>

如果您只想删除 type 属性，但保留其他属性不变，只需将以下模板添加到上述 XSLT 中即可.这将匹配 type 属性，但不输出任何内容(即它们将被忽略)

If you want to remove just type attributes, but leave other attributes intact, just add the following template to the above XSLT. This will match type attributes, but output nothing (i.e. they will be ignored)

<xsl:template match="@type" />

如果您想删除所有属性，只需修改身份转换模板以删除属性匹配.例如:

If you want to remove ALL attributes, simply amend the indentity transform template to remove the match on attributes. For example:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:template match="node()">
    <xsl:copy>
      <xsl:apply-templates select="node()"/>
    </xsl:copy>
  </xsl:template>
</xsl:stylesheet>

这篇关于扁平化 XML 以通过 SSIS 包加载的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！