使用SSIS将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.

我正在尝试使用Xml源来粉碎XML源文件,但是我不希望整个文档被粉碎成表格。相反,我想将xml节点导入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”,类型为“xml”。在查看XmlSource时 - 它似乎适合将源xml粉碎成多个记录 - 不是我正在寻找的。

Any suggestions?

<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>

2 个解决方案

#1

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

我认为SSIS 2005根本不支持XML数据类型。我认为它“支持”它为DT_NTEXT。

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.

在任何情况下,您都不能将XML Source用于此目的。你必须自己写。这实际上并不像听起来那么难。基于联机丛书中的示例。处理将包括移动到第一个子节点,然后调用XmlReader.ReadSubTree以在下一个元素上返回一个新的XmlReader。然后使用您喜欢的XML API读取整个 ,将生成的XML转换为字符串,并将其传递给管道。对所有节点重复此操作。

#2

Could you perhaps change your xml output so that the content of person is seen as a string? Use escape chars for the <>.

您是否可以更改您的xml输出,以便将person的内容视为字符串?使用转义字符表示<>。

You could use a script task to parse it as well, I'd imagine.

我想你也可以使用脚本任务来解析它。

#1