is there VBA code I can use in Excel or Access, or a software app to convert the XML data file below to display the structure (tables, columns)?
XML Mapping in Excel and Access doesn't work .. it only gives me the table name and Row name ... if I do it manually, I can use the map to copy and paste . but this is a huge file and I need to do this on a regular basis .. looking for a solution
Example: action, action_row, action_definition, action_definition_row, etc.
All necessary columns are not included
If I use Power Query in Excel, since there are over 475 tables with multiple columns, it takes forever to load and you still have to do manual work to get the structure modified for visual ...
Data preview:
<?xml version="1.0" encoding="utf-8"?>
<TableList>
<action>
<action_row company_id="SYS" case_id="19525662" action_seq="1" action_type_code="close case" action_type="0 " action_status="0" action_date="9/2/2020 2:48:17 PM" action_priority="5" action_text="" issue_seq="0" letter_seq="" case_text_seq="" case_enclosure_seq="" referred_to_address_id="23293987" referred_to_user_code="trainpur01" passed_from_user_code="trainpur01" accepted_by_user_code="trainpur01" performed_by_user_code="trainpur01" response_due="9/2/2020 2:48:17 PM" responded="9/2/2020 2:48:17 PM" d05_code="" d06_code="" d07_code="09/02/2020" d08_code="" d09_code="" />
<action_row company_id="SYS" case_id="19525671" action_seq="1" action_type_code="case transfer" action_type="1 " action_status="1" action_date="9/21/2020 11:41:50 AM" action_priority="2" action_text="" issue_seq="0" letter_seq="" case_text_seq="" case_enclosure_seq="" referred_to_address_id="" referred_to_user_code="coupons" passed_from_user_code="webuser" accepted_by_user_code="webuser" performed_by_user_code="webuser" response_due="9/22/2020 11:41:50 AM" responded="" d05_code="" d06_code="" d07_code="09/21/2020" d08_code="" d09_code="" />
<action_row company_id="SYS" case_id="19525697" action_seq="1" action_type_code="case transfer" action_type="1 " action_status="1" action_date="9/23/2020 9:16:27 AM" action_priority="2" action_text="" issue_seq="0" letter_seq="" case_text_seq="" case_enclosure_seq="" referred_to_address_id="" referred_to_user_code="petfinder" passed_from_user_code="webuser" accepted_by_user_code="webuser" performed_by_user_code="webuser" response_due="9/24/2020 9:16:27 AM" responded="" d05_code="" d06_code="" d07_code="09/23/2020" d08_code="" d09_code="" />
</action>
<action_definition>
<action_definition_row company_id="JRB" action_type_code="transfer customer care" description="Send Case to Customer Care" active="Y" base_action_type="5" applies_to="1" allow_closed="1" maintain_queue_count="0" datawindow="d_case_action" case_status="C" issue_status="C" case_status_code="" issue_status_code="" text_type_code="" action_status="0" action_priority="5" delta_response_due="" referred_to_user_code="" completed_by_action_type_code="" completes_existing="0" completes_action_type_code="" completes_action_status="0" copy_from_1_category_id="" copy_to_1_category_id="" copy_from_2_category_id="" copy_to_2_category_id="" copy_from_3_category_id="" copy_to_3_category_id="" copy_from_4_category_id="" copy_to_4_category_id="" copy_from_5_category_id="" copy_to_5_category_id="" run_prep_tag="N" requires_esignature="N" ref_ctr_visible="N" order_status="0" />
</action_definition>
<address>
<address_row company_id="SYS" address_id="23293987" address_type_code="CONSUMER, CANADA" address_code="" active="Y" account_number="" name_title="" given_names="Cheez" middle_initial="" last_name="It" suffix="" company_name="" job_title="" address1="2500 Royal Windsor Dr" address2="" address3="" city="Mississauga" state="ON " postal_code="L5J 1K8" country="CAN" email="C200" search_name="I300" search_address="R400" originated_via="1" originated_date="9/2/2020 2:43:28 PM" last_modified="9/2/2020 2:43:28 PM" allow_survey="Y" last_survey="" last_contact="9/2/2020 2:43:28 PM" accumulated_goodwill="0.00" where_to_buy="" latitude="" longitude="" instructions="" a05_code="" a06_code="" a07_code="" a08_code="" a09_code="" a10_code="" a11_code="" a12_code="" a13_code="" a14_code="" a15_code="" email2="" a16_code="" a17_code="" a18_code="" a19_code="" a20_code="ON" a21_code="" a22_code="" a23_code="" a24_code="" a25_code="" repeater_code="" a26_code="" encl_auth_level="" email3="" county="CA" a27_code="" a28_code="" a29_code="" a30_code="" a31_code="" a32_code="" a33_code="" a34_code="" a35_code="" a36_code="" a37_code="" a38_code="" a39_code="" a40_code="" a41_code="" a42_code="" a43_code="" a44_code="" a45_code="" a46_code="" a47_code="" a48_code="" a49_code="" a50_code="" a51_code="" a52_code="" a53_code="" a54_code="" a55_code="" a56_code="" a57_code="" a58_code="" a59_code="" a60_code="" a61_code="" a62_code="" a63_code="" a64_code="" a65_code="" a66_code="" a67_code="" a68_code="" a69_code="" a70_code="" a71_code="" a72_code="" a73_code="" a74_code="" a75_code="" a76_code="" a77_code="" a78_code="" a79_code="" a80_code="" opt_out="" language_id="en" currency_code="" primary_address_id="" date_added="9/2/2020 2:43:28 PM" added_by_user_code="trainpur01" date_changed="9/2/2020 2:43:28 PM" changed_by_user_code="trainpur01" />
<address_row company_id="JRB" address_id="1" address_type_code="CONSUMER, DOMESTIC" address_code="" active="Y" account_number="" name_title="" given_names="Mandy" middle_initial="" last_name="Graboske" suffix="" company_name="" job_title="" address1="" address2="" address3="" city="" state="" postal_code="" country="USA" email="M530" search_name="G612" search_address="" originated_via="1" originated_date="9/18/2020 3:33:44 PM" last_modified="9/18/2020 3:33:44 PM" allow_survey="Y" last_survey="" last_contact="9/21/2020 11:36:09 AM" accumulated_goodwill="0.00" where_to_buy="" latitude="" longitude="" instructions="" a05_code="" a06_code="" a07_code="" a08_code="" a09_code="" a10_code="" a11_code="" a12_code="" a13_code="" a14_code="" a15_code="" email2="mandy.graboske@purina.nestle.com" a16_code="" a17_code="" a18_code="" a19_code="" a20_code="" a21_code="" a22_code="" a23_code="" a24_code="" a25_code="" repeater_code="" a26_code="" encl_auth_level="" email3="" county="" a27_code="" a28_code="" a29_code="" a30_code="" a31_code="" a32_code="" a33_code="" a34_code="" a35_code="" a36_code="" a37_code="" a38_code="" a39_code="" a40_code="" a41_code="" a42_code="" a43_code="" a44_code="" a45_code="" a46_code="" a47_code="" a48_code="" a49_code="" a50_code="" a51_code="" a52_code="" a53_code="" a54_code="" a55_code="" a56_code="" a57_code="" a58_code="" a59_code="" a60_code="" a61_code="" a62_code="" a63_code="" a64_code="" a65_code="" a66_code="" a67_code="" a68_code="" a69_code="" a70_code="" a71_code="" a72_code="" a73_code="" a74_code="" a75_code="" a76_code="" a77_code="" a78_code="" a79_code="" a80_code="" opt_out="" language_id="en" currency_code="" primary_address_id="" date_added="9/18/2020 3:33:44 PM" added_by_user_code="mangrab" date_changed="9/21/2020 11:57:47 AM" changed_by_user_code="mangrab" />
<address_row company_id="JRB" address_id="2" address_type_code="CONSUMER, DOMESTIC" address_code="" active="Y" account_number="" name_title="" given_names="Kim" middle_initial="" last_name="Benson" suffix="" company_name="" job_title="" address1="123 Main St" address2="" address3="" city="Chillicothe" state="OH " postal_code="45601" country="USA" email="K500" search_name="B525" search_address="M500" originated_via="1" originated_date="9/24/2020 10:25:38 AM" last_modified="9/24/2020 10:25:38 AM" allow_survey="Y" last_survey="" last_contact="9/24/2020 10:25:38 AM" accumulated_goodwill="0.00" where_to_buy="" latitude="39.335800" longitude="82.989200" instructions="" a05_code="" a06_code="" a07_code="" a08_code="" a09_code="" a10_code="" a11_code="" a12_code="" a13_code="" a14_code="" a15_code="" email2="" a16_code="" a17_code="" a18_code="" a19_code="" a20_code="OH" a21_code="" a22_code="" a23_code="" a24_code="" a25_code="" repeater_code="" a26_code="" encl_auth_level="" email3="" county="Ross" a27_code="" a28_code="" a29_code="" a30_code="" a31_code="" a32_code="" a33_code="" a34_code="" a35_code="" a36_code="" a37_code="" a38_code="" a39_code="" a40_code="" a41_code="" a42_code="" a43_code="" a44_code="" a45_code="" a46_code="" a47_code="" a48_code="" a49_code="" a50_code="" a51_code="" a52_code="" a53_code="" a54_code="" a55_code="" a56_code="" a57_code="" a58_code="" a59_code="" a60_code="" a61_code="" a62_code="" a63_code="" a64_code="" a65_code="" a66_code="" a67_code="" a68_code="" a69_code="" a70_code="" a71_code="" a72_code="" a73_code="" a74_code="" a75_code="" a76_code="" a77_code="" a78_code="456" a79_code="45601" a80_code="" opt_out="" language_id="en" currency_code="" primary_address_id="" date_added="9/24/2020 10:25:38 AM" added_by_user_code="astuteadmin" date_changed="9/24/2020 10:25:38 AM" changed_by_user_code="astuteadmin" />
</address>
<address_phone>
<address_phone_row company_id="SYS" address_id="23293987" address_phone_seq="1" phone_type_code="HOME" phone="" phone_note="" />
<address_phone_row company_id="SYS" address_id="23293987" address_phone_seq="2" phone_type_code="WORK" phone="" phone_note="" />
<address_phone_row company_id="JRB" address_id="2" address_phone_seq="1" phone_type_code="CELL" phone="740-775-2078" phone_note="" />
<address_phone_row company_id="JRB" address_id="1" address_phone_seq="1" phone_type_code="CELL" phone="" phone_note="" />
</address_phone>
<associated_address />
Thank you.
XML Mapping in Excel and Access doesn't work .. it only gives me the table name and Row name ... if I do it manually, I can use the map to copy and paste . but this is a huge file and I need to do this on a regular basis .. looking for a solution
Example: action, action_row, action_definition, action_definition_row, etc.
All necessary columns are not included
If I use Power Query in Excel, since there are over 475 tables with multiple columns, it takes forever to load and you still have to do manual work to get the structure modified for visual ...
Data preview:
<?xml version="1.0" encoding="utf-8"?>
<TableList>
<action>
<action_row company_id="SYS" case_id="19525662" action_seq="1" action_type_code="close case" action_type="0 " action_status="0" action_date="9/2/2020 2:48:17 PM" action_priority="5" action_text="" issue_seq="0" letter_seq="" case_text_seq="" case_enclosure_seq="" referred_to_address_id="23293987" referred_to_user_code="trainpur01" passed_from_user_code="trainpur01" accepted_by_user_code="trainpur01" performed_by_user_code="trainpur01" response_due="9/2/2020 2:48:17 PM" responded="9/2/2020 2:48:17 PM" d05_code="" d06_code="" d07_code="09/02/2020" d08_code="" d09_code="" />
<action_row company_id="SYS" case_id="19525671" action_seq="1" action_type_code="case transfer" action_type="1 " action_status="1" action_date="9/21/2020 11:41:50 AM" action_priority="2" action_text="" issue_seq="0" letter_seq="" case_text_seq="" case_enclosure_seq="" referred_to_address_id="" referred_to_user_code="coupons" passed_from_user_code="webuser" accepted_by_user_code="webuser" performed_by_user_code="webuser" response_due="9/22/2020 11:41:50 AM" responded="" d05_code="" d06_code="" d07_code="09/21/2020" d08_code="" d09_code="" />
<action_row company_id="SYS" case_id="19525697" action_seq="1" action_type_code="case transfer" action_type="1 " action_status="1" action_date="9/23/2020 9:16:27 AM" action_priority="2" action_text="" issue_seq="0" letter_seq="" case_text_seq="" case_enclosure_seq="" referred_to_address_id="" referred_to_user_code="petfinder" passed_from_user_code="webuser" accepted_by_user_code="webuser" performed_by_user_code="webuser" response_due="9/24/2020 9:16:27 AM" responded="" d05_code="" d06_code="" d07_code="09/23/2020" d08_code="" d09_code="" />
</action>
<action_definition>
<action_definition_row company_id="JRB" action_type_code="transfer customer care" description="Send Case to Customer Care" active="Y" base_action_type="5" applies_to="1" allow_closed="1" maintain_queue_count="0" datawindow="d_case_action" case_status="C" issue_status="C" case_status_code="" issue_status_code="" text_type_code="" action_status="0" action_priority="5" delta_response_due="" referred_to_user_code="" completed_by_action_type_code="" completes_existing="0" completes_action_type_code="" completes_action_status="0" copy_from_1_category_id="" copy_to_1_category_id="" copy_from_2_category_id="" copy_to_2_category_id="" copy_from_3_category_id="" copy_to_3_category_id="" copy_from_4_category_id="" copy_to_4_category_id="" copy_from_5_category_id="" copy_to_5_category_id="" run_prep_tag="N" requires_esignature="N" ref_ctr_visible="N" order_status="0" />
</action_definition>
<address>
<address_row company_id="SYS" address_id="23293987" address_type_code="CONSUMER, CANADA" address_code="" active="Y" account_number="" name_title="" given_names="Cheez" middle_initial="" last_name="It" suffix="" company_name="" job_title="" address1="2500 Royal Windsor Dr" address2="" address3="" city="Mississauga" state="ON " postal_code="L5J 1K8" country="CAN" email="C200" search_name="I300" search_address="R400" originated_via="1" originated_date="9/2/2020 2:43:28 PM" last_modified="9/2/2020 2:43:28 PM" allow_survey="Y" last_survey="" last_contact="9/2/2020 2:43:28 PM" accumulated_goodwill="0.00" where_to_buy="" latitude="" longitude="" instructions="" a05_code="" a06_code="" a07_code="" a08_code="" a09_code="" a10_code="" a11_code="" a12_code="" a13_code="" a14_code="" a15_code="" email2="" a16_code="" a17_code="" a18_code="" a19_code="" a20_code="ON" a21_code="" a22_code="" a23_code="" a24_code="" a25_code="" repeater_code="" a26_code="" encl_auth_level="" email3="" county="CA" a27_code="" a28_code="" a29_code="" a30_code="" a31_code="" a32_code="" a33_code="" a34_code="" a35_code="" a36_code="" a37_code="" a38_code="" a39_code="" a40_code="" a41_code="" a42_code="" a43_code="" a44_code="" a45_code="" a46_code="" a47_code="" a48_code="" a49_code="" a50_code="" a51_code="" a52_code="" a53_code="" a54_code="" a55_code="" a56_code="" a57_code="" a58_code="" a59_code="" a60_code="" a61_code="" a62_code="" a63_code="" a64_code="" a65_code="" a66_code="" a67_code="" a68_code="" a69_code="" a70_code="" a71_code="" a72_code="" a73_code="" a74_code="" a75_code="" a76_code="" a77_code="" a78_code="" a79_code="" a80_code="" opt_out="" language_id="en" currency_code="" primary_address_id="" date_added="9/2/2020 2:43:28 PM" added_by_user_code="trainpur01" date_changed="9/2/2020 2:43:28 PM" changed_by_user_code="trainpur01" />
<address_row company_id="JRB" address_id="1" address_type_code="CONSUMER, DOMESTIC" address_code="" active="Y" account_number="" name_title="" given_names="Mandy" middle_initial="" last_name="Graboske" suffix="" company_name="" job_title="" address1="" address2="" address3="" city="" state="" postal_code="" country="USA" email="M530" search_name="G612" search_address="" originated_via="1" originated_date="9/18/2020 3:33:44 PM" last_modified="9/18/2020 3:33:44 PM" allow_survey="Y" last_survey="" last_contact="9/21/2020 11:36:09 AM" accumulated_goodwill="0.00" where_to_buy="" latitude="" longitude="" instructions="" a05_code="" a06_code="" a07_code="" a08_code="" a09_code="" a10_code="" a11_code="" a12_code="" a13_code="" a14_code="" a15_code="" email2="mandy.graboske@purina.nestle.com" a16_code="" a17_code="" a18_code="" a19_code="" a20_code="" a21_code="" a22_code="" a23_code="" a24_code="" a25_code="" repeater_code="" a26_code="" encl_auth_level="" email3="" county="" a27_code="" a28_code="" a29_code="" a30_code="" a31_code="" a32_code="" a33_code="" a34_code="" a35_code="" a36_code="" a37_code="" a38_code="" a39_code="" a40_code="" a41_code="" a42_code="" a43_code="" a44_code="" a45_code="" a46_code="" a47_code="" a48_code="" a49_code="" a50_code="" a51_code="" a52_code="" a53_code="" a54_code="" a55_code="" a56_code="" a57_code="" a58_code="" a59_code="" a60_code="" a61_code="" a62_code="" a63_code="" a64_code="" a65_code="" a66_code="" a67_code="" a68_code="" a69_code="" a70_code="" a71_code="" a72_code="" a73_code="" a74_code="" a75_code="" a76_code="" a77_code="" a78_code="" a79_code="" a80_code="" opt_out="" language_id="en" currency_code="" primary_address_id="" date_added="9/18/2020 3:33:44 PM" added_by_user_code="mangrab" date_changed="9/21/2020 11:57:47 AM" changed_by_user_code="mangrab" />
<address_row company_id="JRB" address_id="2" address_type_code="CONSUMER, DOMESTIC" address_code="" active="Y" account_number="" name_title="" given_names="Kim" middle_initial="" last_name="Benson" suffix="" company_name="" job_title="" address1="123 Main St" address2="" address3="" city="Chillicothe" state="OH " postal_code="45601" country="USA" email="K500" search_name="B525" search_address="M500" originated_via="1" originated_date="9/24/2020 10:25:38 AM" last_modified="9/24/2020 10:25:38 AM" allow_survey="Y" last_survey="" last_contact="9/24/2020 10:25:38 AM" accumulated_goodwill="0.00" where_to_buy="" latitude="39.335800" longitude="82.989200" instructions="" a05_code="" a06_code="" a07_code="" a08_code="" a09_code="" a10_code="" a11_code="" a12_code="" a13_code="" a14_code="" a15_code="" email2="" a16_code="" a17_code="" a18_code="" a19_code="" a20_code="OH" a21_code="" a22_code="" a23_code="" a24_code="" a25_code="" repeater_code="" a26_code="" encl_auth_level="" email3="" county="Ross" a27_code="" a28_code="" a29_code="" a30_code="" a31_code="" a32_code="" a33_code="" a34_code="" a35_code="" a36_code="" a37_code="" a38_code="" a39_code="" a40_code="" a41_code="" a42_code="" a43_code="" a44_code="" a45_code="" a46_code="" a47_code="" a48_code="" a49_code="" a50_code="" a51_code="" a52_code="" a53_code="" a54_code="" a55_code="" a56_code="" a57_code="" a58_code="" a59_code="" a60_code="" a61_code="" a62_code="" a63_code="" a64_code="" a65_code="" a66_code="" a67_code="" a68_code="" a69_code="" a70_code="" a71_code="" a72_code="" a73_code="" a74_code="" a75_code="" a76_code="" a77_code="" a78_code="456" a79_code="45601" a80_code="" opt_out="" language_id="en" currency_code="" primary_address_id="" date_added="9/24/2020 10:25:38 AM" added_by_user_code="astuteadmin" date_changed="9/24/2020 10:25:38 AM" changed_by_user_code="astuteadmin" />
</address>
<address_phone>
<address_phone_row company_id="SYS" address_id="23293987" address_phone_seq="1" phone_type_code="HOME" phone="" phone_note="" />
<address_phone_row company_id="SYS" address_id="23293987" address_phone_seq="2" phone_type_code="WORK" phone="" phone_note="" />
<address_phone_row company_id="JRB" address_id="2" address_phone_seq="1" phone_type_code="CELL" phone="740-775-2078" phone_note="" />
<address_phone_row company_id="JRB" address_id="1" address_phone_seq="1" phone_type_code="CELL" phone="" phone_note="" />
</address_phone>
<associated_address />
Thank you.
