XML Patterns - SQL Server Integration Services Design Patterns - page 257

CHAPTER 9
XML Patterns
XML is a popular format for exchanging data between systems. SSIS provides an XML
Source adapter, but because of the flexible nature of XML, it can sometimes be tricky to
get your data to fit into the tabular format that the SSIS data flow expects. This chapter
describes the formats that work best with the XML Source and two alternative patterns
for reading XML data with SSIS.
Using the XML Source
Like most Data Flow components, the XML Source component requires column
metadata to be set at design time. This is done using an XML schema file ( .xsd ). The
XML Source component uses the XML structure defined in the schema to create one or
more outputs, and it also uses the element and attribute data types to set the column
metadata. Changing the schema file will refresh the component's metadata and may
cause validation errors if you have already mapped some of its outputs.
If you don't already have an XML schema defined for your document, SSIS can
generate one for you. Click the Generate Schema button on the XML Source editor UI,
and the component will infer the schema from the current document. Note that although
this schema is guaranteed to work with the current XML file, it might not work for oth-
ers if optional elements or values are longer than expected. You may need to modify the
generated schema file by hand to ensure that the minOccurs and maxOccurs attrib-
ute values are correct for each element and that the data types were set correctly.
The XML Source is easiest to use when your input file has a simple element/subele-
ment structure. Listing 9-1 shows an example of that structure.