Example for using actions in XML パッケージ

Using the XML パッケージ, 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 アクション after the Start flow in the Bot エディター.
      Note: To add an アクション, search for the アクション in the Actions pane and drag it to the Bot flow in the Bot エディター.
    2. In Title, enter Open an Excel Sheet and save the アクション.
  3. To create and open an Excel sheet, use Excel advanced > Create workbook.
    1. Add the Excel advanced > Create workbook アクション within the Step アクション.
    2. In File path, enter C:\movies\movies.xlsx, and save the アクション. 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 アクション.
    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 アクション.
    2. In Title, enter Get XML Data and save the アクション.
  6. Start an XML session.
    1. Add the XML > Start session アクション 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 アクション.
    1. Add the XML > Get multiple nodes action after the Start session アクション.
    2. In XPath Expression, enter //movie[rating/text()='R'], and save the アクション.
      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 アクション retrieves.
    1. Add Loop > Loop after the XML: Get multiple nodes アクション.
    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 アクション.
  9. To get the movie name from the iterated node, use the Get single node アクション.
    1. Add the XML > Get single node action within the Loop: Loop アクション.
    2. In XPath expression, enter $XMLNode$/name.
    3. In Assign the output to variable, create a variable and name it sMovieName.
    4. Save the アクション.
  10. To write the movie name from the XMLNode variable, use the Set cell アクション.
    1. Add the Excel advanced > Set cell アクション after the Get single node アクション.
    2. Click Active cell, enter $sMovieName$ in Cell value, and save the アクション.
  11. To select a new cell within the Excel sheet, use the Go to cell アクション.
    1. Add the Go to cell アクション after the Set cell アクション after the Excel advanced: Set cell アクション.
    2. Click Active cell, select One cell to the right, and then save the アクション.
  12. To get the director name from the iterated node, use the Get single node アクション.
    1. Add the XML > Get single node アクション after the Excel advanced: Go to cell アクション.
    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 アクション.
  13. To write the director name from the XMLNode variable, use the Set cell アクション.
    1. Add the Excel advanced > Set cell action after the XML: Get single node アクション.
    2. Click Active cell, enter $sDirector$ in Cell value, and save the アクション.
  14. To select a new cell within the Excel sheet, use the Go to cell アクション.
    1. Add the Go to cell アクション after the Excel advanced: Set cell アクション.
    2. Click Active cell, select One cell to the right, and then save the アクション.
  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 アクション within the current Loop アクション.
    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 アクション. 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 アクション.
      Important: The second Loop アクション must be added within the main Loop アクション.
    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 アクション.
  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 アクション.
    1. Add the XML > Get single node アクション within the second Loop アクション.
    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 アクション.
  18. To assign values from the writer nodes, use the String > Assign アクション.
    1. Add String > Assign アクション 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 アクション.
    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 アクション after the inner Loop action within the first Loop アクション.
    2. Click Active cell.
    3. Enter $sWriters$ in Cell value.
    4. Save the アクション.
    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 アクション.
    1. Add the Go to cell アクション after the Set cell アクション.
    2. Click Active cell, select Beginning of the row, and then save the アクション.
    3. Add the Go to cell アクション again. Click Active cell, select One cell below, and then save the アクション.
  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 アクション.
  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 アクション.
    1. Add Excel advanced > Close within the Close the file step.
    2. Save the changes.
  24. Run the Bot.