The acronym XML means Extensible Mark-up Language. This feature/ format is used for storing data used in various applications and sharing. Unlike Notepad, which easily opens XML files, the result may not be readable. Google Sheets allows you to import XML data easily and edit it. There are various ways to import XML data into Google Sheets.
Importing XML to Google Sheets Manually
Table of Contents
Use the following steps:
1. “Copy and paste” the contents of the XML file into Google Sheets
2. You can do this by highlighting the content in the XML file, then press “Ctrl+ C “to copy and “Ctrl +V” to paste the content into Google Sheets
The method is tiresome and inefficient as it can only be used with a small set of data.
Using the IMPORTXML function
This function is mostly used to import structured data in XNL, TSV, and HTML. The syntax used in this case is =IMPORTXML (URL, xpath_query)
According to the syntax,
URL is an address that is used for importing the data
Xpath_query specifies the kind of information you are trying to fetch from XML to Google Sheets. Follow these steps:
1. Make sure that the URL is valid
2. Enclose the URL with two quotes
3. Press Enter
4. Type in the formula above. Google Sheets will thus try to fetch the information being copied to it from XML. Click above the cell you are copying. The data will successfully be copied to the cell.
The following example shows you how you can open the XML file in Google Sheets by using the IMPORTXML formula
1. On your browser, open the XML file
2. Copy the URL of the formula
3. Open your Google Sheet file and choose a cell, then type the formula, pasting the URL of the file in XML.
4. The URL of the XML file should be placed in double quotation marks to make it valid
5. Press the Enter Key and give it time to copy.
Importing XML table to Google Sheets
Follow these steps:
1. Open the website that contains the table
2. Copy the URL of that website table
3. Right-click on the XML file
4. Click on Inspect
5. In this way, the source code will be opened
6. To get the whole table, use the “//tr” feature
To import the XML class to Google sheets, use the steps given below.
1. Open the website that has the class
2. Copy the URL, ensuring that as you copy it to the formula, the URL should be in double quotation marks for validity purposes.
3. Right-click on that XML element
4. Choose Inspect
5. The source code is thus opened, and the data you want to copy is tagged
6. Look up the class name of your data
7. To get the contents of the class you intend to copy to Google Sheets, the formula will be IMPORTXML (“https://en.wikipedia.org/wiki/Me)
By using this function, information is pulled from XML to Google Sheets.