{"id":1776,"date":"2026-10-07T08:50:12","date_gmt":"2026-10-07T08:50:12","guid":{"rendered":"https:\/\/www.coolutils.com\/blog\/?p=1776"},"modified":"2026-10-07T08:50:12","modified_gmt":"2026-10-07T08:50:12","slug":"xml-to-excel-repeating-elements-rows","status":"publish","type":"post","link":"https:\/\/www.coolutils.com\/blog\/xml-to-excel-repeating-elements-rows\/","title":{"rendered":"XML to CSV or Excel: How to Handle Repeating Elements and Check Your Rows"},"content":{"rendered":"<div id=\"bsf_rt_marker\"><\/div><p><small>Photo by <a href=\"https:\/\/unsplash.com\/photos\/hands-typing-on-a-laptop-with-a-spreadsheet-on-screen-iDqNlr1Y1_w\">Bluestonex on Unsplash<\/a>. Editorial illustration, not a CoolUtils screenshot.<\/small><\/p>\n<p>Before converting XML to CSV or Excel, decide what one spreadsheet row should represent. An order, a line item, and a customer are different records. If one order contains several items, an item-level table needs several rows, with the order identifier repeated on each. Choosing that structure first makes it easier to spot missing records, misplaced values, and totals that have been counted twice.<\/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\/xml-to-excel-repeating-elements-rows\/#Choose_the_record_that_belongs_on_each_row\" >Choose the record that belongs on each row<\/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\/xml-to-excel-repeating-elements-rows\/#Two_valid_tables_from_the_same_XML\" >Two valid tables from the same XML<\/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\/xml-to-excel-repeating-elements-rows\/#Map_paths_and_attributes_not_just_matching_names\" >Map paths and attributes, not just matching names<\/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\/xml-to-excel-repeating-elements-rows\/#Decide_what_blank_cells_mean\" >Decide what blank cells mean<\/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\/xml-to-excel-repeating-elements-rows\/#Keep_independent_repeating_groups_separate\" >Keep independent repeating groups separate<\/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\/xml-to-excel-repeating-elements-rows\/#Convert_a_sample_then_check_the_relationships\" >Convert a sample, then check the relationships<\/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\/xml-to-excel-repeating-elements-rows\/#Choose_CSV_or_XLSX_for_the_next_step\" >Choose CSV or XLSX for the next step<\/a><\/li><\/ul><\/nav><\/div>\r\n<h2><span class=\"ez-toc-section\" id=\"Choose_the_record_that_belongs_on_each_row\"><\/span>Choose the record that belongs on each row<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>XML stores information in a hierarchy. A spreadsheet table presents values in rows and columns. Moving between them requires a choice about which level of that hierarchy becomes a row.<\/p>\n<p>Suppose you need a list of orders for an account manager. One row per order may be enough. If you need quantities by product, each order line needs its own row. Both tables can come from the same XML, but they answer different questions.<\/p>\n<p>Use this small example to make the choice explicit. The businesses, identifiers, and quantities below are invented for illustration; the tables show intended mappings, not output from a tested converter run.<\/p>\n<pre><code class=\"language-xml\">&lt;orders&gt;\n  &lt;order id=&quot;A100&quot;&gt;\n    &lt;customer&gt;North Studio&lt;\/customer&gt;\n    &lt;items&gt;\n      &lt;item sku=&quot;PEN-01&quot;&gt;\n        &lt;quantity&gt;2&lt;\/quantity&gt;\n        &lt;note&gt;Blue ink&lt;\/note&gt;\n      &lt;\/item&gt;\n      &lt;item sku=&quot;PAD-02&quot;&gt;\n        &lt;quantity&gt;1&lt;\/quantity&gt;\n        &lt;note\/&gt;\n      &lt;\/item&gt;\n    &lt;\/items&gt;\n  &lt;\/order&gt;\n  &lt;order id=&quot;A101&quot;&gt;\n    &lt;customer&gt;West Workshop&lt;\/customer&gt;\n    &lt;items&gt;\n      &lt;item sku=&quot;PEN-01&quot;&gt;\n        &lt;quantity&gt;4&lt;\/quantity&gt;\n      &lt;\/item&gt;\n    &lt;\/items&gt;\n  &lt;\/order&gt;\n&lt;\/orders&gt;\n<\/code><\/pre>\n<p>There are two <code>order<\/code> elements and three <code>item<\/code> elements. Two rows would be correct for an order summary. Three rows would be correct for a line-item table. The number of source files tells you neither of those counts.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Two_valid_tables_from_the_same_XML\"><\/span>Two valid tables from the same XML<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>An order summary can keep the customer and the number of line items together. Here, <code>line_item_count<\/code> is a value calculated by counting the child <code>item<\/code> elements; it is not a field already present in the XML.<\/p>\n<table>\n<thead>\n<tr>\n<th>order_id<\/th>\n<th>customer<\/th>\n<th align=\"right\">line_item_count<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>A100<\/td>\n<td>North Studio<\/td>\n<td align=\"right\">2<\/td>\n<\/tr>\n<tr>\n<td>A101<\/td>\n<td>West Workshop<\/td>\n<td align=\"right\">1<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>For product-level analysis, use a row for each item and carry the parent order information onto that row:<\/p>\n<table>\n<thead>\n<tr>\n<th>order_id<\/th>\n<th>customer<\/th>\n<th>sku<\/th>\n<th align=\"right\">quantity<\/th>\n<th>note<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>A100<\/td>\n<td>North Studio<\/td>\n<td>PEN-01<\/td>\n<td align=\"right\">2<\/td>\n<td>Blue ink<\/td>\n<\/tr>\n<tr>\n<td>A100<\/td>\n<td>North Studio<\/td>\n<td>PAD-02<\/td>\n<td align=\"right\">1<\/td>\n<td><\/td>\n<\/tr>\n<tr>\n<td>A101<\/td>\n<td>West Workshop<\/td>\n<td>PEN-01<\/td>\n<td align=\"right\">4<\/td>\n<td><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Repeating <code>A100<\/code> here preserves the relationship between the two items and their order. It is not a duplicate to remove. The same SKU also appears in two different orders, so a repeated product code is not enough to identify a duplicate record.<\/p>\n<p>Watch what happens to parent-level totals. If an order total of 50 were repeated on two item rows, adding that column would produce 100. Keep order totals in the order table, or use an analysis method that counts each order once. Do not sum a repeated parent value as though it belonged to each item.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Map_paths_and_attributes_not_just_matching_names\"><\/span>Map paths and attributes, not just matching names<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>In the example, the order identifier is an attribute: <code>order\/@id<\/code>. The SKU is another attribute: <code>item\/@sku<\/code>. The quantity is text inside a child element. A mapping that collects only element text can leave the identifiers out.<\/p>\n<p>Write a short field map before processing a larger export:<\/p>\n<table>\n<thead>\n<tr>\n<th>Output column<\/th>\n<th>Source in this example<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>order_id<\/td>\n<td><code>id<\/code> attribute of the item&#39;s parent order<\/td>\n<\/tr>\n<tr>\n<td>customer<\/td>\n<td><code>customer<\/code> element in that order<\/td>\n<\/tr>\n<tr>\n<td>sku<\/td>\n<td><code>sku<\/code> attribute of the current item<\/td>\n<\/tr>\n<tr>\n<td>quantity<\/td>\n<td><code>quantity<\/code> element in the current item<\/td>\n<\/tr>\n<tr>\n<td>note<\/td>\n<td><code>note<\/code> element in the current item, if present<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Full paths become especially useful when a file contains several elements named <code>name<\/code>, <code>id<\/code>, or <code>date<\/code> in different branches. XML namespaces also matter when identifying elements. The <a href=\"https:\/\/www.w3.org\/TR\/1999\/REC-xpath-19991116\/\">W3C XPath specification<\/a> describes paths and attribute selection; this field map does not assume a particular converter&#39;s mapping syntax.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Decide_what_blank_cells_mean\"><\/span>Decide what blank cells mean<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The second item has an empty <code>note<\/code> element. The third item has no <code>note<\/code> element at all. Both appear as blank cells in the table above because this example deliberately maps them that way.<\/p>\n<p>That may be acceptable for a reading sheet. It may be wrong for a data import where &#8220;present but empty&#8221; means clear the existing value, while &#8220;absent&#8221; means leave it unchanged. If the distinction matters, define a separate status column or another explicit representation before conversion. A zero quantity is a value and should not become an empty cell.<\/p>\n<p>Also check identifiers that contain leading zeros, long digit strings, and dates. When opening CSV in a spreadsheet, import those columns with the intended data type. Inspect the saved result as well as the initial preview: an identifier that looks like a number may be reformatted.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Keep_independent_repeating_groups_separate\"><\/span>Keep independent repeating groups separate<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>An order might contain two items and three delivery contacts. If a flattening rule pairs every item with every contact, that order produces six rows. Those six rows can be mathematically consistent with the rule and still be wrong for your report.<\/p>\n<p>For independent lists, separate related tables are often clearer: an orders table, an items table, and a contacts table, each linked by the order identifier. Specify that requirement rather than assuming a converter will choose it for you.<\/p>\n<p>Check orders with no items, too. They contribute no rows to a table containing only items. Keep an order-level table if those orders must remain visible. A successful conversion message cannot tell you whether you chose the right record level.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Convert_a_sample_then_check_the_relationships\"><\/span>Convert a sample, then check the relationships<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><a href=\"https:\/\/www.coolutils.com\/TotalXMLConverter\">Total XML Converter<\/a> is a Windows desktop program with CSV and XLSX output and support for applying an XSLT stylesheet. Its <a href=\"https:\/\/www.coolutils.com\/TotalXMLConverter\/XML-to-XLSX\">XML-to-XLSX guide<\/a> explains the standard conversion steps. Your file structure and required table still determine what a useful result looks like.<\/p>\n<p>Choose a representative sample that includes a single-item order, a multi-item order, and missing optional values. Include separate repeating groups if your real data has them. Then check:<\/p>\n<ol>\n<li><strong>Coverage:<\/strong> the sample above has two orders and three items. Confirm the count at the level chosen for the table.<\/li>\n<li><strong>Relationships:<\/strong> both A100 rows must belong to North Studio; the A101 row must belong to West Workshop.<\/li>\n<li><strong>Values:<\/strong> item quantities are 2, 1, and 4, with a total of 7. A matching total is useful, but does not replace checking the individual rows.<\/li>\n<li><strong>Missing values:<\/strong> confirm the intended treatment of empty and absent notes.<\/li>\n<li><strong>Data types:<\/strong> check identifiers, dates, decimal values, and non-English characters in the application that will consume the output.<\/li>\n<\/ol>\n<p>If the standard result uses the wrong record level, define the required transformation before processing the full collection. XSLT support does not establish that the example tables above appear automatically with default settings. This article does not prescribe an untested stylesheet or claim a measured result from the application.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Choose_CSV_or_XLSX_for_the_next_step\"><\/span>Choose CSV or XLSX for the next step<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>CSV suits a single rectangular table that another system will import. Separate related tables need separate files and a shared identifier. XLSX suits work in Excel and can hold multiple sheets, but the format&#39;s capabilities do not mean a converter will automatically create the particular multi-sheet arrangement you want.<\/p>\n<p>Choose the row structure first, then the format. Keep the source XML with your field map and record the settings used for the accepted sample. That gives you a reference when the next export has different fields or a different nesting pattern.<\/p>\n<p><a href=\"https:\/\/www.coolutils.com\/Downloads\/TotalXMLConverter.exe\">Download Free Trial of Total XML Converter<\/a> to try your own sample and compare the output with the table you need.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Choose the right rows when converting XML to CSV or Excel. See a worked example of repeated items, parent IDs, blank values, and checks before a full export.<\/p>\n","protected":false},"author":3,"featured_media":1777,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[189],"tags":[],"class_list":["post-1776","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-office-converters"],"_links":{"self":[{"href":"https:\/\/www.coolutils.com\/blog\/wp-json\/wp\/v2\/posts\/1776","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=1776"}],"version-history":[{"count":1,"href":"https:\/\/www.coolutils.com\/blog\/wp-json\/wp\/v2\/posts\/1776\/revisions"}],"predecessor-version":[{"id":1778,"href":"https:\/\/www.coolutils.com\/blog\/wp-json\/wp\/v2\/posts\/1776\/revisions\/1778"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.coolutils.com\/blog\/wp-json\/wp\/v2\/media\/1777"}],"wp:attachment":[{"href":"https:\/\/www.coolutils.com\/blog\/wp-json\/wp\/v2\/media?parent=1776"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.coolutils.com\/blog\/wp-json\/wp\/v2\/categories?post=1776"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.coolutils.com\/blog\/wp-json\/wp\/v2\/tags?post=1776"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}