Example for using actions in XML package

Using the XML package, create a bot that reads and writes data from an XML file to an Excel sheet.

The sample XML used in this tutorial has the following structure:
<movies>
    <movie genre="" year="">
        <name></name>
        <description></description>
        <director></director>
        <writers>
            <writer type=""></writer>
            <writer type=""></writer>
        </writers>
        <rating>R</rating>
    </movie>
</movies>

Before you begin, download a sample XML file.

The tutorial is intended to demonstrate the following actions:
  • Use an XPath expression to get a node set with specific value and iterate through the items in the node set, for example, movies with specific rating in this tutorial.
  • Use an XPath function to iterate through the child node sets.
  • Concatenate variables to build a string.
  • Write data to an Excel sheet.

Prerequisites

Ensure the following:

  • You know how to create bots.
  • You are familiar with the Control Room user interface.

Procedure

  1. Create a bot and name it XMLDataReader.
  2. Add a step to group all tasks for opening an Excel sheet.
    1. Add the Step > Step action after the Start flow in the Bot editor.
      Note: To add an action, search for the action in the Actions pane and drag it to the bot flow in the Bot editor.
    2. In Title, enter Open an Excel Sheet and save the action.
  3. To create and open an Excel sheet, use Excel advanced > Create workbook.
    1. Add the Excel advanced > Create workbook action within the Step action.
    2. In File path, enter C:\movies\movies.xlsx, and save the action. If the movies folder does not exist in the specified path, create a movies folder in the path.
  4. To select the first cell on the Excel sheet, use the Excel advanced > Go to cell action.
    1. Add the Excel advanced > Go to cell action after the Excel advanced: Create workbook action.
    2. Click Specific cell if it is not already selected, and enter A1.
    3. Save the action.
  5. Create another step to group all the tasks for retrieving XML data.
    1. Add the Step > Step action after the first Step action.
    2. In Title, enter Get XML Data and save the action.
  6. Start an XML session.
    1. Add the XML > Start session action within the Get XML Data step.
    2. Under File, select Desktop, browse and select the sample XML file that you downloaded.
    3. Save the changes.
  7. To get all movie nodes with rating R, use the Get multiple nodes action.
    1. Add the XML > Get multiple nodes action after the Start session action.
    2. In XPath Expression, enter //movie[rating/text()='R'], and save the action.
      The selected node set is stored in a system variable and is available to the For each Node in a XML dataset iterator now.
    3. To ensure that the action retrieves the entire node set instead of just the text nodes, click Xpath expression.
  8. Use the Loop action to iterate through the selected node set that the Get multiple nodes action retrieves.
    1. Add Loop > Loop after the XML: Get multiple nodes action.
    2. Under Iterator, select XML > For each Node in a XML dataset as the iterator.
    3. In Assign the current row to this variable, click (x) and create a variable with the default name, for example, XMLNode. Each item in the node set is now available through the variable.
    4. Save the action.
  9. To get the movie name from the iterated node, use the Get single node action.
    1. Add the XML > Get single node action within the Loop: Loop action.
    2. In XPath expression, enter $XMLNode$/name.
    3. In Assign the output to variable, create a variable and name it sMovieName.
    4. Save the action.
  10. To write the movie name from the XMLNode variable, use the Set cell action.
    1. Add the Excel advanced > Set cell action after the Get single node action.
    2. Click Active cell, enter $sMovieName$ in Cell value, and save the action.
  11. To select a new cell within the Excel sheet, use the Go to cell action.
    1. Add the Go to cell action after the Set cell action after the Excel advanced: Set cell action.
    2. Click Active cell, select One cell to the right, and then save the action.
  12. To get the director name from the iterated node, use the Get single node action.
    1. Add the XML > Get single node action after the Excel advanced: Go to cell action.
    2. In XPath expression, enter $XMLNode$/director.
    3. In Assign the output to variable, create a variable with the following name: sDirector
    4. Save the action.
  13. To write the director name from the XMLNode variable, use the Set cell action.
    1. Add the Excel advanced > Set cell action after the XML: Get single node action.
    2. Click Active cell, enter $sDirector$ in Cell value, and save the action.
  14. To select a new cell within the Excel sheet, use the Go to cell action.
    1. Add the Go to cell action after the Excel advanced: Set cell action.
    2. Click Active cell, select One cell to the right, and then save the action.
  15. Because the <writers> node can contain multiple <writer> nodes, get each writer name, concatenate the names, and write them to a single cell instead of writing values to multiple cells. To do so, add the Loop action within the current Loop action.
    1. To get the number of the <writer> nodes within the <writers> node, add the XML > Execute XPath function after the Excel advanced: Set cell action. In XPath expression, enter count($XMLNode$/writers/writer). In Assign the output to variable, create a variable named sWriterCount.
      You will use the <writer> node count to loop through all the <writer> nodes.
    2. Add the Loop > Loop action after the XML: Execute XPath function action.
      Important: The second Loop action must be added within the main Loop action.
    3. Under Iterator, select For n times as the iterator.
    4. In Times, enter the following: $sWriterCount.String:toNumber$
      Note: sWriterCount contains a string value. To convert string to number, you can use the
      lt;variable name>.String:toNumber$ format, for example, $sWriterCount.String:toNumber$.
    5. In Assign the current value to variable (optional), create a variable named Counter.
    6. Save the action.
  16. To concatenate the values from all writer nodes, create a string variable.
    1. In the Variables pane, click the plus icon.
      The Create variable window opens.
    2. In Type, select String.
    3. In Name, enter sWriters.
    4. Click Create.
  17. To get the value from the iterated <writer> node, use the Get single node action.
    1. Add the XML > Get single node action within the second Loop action.
    2. In XPath expression, enter $XMLNode$/writers/writer[$Counter.Number:toString$].
      Using the Counter variable, you can access a node at a specific index. Note that because the Counter variable contains a numeric value, the .Number:toString$ expression is added to the Counter variable to convert it to a string.
    3. In Assign the output to variable, create a variable and name it sWriter.
    4. Save the action.
  18. To assign values from the writer nodes, use the String > Assign action.
    1. Add String > Assign action after the XML: Get single node within the second loop.
    2. In Select the source string variable(s)/ value (optional), enter $sWriters$, $sWriter$.
    3. In Select the destination string variable, select sWriters.
    4. Save the action.
    After the inner loop is complete, the sWriters variable will contain all the writer names.
  19. To write the writer names to a new cell, use the Excel advanced > Set cell action within the main loop.
    1. Add the Excel advanced > Set cell action after the inner Loop action within the first Loop action.
    2. Click Active cell.
    3. Enter $sWriters$ in Cell value.
    4. Save the action.
    The data retrieved from one <movie> node is written to a row. To write the data from the next <movie> node, you must select the next row in the Excel sheet.
  20. To move the cursor to the next row, use the Excel advanced > Go to cell action.
    1. Add the Go to cell action after the Set cell action.
    2. Click Active cell, select Beginning of the row, and then save the action.
    3. Add the Go to cell action again. Click Active cell, select One cell below, and then save the action.
  21. Before the next node in the retrieved <movie> node set is iterated, you must reset the sWriters variable. To reset the sWriters variable, perform the following steps:
    1. Add the String > Assign action after the Excel advanced: Go to cell within the main loop.
    2. Leave Select the source string variable(s)/ value (optional) blank.
    3. In Select the destination string variable, select sWriters.
    4. Save the action.
  22. Create a new step.
    1. Add the Step > Step action after the second Step action.
    2. In Title, enter Close the file.
  23. To close the Excel sheet, use Excel advanced Close action.
    1. Add Excel advanced > Close within the Close the file step.
    2. Save the changes.
  24. Run the bot.