Introduction
In this article, we will demonstrate how to extract two or more objects from an XML file and use them within the same data flow using the XML Parser Transform. Working with XML data in SSIS can be complex, especially when dealing with multiple nested objects. The ZappySys XML Parser Transform simplifies this process by allowing you to extract and manipulate multiple XML objects within a single data flow task, providing a streamlined and efficient approach to handling XML data in your ETL processes. This guide will walk you through the steps needed to set up the XML Parser Transform and ensure seamless integration of XML data into your SSIS packages.
Prerequisites
- SSIS PowerPack: Download and install the ZappySys SSIS PowerPack from the Customer Download Area or get the trial version.
Steps
-
Add a Data Flow task to your SSIS package.
-
Inside the Data Flow, add a Source component that contains XML data in a column. In our case, we will use our XML Source with Example 1 object from our component and add the Employee object; finally, check the option Output as RAW document.
-
Preview the data and copy the XML example from the column you will use. This will help in the following steps.
-
Connect a Multicast component from Microsoft to the XML Source.
-
Drag and drop the required XML Parser Transform components. We will use two for our objects: Employees and Books.
-
In the first XML Parser Transform, we will extract Employees. Select the column with the XML, copy and paste the example into the component, use the Select filter, choose the object, and preview the data to verify. Click OK to save the configuration.
-
In the next XML Parser Transform, repeat the process for the Books object. Preview the data and click OK to save the configuration.
-
Connect destination components to all the XML Parser Transform components.
-
Run the package and check the results.
Video Tutorial
Conclusion
By following these steps, you can effectively extract and use multiple XML objects from the same source component within a data flow, leveraging the ZappySys tool for efficient data retrieval and integration. For assistance or inquiries, contact our support team via chat on our website.
References
For more information, refer to the XML Parser Transform and XML Source documentation.