{"id":1821,"date":"2026-11-12T07:51:23","date_gmt":"2026-11-12T07:51:23","guid":{"rendered":"https:\/\/www.coolutils.com\/blog\/?p=1821"},"modified":"2026-11-12T07:51:23","modified_gmt":"2026-11-12T07:51:23","slug":"how-to-open-xml-file-in-excel","status":"publish","type":"post","link":"https:\/\/www.coolutils.com\/blog\/how-to-open-xml-file-in-excel\/","title":{"rendered":"How to Open an XML File in Excel with Power Query"},"content":{"rendered":"<div id=\"bsf_rt_marker\"><\/div><p>In Microsoft 365 Excel for Windows, choose <strong>Data &gt; Get Data &gt; From File &gt; From XML<\/strong>, select the local <code>.xml<\/code> file, and preview its data in <strong>Navigator<\/strong>. Choose the collection that contains the records you want in your worksheet. Select <strong>Transform Data<\/strong> if you need to expand a column or set its type before loading the table.<\/p>\n<p>This example turns two XML product records into two worksheet rows. It also shows how to keep the leading zeros in product codes. You can view XML tags in a text editor to see how a file is organized. In Excel, use the XML connector to put the values from those tags into columns you can sort or calculate with. The steps here are for an XML data file stored on your computer.<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_83 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\r\n<div class=\"ez-toc-title-container\">\r\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\r\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\r\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.coolutils.com\/blog\/how-to-open-xml-file-in-excel\/#Download_the_sample_XML_file\" >Download the sample XML file<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.coolutils.com\/blog\/how-to-open-xml-file-in-excel\/#Open_the_XML_file_and_choose_the_product_records\" >Open the XML file and choose the product records<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.coolutils.com\/blog\/how-to-open-xml-file-in-excel\/#Keep_the_product_codes_as_text\" >Keep the product codes as text<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/www.coolutils.com\/blog\/how-to-open-xml-file-in-excel\/#Load_the_table_and_save_the_workbook\" >Load the table and save the workbook<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/www.coolutils.com\/blog\/how-to-open-xml-file-in-excel\/#Fix_common_XML_import_problems\" >Fix common XML import problems<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/www.coolutils.com\/blog\/how-to-open-xml-file-in-excel\/#Check_a_larger_file_with_a_known_record\" >Check a larger file with a known record<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/www.coolutils.com\/blog\/how-to-open-xml-file-in-excel\/#Choose_a_route_for_your_XML_file\" >Choose a route for your XML file<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/www.coolutils.com\/blog\/how-to-open-xml-file-in-excel\/#Frequently_asked_questions\" >Frequently asked questions<\/a><\/li><\/ul><\/nav><\/div>\r\n<h2><span class=\"ez-toc-section\" id=\"Download_the_sample_XML_file\"><\/span>Download the sample XML file<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Download <a href=\"https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/xml-excel-sample.xml\">xml-excel-sample.xml<\/a>. You can also paste the example into a plain-text editor and save it as a UTF-8 file with the <code>.xml<\/code> extension. Keep that copy as the source for the import. Use a separate name when you save the Excel workbook.<\/p>\n<pre><code class=\"language-xml\">&lt;?xml version=\"1.0\" encoding=\"UTF-8\"?&gt;\n&lt;products&gt;\n  &lt;product&gt;\n    &lt;code&gt;00127&lt;\/code&gt;\n    &lt;name&gt;Blue notebook&lt;\/name&gt;\n    &lt;quantity&gt;3&lt;\/quantity&gt;\n  &lt;\/product&gt;\n  &lt;product&gt;\n    &lt;code&gt;00128&lt;\/code&gt;\n    &lt;name&gt;Graph notebook&lt;\/name&gt;\n    &lt;quantity&gt;5&lt;\/quantity&gt;\n  &lt;\/product&gt;\n&lt;\/products&gt;<\/code><\/pre>\n<p>The outer <code>products<\/code> element holds two <code>product<\/code> elements. Each <code>product<\/code> has a <code>code<\/code>, <code>name<\/code> and <code>quantity<\/code>. A worksheet row should represent one product. The expected result has two rows, and the quantity total is <code>8<\/code>.<\/p>\n<table>\n<thead>\n<tr>\n<th>code (Text)<\/th>\n<th>name (Text)<\/th>\n<th>quantity (Whole Number)<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>00127<\/td>\n<td>Blue notebook<\/td>\n<td>3<\/td>\n<\/tr>\n<tr>\n<td>00128<\/td>\n<td>Graph notebook<\/td>\n<td>5<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>The codes are identifiers. Keep the leading zeros so the cells contain the same codes you see in the source. The quantity column contains numbers you can add. For this sample, both columns use digits in the source, so check their types separately after import.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Open_the_XML_file_and_choose_the_product_records\"><\/span>Open the XML file and choose the product records<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<ol>\n<li>Open a blank workbook in Microsoft 365 Excel for Windows. On the <strong>Data<\/strong> tab, choose <strong>Get Data &gt; From File &gt; From XML<\/strong>.<\/li>\n<li>Browse to <code>xml-excel-sample.xml<\/code> and select <strong>Open<\/strong>. If you have multiple exports, confirm you selected the XML source.<\/li>\n<\/ol>\n<figure><img decoding=\"async\" class=\"wp-image-1827 size-full\" src=\"https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/xml-excel-menu-en.png\" alt=\"Excel Get Data menu with the From XML command\" width=\"1440\" height=\"900\" loading=\"lazy\" srcset=\"https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/xml-excel-menu-en.png 1440w, https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/xml-excel-menu-en-300x188.png 300w, https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/xml-excel-menu-en-1024x640.png 1024w, https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/xml-excel-menu-en-768x480.png 768w, https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/xml-excel-menu-en-1200x750.png 1200w\" sizes=\"auto, (max-width: 709px) 85vw, (max-width: 909px) 67vw, (max-width: 1362px) 62vw, 840px\" \/><\/figure>\n<ol start=\"3\">\n<li>In <strong>Navigator<\/strong>, select the available collection and inspect its preview. Look for the two <code>product<\/code> entries or a nested collection that contains them. Use the values in the preview to identify the product level. A label on its own may describe the outer container.<\/li>\n<li>Choose <strong>Transform Data<\/strong>. In Power Query Editor, check whether the preview already has <code>code<\/code>, <code>name<\/code> and <code>quantity<\/code>. If so, you can move on to the column types.<\/li>\n<li>If the product values are inside a <strong>Table<\/strong> or <strong>Record<\/strong> column, inspect it to confirm it contains the product fields. Use the expand control for that column, select <code>code<\/code>, <code>name<\/code> and <code>quantity<\/code>, and check the result. If a prefix appears in the resulting headers, rename the columns once you have verified their contents. You should now have two product rows.<\/li>\n<\/ol>\n<figure><img decoding=\"async\" class=\"wp-image-1828 size-full\" src=\"https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/xml-power-query-en.png\" alt=\"XML product records in Navigator or Power Query Editor\" width=\"1440\" height=\"900\" loading=\"lazy\" srcset=\"https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/xml-power-query-en.png 1440w, https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/xml-power-query-en-300x188.png 300w, https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/xml-power-query-en-1024x640.png 1024w, https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/xml-power-query-en-768x480.png 768w, https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/xml-power-query-en-1200x750.png 1200w\" sizes=\"auto, (max-width: 709px) 85vw, (max-width: 909px) 67vw, (max-width: 1362px) 62vw, 840px\" \/><\/figure>\n<p>The preview depends on the source structure. A collection may appear with all three columns, or its fields may sit one level deeper. In a larger file, inspect a few records and decide what each worksheet row should represent before expanding a column. For example, a product may have several stock locations. Expanding those locations gives you location rows and can repeat product details. The sample in this guide uses only the product level. <a href=\"https:\/\/learn.microsoft.com\/en-us\/power-query\/connectors\/xml\">Microsoft&#8217;s XML connector documentation<\/a><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Keep_the_product_codes_as_text\"><\/span>Keep the product codes as text<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>In Power Query Editor, check the type symbol beside each header. Set <code>code<\/code> and <code>name<\/code> to <strong>Text<\/strong> and set <code>quantity<\/code> to <strong>Whole Number<\/strong>. If Power Query already added a <strong>Changed Type<\/strong> step, select it and see what happens to <code>00127<\/code> and <code>00128<\/code>. Click the previous step to check where the zeros were still present.<\/p>\n<p>If the codes change to <code>127<\/code> and <code>128<\/code>, edit the first type step that interprets <code>code<\/code> as a number. For the simple sample, you can remove that automatic type step and apply the three types explicitly after the product fields are visible. If the conversion already happened earlier in the query, return to the source or expansion step that still contains the full codes before setting the type. Changing the final column to Text after it has already lost its zeros would store <code>127<\/code> and <code>128<\/code> as text.<\/p>\n<p>Check the value in each code cell before you load the worksheet. A display format that adds zeros to a number may make a cell look right, while the stored value is still numeric. Keeping the original value as text preserves the identifier when you use it for a lookup or compare it with another file. Check that the quantity column still contains the whole numbers <code>3<\/code> and <code>5<\/code>.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Load_the_table_and_save_the_workbook\"><\/span>Load the table and save the workbook<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>When the preview has two product rows and the expected column types, choose <strong>Home &gt; Close &amp; Load<\/strong>. If you need to choose the destination, use <strong>Close &amp; Load To<\/strong>, select <strong>Table<\/strong>, and load it to a <strong>New worksheet<\/strong>.<\/p>\n<p>Read across the two rows in the worksheet. <code>00127<\/code> should belong to Blue notebook, with quantity <code>3<\/code>. <code>00128<\/code> should belong to Graph notebook, with quantity <code>5<\/code>. If the table starts in cell A1 as shown in this example, <code>=SUM(C2:C3)<\/code> in an empty cell outside the table should return <code>8<\/code>. Check that both the names and quantities stayed with the right codes. A correct total can still hide two quantities assigned to the wrong rows.<\/p>\n<figure><img decoding=\"async\" class=\"wp-image-1829 size-full\" src=\"https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/xml-excel-result-en.png\" alt=\"Excel worksheet with product codes 00127 and 00128 and quantities 3 and 5\" width=\"1440\" height=\"900\" loading=\"lazy\" srcset=\"https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/xml-excel-result-en.png 1440w, https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/xml-excel-result-en-300x188.png 300w, https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/xml-excel-result-en-1024x640.png 1024w, https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/xml-excel-result-en-768x480.png 768w, https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/xml-excel-result-en-1200x750.png 1200w\" sizes=\"auto, (max-width: 709px) 85vw, (max-width: 909px) 67vw, (max-width: 1362px) 62vw, 840px\" \/><\/figure>\n<p>Save your imported result as <code>xml-excel-sample.xlsx<\/code> using <strong>File &gt; Save As<\/strong> and the <strong>Excel Workbook (.xlsx)<\/strong> format. Reopen the workbook and check both rows again. You can also download the <a href=\"https:\/\/www.coolutils.com\/blog\/wp-content\/uploads\/2026\/11\/xml-excel-sample.xlsx\">completed example workbook<\/a> and compare its table and total with your import. The downloadable example contains the checked result but no refreshable query. Keep the original XML alongside your own imported workbook. If you move the source file and later refresh your query, Excel may need its updated path.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Fix_common_XML_import_problems\"><\/span>Fix common XML import problems<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<table>\n<thead>\n<tr>\n<th>What you see<\/th>\n<th>What to check<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>The preview shows Table or Record<\/td>\n<td>Open the nested value and look for the expected record fields. Expand the collection you need, then check how many rows it produced.<\/td>\n<\/tr>\n<tr>\n<td>The codes appear as 127 and 128<\/td>\n<td>Find the first step where the leading zeros disappear. Set the field to Text at that step or earlier while the original characters are still available.<\/td>\n<\/tr>\n<tr>\n<td>The file does not parse<\/td>\n<td>Confirm that you selected an XML data file and open it in a text editor. Check the error location and look for an incomplete export or mismatched tags. If the source came from another system, request a fresh export when needed.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>For a file that imports with the wrong number of rows, start with one repeated element you recognize and compare it with the worksheet. The source may group records under another element or contain a list of details within each record. Track which expansion changed the count. If the worksheet should contain one row per product, check the product codes for repeats and inspect the nested fields that were expanded.<\/p>\n<p>An XML element can also contain text beside child elements. Inspect a few records after import to make sure the values you need reached the worksheet. Microsoft&#8217;s connector documentation explains how different XML structures can require additional shaping. If the real file fails but the sample opens, compare the file structure and the exact error before changing any data.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Check_a_larger_file_with_a_known_record\"><\/span>Check a larger file with a known record<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>When you move from the sample to a real export, pick one code you can locate easily in the source. Write down the name and quantity next to it, then find the same code in the worksheet. If you cannot find it, check the Navigator selection and any filters in Applied Steps. If you find several rows, inspect the collection you expanded and see what distinguishes those rows. A code that occurs once per product can legitimately repeat when the table includes more detailed records, such as multiple locations. Settle which rows you want before you calculate totals. Then repeat the same comparison with a record near the end of the export to catch an incomplete import.<\/p>\n<p>Look at missing fields before filling any blanks. An empty quantity in a real export could mean that the source has no count for that product. Entering zero would give the blank a different meaning and could change a summary. Find the matching product in the original XML and see whether the quantity element is empty, absent or present at another level. If the value is present in the source, follow that field through the preview and the applied steps to see where it disappeared. Then check the same row again after loading.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Choose_a_route_for_your_XML_file\"><\/span>Choose a route for your XML file<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Use the Excel workflow above when you want to inspect the records and set column types yourself. For a file you want to turn into a spreadsheet in a browser, use <a href=\"https:\/\/www.coolutils.com\/online\/XML-to-XLS\">convert XML to Excel online<\/a>. For a desktop batch workflow on Windows, see the <a href=\"https:\/\/www.coolutils.com\/TotalXMLConverter\/XML-to-XLSX\">Total XML Converter XML-to-XLSX guide<\/a>.<\/p>\n<p>For files with several levels of repeated elements, decide which level should become a row before choosing a workflow. For more examples, see <a href=\"https:\/\/www.coolutils.com\/blog\/xml-to-excel-repeating-elements-rows\/\">how XML elements become spreadsheet rows<\/a>.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Frequently_asked_questions\"><\/span>Frequently asked questions<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><strong>Can I import an XML file directly into Excel?<\/strong> Yes. In Microsoft 365 Excel for Windows, select <strong>From XML<\/strong> under <strong>Data &gt; Get Data &gt; From File<\/strong>. Preview the records in Navigator and choose the table you need.<\/p>\n<p><strong>Why does Navigator show a nested collection?<\/strong> XML can group several records inside a parent element. Inspect the collection, then open <strong>Transform Data<\/strong> if you need to expand the product fields before loading them.<\/p>\n<p><strong>How do I keep leading zeros in XML codes?<\/strong> Set the code field to Text while the original zeros are still present. Check any automatic type step before loading. In the sample, the saved table should show <code>00127<\/code> and <code>00128<\/code>.<\/p>\n<p><strong>What happens to my XML file when I save the workbook?<\/strong> Excel saves the table and query in a separate <code>.xlsx<\/code> file. You can keep the XML as your original and use it again when you need to check or refresh the imported data.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Open an XML file in Excel with Power Query. Follow a sample import, check the rows and values, and save a workbook you can use and share.<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[189],"tags":[],"class_list":["post-1821","post","type-post","status-publish","format-standard","hentry","category-office-converters"],"_links":{"self":[{"href":"https:\/\/www.coolutils.com\/blog\/wp-json\/wp\/v2\/posts\/1821","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.coolutils.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.coolutils.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.coolutils.com\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.coolutils.com\/blog\/wp-json\/wp\/v2\/comments?post=1821"}],"version-history":[{"count":1,"href":"https:\/\/www.coolutils.com\/blog\/wp-json\/wp\/v2\/posts\/1821\/revisions"}],"predecessor-version":[{"id":1832,"href":"https:\/\/www.coolutils.com\/blog\/wp-json\/wp\/v2\/posts\/1821\/revisions\/1832"}],"wp:attachment":[{"href":"https:\/\/www.coolutils.com\/blog\/wp-json\/wp\/v2\/media?parent=1821"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.coolutils.com\/blog\/wp-json\/wp\/v2\/categories?post=1821"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.coolutils.com\/blog\/wp-json\/wp\/v2\/tags?post=1821"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}