XML Result Set

In this section, we'll demonstrate how to set an object variable to hold the SQL result in XML format and how to set parameters in SQL statements. Let's continue to build the package.

  1. Open the package ExecuteSQLTask.dtsx in the project LearnSSIS1 if it is closed.

  2. Disable Contain 3 and enable Container 4 in the package.

  3. Create 3 variables as below.

  4. VariableData TypeValueUsage
    XMLResultString Hold the execution result
    p1Int321Parameter 1
    p2Int322Parameter 2

  5. Right click "XML Result" task and choose "Edit..." to open the task editor and make the settings as follows.
  6. Execute SQL Task Editor XML Output

    The ResultSet field is set as "XML" and the SQLStatement was set as below.


    SELECT name, age FROM dbo.student
    WHERE id = ? or id = ?
    FOR XML AUTO
    

    The question marks means we will transfer 2 parameters before the SQL runs. "FOR XML AUTO" means we'll output the result in XML format.


  7. Click Parameter Mapping tab and set the parameters as follows.
  8. Parameter Mapping Setting


  9. Click Result Set tab and then click Add button. Then change the NewResultName to 0 and set the Variable Name to User::XMLResult. At last click OK to finish the task setting.
  10. Result Setting For XML


  11. Edit "Script Task" in Container 4 and add "User::XMLResult" in the ReadOnlyVariables and add the following code in the Script to display the result of the variable.

  12. MessageBox.Show(Dts.Variables["User::XMLResult"].Value.ToString());
    

    For details to make these settings, you can check the Script Task.


  13. Run the package and you will get the result in XML format.
  14. XML Result Set Running Result

In the above example, we used parameters to transfer data to the SQL statement. Whenever the value of a parameter is changed, the whole result will be changed as well. This makes the whole package work dynamically. But the settings of parameters varies by the different connection types. check the list below to see the difference.

Connection TypeSelect SQLParameter name
EXCEL and OLE DBSELECT * FROM student WHERE id = ? OR id = ?0, 1, 2, 3, ...
ODBCSELECT * FROM student WHERE id = ? OR id = ?1, 2, 3, ...
ADOSELECT * FROM student WHERE id = ? OR id = ?Param1, Param2, ...
ADO.NETSELECT * FROM student WHERE id = @param1 OR id = @param2@Param1, @Param2, ...