Have coding elements a placement cell

Map XML elements to cells in an XML map

A useful feature for importing and exporting XML data into Excel is an XML map, which links XML elements to data in cells to get the results you want. To create such a mapping, you need an XML schema file (XSD) and an XML data file (XML). After creating the XML map, you can map XML elements in the way you want.

Search for or create XML schema and XML data files

If an XML schema or XML data file was created by another database or application, they may already be available. For example, you might have a business application that exports data to these XML file formats, a commercial website or web service that supplies these XML files, or a custom application developed by your IT department that uses this XML - Files created automatically.

If you don't have the required XML files, you can create them by saving the data you want to use as a text file. You can then convert this text file into the required XML files with both Access and Excel. To do this, proceed as follows:

Access

  1. Import the text file to be converted and link it to a new table.

    1. click on file > to open.

    2. In the dialog box, choose to open and open the database in which you want to create a new table.

    3. click on External data > Text file, and follow the instructions for each step. Make sure to link the table to the text file.

      The new table is created by Access and displayed in the navigation area.

  2. Export the data from the linked table to an XML data file and an XML schema file.

    1. click on External data > XML file (in the group Export).

    2. In the dialog box, enter Export - XML ​​files the file name and format, and click OK.

  3. Quit Access.

Excel

  1. Create an XML map based on the XML schema file exported from Access.

    When the Multiple roots appears, you need the item dataroot so you can create an XML table.

  2. Create an XML table by adding the element dataroot assign. For more information, see Mapping XML Elements.

  3. Import the XML file that you exported from Access.

Hints:

  • There are several XML schema element constructs that Excel does not support. The following constructs cannot be imported into Excel:

  • <einer> This element enables the inclusion of elements that are not declared in the schema.

  • <anyAttribute-> This element enables the inclusion of attributes that are not declared in the schema.

  • Recursive structures A typical example of a recursive structure is a hierarchy of employees and managers in which the same XML elements are nested on several levels. Recursive structures with a depth of more than one level are not supported in Excel.

  • Abstract elements These elements should be declared in the schema, but never used as elements. Abstract elements depend on other elements that abstract elements are replaced with.

  • Substitution groups These groups allow an item to be swapped when another item is referenced. An element gives through the attribute <substitutionGroup> indicates that it is a member of another element's substitution group.

  • Mixed content When defining complex types, this content is declared using mixed = "true". Excel does not support the simple content of the complex type, but it does support the child tags and attributes defined in this complex type.

Using sample XML schema and data files

The following sample data contains basic XML elements and structures that you can use to test XML mapping if you do not have XML files or text files to create the XML files. Here's how to save this sample data to files on your computer:

  1. Highlight the sample text of the file you want to copy and press Ctrl + C.

  2. Start Notepad and press Ctrl + V to paste the sample text.

  3. Press Ctrl + S to save the file with the filename and extension of the copied sample data.

  4. In Notepad, press Ctrl + N and repeat steps 1-3 to create a file for the second sample text.

  5. Quit Notepad.

Sample XML Data (Expenses.xml)

Sample XML Schema (Expenses.xsd)

Create an XML Map

An XML map is created by adding an XML schema to a workbook. The schema can be copied from an XML schema file (XSD) or derived by Excel from an XML data file (XML).

  1. click on Developer tools > source.

    When the tab Developer tools If you don't see it, see Viewing the Developer Tab.

  2. Click in the task pane XML source on XML mappings and then on Add.

  3. Click in the list Search in the drive, folder, or Internet location that contains the file you want to open.

  4. Click the file, then click to open.

    • When you open an XML schema file, XML creates an XML map based on the XML schema. When the Multiple roots appears, select one of the root nodes defined in the XML schema file.

    • When you open an XML data file, Excel attempts to infer the XML schema from the XML data and then creates an XML map.

  5. click on OK.

    The XML mapping is done in the task pane XML source displayed.

Mapping XML elements

XML elements are mapped to individual and repeated cells in XML tables in such a way that a relationship is established between the cell and the XML data element in the XML schema.

  1. click on Developer tools > source.

    When the tab Developer tools If you don't see it, see Viewing the Developer Tab.

  2. Select in the task area XML source select the items that you want to map.

    To select non-contiguous items, click an item. Then hold down Ctrl and click each item that you want to map.

  3. Proceed as follows to assign the elements:

    1. Right-click the selected items and click Link element.

    2. In the dialog box, choose Map XML elements select a cell and click OK.

      Tip: You can also drag the selected items to where you want them to appear on the worksheet.

      Each item is in the work area XML source displayed in bold. This indicates that the item is assigned.

  4. Think about how you want labels and column headings to be treated:

    • If you drag a non-repeating XML element onto the worksheet and create a single mapped cell, you'll see a smart tag with three commands to control the placement of the header or label:

      My data already has a header Click this option to ignore the XML element header because the cell already has a header (on the left or above the data).

      Arrange XML header on the left Click this option if you want the XML element header to be used as the cell label (on the left side of the data).

      Arrange XML header above Click this option if you want the header of the XML element to be used as the cell header (above the data).

    • If you drag a repeating XML element onto the worksheet and create repeating cells in an XML table, the XML element names are automatically used as column headings for the table. However, you can change the column headings as you wish by editing the associated cells.

      For further control of the behavior of the XML table, click in the work area XML source on Options:

      Automatically merge elements when linked If this check box is selected, XML tables will automatically expand when you drag an item into a cell next to the XML table.

      My dates have headings When this check box is selected and you assign repeating items to your worksheet, existing data can be used as column headings.

      Hints:

      • If all XML commands are grayed out and you cannot map XML elements to cells, the workbook may have been shared. click on Check > Share workbookto verify and deauthorize the workbook as needed.

        To map XML elements in a workbook you want to share, map them to the cells you want, import the XML data, remove any XML mappings, and then share the workbook.

      • If you cannot copy an XML table that contains data to another workbook, the table may have an associated XML map that defines the data structure. This XML map is stored in the workbook, but if you copy the XML table to a new workbook, the map is not automatically included. Instead of copying the XML table, Excel creates its own table that contains the same data. If you want the new table to be an XML table, do the following:

        1. Add an XML map to the new workbook using the XML or XSD file that you used to create the original XML map. If you want to add XML maps to other workbooks, you should save these files.

        2. Map the XML elements to the table to convert the table into an XML table.

      • When you map a repeating XML element to a merged cell, Excel disconnects the cell. This is expected behavior because repeated elements are only intended to work on disconnected cells.

        You can map individual, non-repeating XML elements to a merged cell, but you cannot map a repeating XML element (or an element that contains a repeating element) to a merged cell. The cell is disconnected and the element is assigned to the cell in which the mouse pointer is located.

Tips:

  • You can map XML elements that you do not want to use or prevent the contents of cells from being overwritten when importing XML data. For example, you could temporarily detach an XML element from a single cell or from repeating cells that contain formulas that you do not want to be overwritten when you import an XML file. After the import is complete, you can reassign the XML element to the formula cells so that you can export the results of the formulas to the XML data file.

  • To disassociate an XML element, right-click in the task pane XML source its name, and then click Remove item.

Show the Developer Tab

When the tab Developer tools does not appear, do the following to view it:

  • In Excel 2010 and newer versions:

    1. click on file > Options.

    2. Click on the category Customize the ribbon.

    3. Activate under Main tabs check box Developer toolsand click OK.

  • In Excel 2007:

    1. click on Microsoft Office button > Excel Options.

    2. Click on the category Popular.

    3. Activate under The most common options when working with Excel check box Show developer tab in ribbonand click OK.

See also

Delete XML mapping information from a workbook

Appending or overwriting associated XML data

Overview of XML in Excel

Importing XML data

Exporting XML Data