I'm trying to map XML to a range of rows but, for some reason, Excel (2007)
is only mapping it to the first row of the range. I've created an XML file
and imported it. Then, I right-click on the root element and choose Map
Element. Excel asks "Where do you want to map the XML elements?" I click and
drag a range of contiguous cells and it updates the range in the little
dialog box. The range displayed include the full range of columns and rows so
I hit OK. But then Excel only shows the first row with the blue border. And
when I export it only exports the first row.

What am I doing wrong? I've done this before successfully with a slightly
different map and it was working fine. I can't figure out what I'm doing
wrong today.

David Salahi

OK, I figured out the answer to my own question. The problem was that the
sample XML that I created to define the XML map had only a single XML node.
You have to have at least two XML nodes or "records" in order to map the node
as a repeating element.

Dave

On Nov 12, 11:56*pm, DaveLS wrote:
OK, I figured out the answer to my own question. The problem was that the
sample XML that I created to define the XML map had only a single XML node.
You have to have at least two XML nodes or "records" in order to map the node
as a repeating element.

Dave

Hi Dave,

I have a similar problem and i've actually tried your solution above
to no avail. May I email you my spreadsheet and the sample XML i'm
using? I'll really appreciate your help. I'd actually now resorted to
mapping each row (1199 rows) in excel to each element in the xml and
this is a painful task. I'll really appreciate your assistance in this
regard.

Thanks in advance.
Simon

Me too I have the exact same problem as Simon! I have about 700 lines.
Thanks Mack

Oh my goodness. Thank you for taking the time to answer your own question. I've been trying to figure this out for about four hours. Your solution worked fine for me. Life-saver, thanks!!

On Thursday, November 12, 2009 at 11:56:02 PM UTC+2, DaveLS wrote:
OK, I figured out the answer to my own question. The problem was that the
sample XML that I created to define the XML map had only a single XML node.
You have to have at least two XML nodes or "records" in order to map the node
as a repeating element.

Dave

I cant thank you enough for posting this. It solved a problem I've been trying to figure out for hours. Thank you

On Thursday, November 12, 2009 at 4:56:02 PM UTC-5, DaveLS wrote:
OK, I figured out the answer to my own question. The problem was that the
sample XML that I created to define the XML map had only a single XML node.
You have to have at least two XML nodes or "records" in order to map the node
as a repeating element.

Dave

Thanks

Il giorno giovedì 12 novembre 2009 22:56:02 UTC+1, DaveLS ha scritto:
OK, I figured out the answer to my own question. The problem was that the
sample XML that I created to define the XML map had only a single XML node.
You have to have at least two XML nodes or "records" in order to map the node
as a repeating element.

Dave

Thank you, thank you, thank you!

Hi Dave

Thanks I was having this exact issue... You are my saviour!

ME

On Thursday, November 12, 2009 at 5:56:02 PM UTC-4, DaveLS wrote:
OK, I figured out the answer to my own question. The problem was that the
sample XML that I created to define the XML map had only a single XML node.
You have to have at least two XML nodes or "records" in order to map the node
as a repeating element.

Dave

I had a similar problem but the XML structure was complex enough that some XML files had single records where other XML files had repeating elements, across hundreds of elements.

Here's how I overcame it:
I learned from here that you can dig into the workbook structure of an excel file and access the xml code of the XML map directly: https://stackoverflow.com/questions/...-to-an-xml-map

I followed these instructions to convert an xlsx to zip, unzip, and access the xmlMaps.xml file: http://professor-excel.com/xml-zip-e...ile-structure/

And I used some insight from this thread to do some analysis of the xml file and I did a find / replace to change maxOccurs="1" to maxOccurs="unbounded" for those elements I needed to see as repeating elements rather than single record: https://answers.microsoft.com/en-us/...a-c3a0b94a8b1e

Maybe this will help others in the future :)
DC

dcor,

I have the same problem except for when I dig into my xmlMaps.xml file there are no cases of maxOccurs and only minOccurs. Do you have any idea how to map repeating elements if there's no maxOccurs="1" to change to "unbounded"?

Thank you - That was driving me nuts

Thank you for the solution.

Thanks Dave! The post that keeps on giving!

Cheers, Lloyd

VÃ*o 03:16:01 UTC+7 Thứ Sáu, ngÃ*y 13 tháng 11 năm 2009, DaveLS đã viết:
I'm trying to map XML to a range of rows but, for some reason, Excel (2007)
is only mapping it to the first row of the range. I've created an XML file
and imported it. Then, I right-click on the root element and choose Map
Element. Excel asks "Where do you want to map the XML elements?" I click and
drag a range of contiguous cells and it updates the range in the little
dialog box. The range displayed include the full range of columns and rows so
I hit OK. But then Excel only shows the first row with the blue border. And
when I export it only exports the first row.

What am I doing wrong? I've done this before successfully with a slightly
different map and it was working fine. I can't figure out what I'm doing
wrong today.

David Salahi

Thank you

Hi David,

It sounds like you're trying to map repeating rows in Excel using XML. Here's a step-by-step guide to help you out:

1. Open your Excel file and make sure you have the [Developer] tab visible. If not, go to [File] [Options] [Customize Ribbon] and check the box next to [Developer].
2. Click on the [Developer] tab and select [XML Source] from the [XML] group.
3. In the [XML Source] pane, click on [XML Maps] and then [Add].
4. Browse to your XML file and select it. Excel will display a list of elements in the XML file.
5. Select the element you want to map and click on [OK].
6. Excel will ask you where you want to map the XML element. Click and drag a range of contiguous cells to map the element to.
7. Click on [OK] to close the [Map XML Elements] dialog box.
8. Excel will display a blue border around the first row of the mapped range. To map the remaining rows, click on the [Repeat] button in the [XML Tools] group on the [Developer] tab.
9. Excel will display a dialog box asking you how many rows you want to repeat. Enter the number of rows you want to repeat and click on [OK].
10. Excel will now display the blue border around all the rows you've mapped.
11. Save your Excel file and export it to XML.

That should do it! Let me know if you have any questions or if there's anything else I can help you with.

Best regards,
[Your Name]