The SSIS Foreach NodeList Enumerator is useful to enumerate the XML nodes. For example, you can traverse to any node in a XML file using this enumerator. In this article we will show you, How to use this SSIS Foreach NodeList Enumerator with example.
Before we start demonstrating the Foreach NodeList Enumerator in SSIS, Let me show you the data that we are going to use for this example. This is Employee Table present in SQL Server that we are going to use for this example:
Below query will convert the data in Employee table to XML file
and the data inside the XML file is:
SSIS Foreach NodeList Enumerator Example
In this example, we will enumerate the nodes in above XML file, and copy the Occupation column information into another database table. In order to do so, First Drag and drop the Foreach Loop Container into the Control Flow region as shown below
Double click on it will open the Foreach Loop Editor to configure it. Within the General tab, Please specify valid Name and Description. From the below screenshot you can see that, we changed the default name to SSIS Foreach NodeList Enumerator
Next go to the Collections tab to select the Enumerator. From the below screenshot you can see, we are selecting Foreach NodeList Enumerator because we want to loop over the XML nodes.
Following are the list of properties that you have to configure for this enumerator.
Document Source Type: This property has three options:
- File Connection: It means, source is in a XML file present in the file system. You can use the next property to select the file.
- Variables: Source is present in the variable. If you select this option then you have to select the variable name in the next option.
- Direct Input: We want to insert the XML data directly into this editor. For now, we are selecting this option.
As you can see, we inserted the above specified XML data
Enumerator Type: This property has four options:
- Navigator: This will enumerate using XpathNavigator
- Node: It will enumerate nodes.
- NodeText: This will enumerate text nodes. This is useful to narrow down your search.
- ElementCollection: This will enumerate element nodes returned by the Xpath.
Next we are providing the path. below path will navigate to Occupation node present in each Employ under the Employee details node.
Now select the existing variable and assign it to index 0 as we shown in below screenshot. Once you are done, Click OK to finish configuring the Foreach Loop Container.
Next, Drag and drop the Execute SQL Task from toolbox to Foreach loop container. This task will help us to insert the values into our Empty table.
Double click on it will open the Execute SQL Task Editor window. From the below screenshot you can observe that, we selected OLE DB as Connection type and selected the existing connection which is pointing to [SQL Tutorials] Database.
TIP: Please refer the OLE DB Connection manager article to understand the steps involved in creating new OLE DB Connection.
Next, we are writing the following SQL Query to insert the table name into SMO_Enumerator table.
INSERT INTO [NodeListEnumerator] ([Occupation])
VALUES (?)Here question mark will be replaced by the parameter.
Please cross check all the properties before you start parameter mapping
Next, go to parameter mapping and select the parameter that we assigned in Foreach Loop container. It means, Foreach loop container will loop through the XML nodes, and store the ocuupation information in the variable. Next we are passing that variable into Execute SQL task
Click OK to finish configuring the SSIS Foreach NodeList Enumerator package. Let us Run the package
Let us open the SQL Server management studio and check whether we inserted the Occupation details into the destination table using SSIS Foreach NodeList Enumerator or not
Thank You for Visiting Our Blog