XML mapping in Excel 2013

Question

I have a spreadsheet where on each line I register a test case and then export it to XML, then upload this xml into another system we use.

The problem is that I can only do 1 to 1, I can’t fill several lines and then export everything, when I do this XML exports only the first line and nothing else or gives an error that I can’t handle.

Currently exports in this way:

    <testcases>
<testcase internalid="980" name="Caso de testes Importação">
   <node_order><![CDATA[100]]></node_order>
   <externalid><![CDATA[1]]></externalid>
   <version><![CDATA[1]]></version>
   <summary><![CDATA[<p>&nbsp;O caso de teste deve fazer isso   </p>]]></summary>
   <preconditions><![CDATA[<p>&nbsp;<a id="fck_paste_padding"></a>&nbsp;Pré Condições od caso de testes</p>]]></preconditions>
   <execution_type><![CDATA[1]]></execution_type>
   <importance><![CDATA[2]]></importance>
<steps>
<step>
   <step_number><![CDATA[1]]></step_number>
   <actions><![CDATA[<p>&nbsp;A1</p>]]></actions>
   <expectedresults><![CDATA[<p>&nbsp;EXC1</p>]]></expectedresults>
   <execution_type><![CDATA[1]]></execution_type>
</step>

<step>
   <step_number><![CDATA[2]]></step_number>
   <actions><![CDATA[<p>&nbsp;A2</p>]]></actions>
   <expectedresults><![CDATA[<p>&nbsp;EXC2</p>]]></expectedresults>
   <execution_type><![CDATA[2]]></execution_type>
</step>
</steps>
</testcase>
</testcases>

But I need you to export this way:

<testcases>
   <!-- Aqui começa o caso de teste 1 --> 
<testcase internalid="980" name="Caso de testes Importação">
   <node_order><![CDATA[100]]></node_order>
   <externalid><![CDATA[1]]></externalid>
   <version><![CDATA[1]]></version>
   <summary><![CDATA[<p>&nbsp;O caso de teste deve fazer isso   </p>]]></summary>
   <preconditions><![CDATA[<p>&nbsp;<a id="fck_paste_padding"></a>&nbsp;Pré Condições od caso de testes</p>]]></preconditions>
   <execution_type><![CDATA[1]]></execution_type>
   <importance><![CDATA[2]]></importance>
<steps>
<step>
   <step_number><![CDATA[1]]></step_number>
   <actions><![CDATA[<p>&nbsp;A1</p>]]></actions>
   <expectedresults><![CDATA[<p>&nbsp;EXC1</p>]]></expectedresults>
   <execution_type><![CDATA[1]]></execution_type>
</step>

<step>
   <step_number><![CDATA[2]]></step_number>
   <actions><![CDATA[<p>&nbsp;A2</p>]]></actions>
   <expectedresults><![CDATA[<p>&nbsp;EXC2</p>]]></expectedresults>
   <execution_type><![CDATA[2]]></execution_type>
</step>
</steps>
</testcase>

   <!-- Aqui Termina o caso de teste 1 --> 



      <!-- Aqui começa o caso de teste 2 --> 
<testcase internalid="981" name="Caso de testes Importação">
   <node_order><![CDATA[100]]></node_order>
   <externalid><![CDATA[2]]></externalid>
   <version><![CDATA[1]]></version>
   <summary><![CDATA[<p>&nbsp;O caso de teste deve fazer isso   </p>]]></summary>
   <preconditions><![CDATA[<p>&nbsp;<a id="fck_paste_padding"></a>&nbsp;Pré Condições od caso de testes</p>]]></preconditions>
   <execution_type><![CDATA[1]]></execution_type>
   <importance><![CDATA[2]]></importance>
<steps>
<step>
   <step_number><![CDATA[1]]></step_number>
   <actions><![CDATA[<p>&nbsp;A1</p>]]></actions>
   <expectedresults><![CDATA[<p>&nbsp;EXC1</p>]]></expectedresults>
   <execution_type><![CDATA[1]]></execution_type>
</step>

<step>
   <step_number><![CDATA[2]]></step_number>
   <actions><![CDATA[<p>&nbsp;A2</p>]]></actions>
   <expectedresults><![CDATA[<p>&nbsp;EXC2</p>]]></expectedresults>
   <execution_type><![CDATA[2]]></execution_type>
</step>
</steps>
</testcase>

   <!-- Aqui Termina o caso de teste 2 --> 

</testcases>

Worksheet structure:

Answer 1

Good morning!

As far as I know and worked with XML in Excel, there are two files one works with two XML files, one containing the data (.xml) and the other containing the schemas (.xsd).

I don’t know how is your XML map in Excel that can be viewed via tab Developer > Source Code (XML), however, some parameters that Excel understands as repeated data are <xsd:sequence> (all together).

Below is an example of a scheme run for exporting multiple data:

<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <xsd:element name="Cadastro">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element minOccurs="0" maxOccurs="unbounded" name="Pessoa">
          <xsd:complexType>
            <xsd:all>
              <xsd:element minOccurs="0" maxOccurs="1" name="Nome" type="xsd:string"/>
              <xsd:element minOccurs="0" maxOccurs="1" name="Nascimento" type="xsd:date"/>
              <xsd:element minOccurs="0" maxOccurs="1" name="Telefone" />
              <xsd:element minOccurs="0" maxOccurs="1" name="Calc" />
              <xsd:element minOccurs="0" maxOccurs="1" name="Test" type="xsd:integer"/>
            </xsd:all>
          </xsd:complexType>
        </xsd:element>        
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

To test, follow a step by step:

1. Save this code above in a document with extension .xsd (ex. mapa_cadastro.xsd)

2. Open Excel, on the developer tab click Source Code, on the XML option, click on XML maps and add the saved XML map (mapa_cadastro.xsd), as below:

3. After viewing the map, select all items and paste into the sheet as shown below:

4. Enter some data and export it in XML via tab Developer > Export (XML)

All data, including all rows filled in, will be in your XML.

I hope I’ve helped.

[EDITION 1]

If you have your XML ready, an example of what you need, with multiple lines, you can try importing the file (.xml) to Excel, which will generate the map automatically for you.

Try after that to change the data, insert new lines and export, this should also work, but you will not have the map (.xsd) designed to edit some information (header) or data type that needs to be exported in XML.