none
XML and Excel RRS feed

  • Question

  • I am trying to provide users with an excel template that will allow them to save as .xml and upload orders to one of our systems. There are a couple of levels required in these xml files. First there's a batch, which can have multiple orders. Within the orders there can be multiple lines.

    I've figured out how to have multiple orders in a batch, but can not figure out how to get a varying number of lines per order - I can only seem to do this with one line orders. Here's how some sample order data looks (forgive this formatting - I had a screen shot but can't include it until my account is verified, however long that takes):

    BatchID BatchDate BillToCustomer ShipToCustomer PONumber Type Item Quantity Price

    TEST 9/2/16 1234 5678 ORDER1 C ITEM1 1 14.00

    9012 3456 ORDER2 P ITEM2 2 20.00

    ITEM3 5 5.00


    In this case, the second row (just below header row) is a single order. The third and fourth rows represent a second order.

    I can't verify the XML map for export because I get a message that says it's not exportable because it contains either a list of lists, denormalized data or a mapped element's relationship with other elements cannot be preserved.

    Is there a way for me to export to xml with a varying number of rows representing orders? I feel like this should be possible but have limited knowledge of xml, so any assistance would be appreciated!

    Friday, September 2, 2016 3:09 PM

Answers

  • >>because it contains either a list of lists, denormalized data or a mapped element's relationship with other elements cannot be preserved.

     

    How do you define your xsd schema? 

    Could you please share the excepted XML code, since we couldn’t  figure out how the XML maps would be based on your post?

     

    Besides, according to Issue: Verifying an XML map for export,

    Additionally, the contents of an XML mapping cannot be exported if the contents contain one of the following XML schema constructs:

    List of lists     One list of items contains a second list of items.

    Denormalized data     An XML table contains an element that has been defined in the schema to occur once (the maxoccurs attribute is set to 1). When you add such an element to an XML table, the table column is filled with multiple instances of the element.

    Choice     This is a mapped element that is part of a <choice> schema construct.

     

    If there are "List of lists" items, we are unable to export the XML map, this is the limitation for Excel, to work around, I would suggest you modify your schema.    

    Please have a look at this thread and the last post from Andrius.

    Monday, September 5, 2016 8:19 AM
    Moderator