使用 SSIS 将 Xml 节点导入为 Xml 列 [英] Import Xml nodes as Xml column with SSIS

问题描述

我正在尝试使用 Xml 源来分解 XML 源文件，但是我不希望将整个文档分解成表格.相反，我想将 xml 节点导入到 Xml 行中.

I'm trying to use the Xml Source to shred an XML source file however I do not want the entire document shredded into tables. Rather I want to import the xml Nodes into rows of Xml.

一个简化的例子是将下面的文档导入到一个名为people"的表中，其中一个名为person"的列是xml"类型.在查看 XmlSource 时 --- 它似乎适合将源 xml 粉碎成多个记录 --- 不是我正在寻找的.

a simplified example would be to import the document below into a table called "people" with a column called "person" of type "xml". When looking at the XmlSource --- it seem that it suited to shredding the source xml, into multiple records --- not quite what I'm looking for.

有什么建议吗?

<people>
    <person>
        <name>
            <first>Fred</first>
            <last>Flintstone</last>
        </name>
        <address>
            <line1>123 Bedrock Way</line>
            <city>Drumheller</city>
        </address>
    </person>
    <person>
        <!-- more of the same -->
    </person>
</people>

推荐答案

我认为 SSIS 2005 根本不支持 XML 数据类型.我想它支持"它作为 DT_NTEXT.

I didn't think that SSIS 2005 supported the XML datatype at all. I suppose it "supports" it as DT_NTEXT.

无论如何，您不能将 XML 源用于此目的.你必须自己写.这实际上并不像听起来那么难.它基于联机丛书中的示例.处理过程包括移动到第一个子节点，然后调用 XmlReader.ReadSubTree 以在下一个 <上返回一个新的 XmlReader/code> 元素.然后使用您最喜欢的 XML API 读取整个 <person/>，将生成的 XML 转换为字符串，并将其沿管道向下传递.对所有 节点重复.

In any case, you can't use the XML Source for this purpose. You would have to write your own. That's not actually as hard as it sounds. Base it on the examples in Books Online. The processing would consist of moving to the first child node, then calling XmlReader.ReadSubTree to return a new XmlReader over just the next <person/> element. Then use your favorite XML API to read the entire <person/>, convert the resulting XML to a string, and pass it along down the pipeline. Repeat for all <person/> nodes.

这篇关于使用 SSIS 将 Xml 节点导入为 Xml 列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！