Import Xml nodes as Xml column with SSIS

Question

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.

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.

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>

Answer 1

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

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

Answer 2

I didn't think that SSIS 2005 supported the XML datatype at all. I suppose it "supports" it as 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.