2

I am looking to delete a specific part of the xml file then import to a new table in access. Everything works but there is a node in the xml that is stopping this being imported.

I am looking to delete the node.

<?xml version="1.0" encoding="UTF-8"?>
<spark>
    <__status>success</__status>
    <active>true</active>
    <activity_due/>
    <approval>Not Yet Requested</approval>
    <approval_history/>
    <approval_set/>
    <assigned_to>Andrew Mansell</assigned_to>
    <assignment_group>Reference Data Support</assignment_group>
    <business_duration/>
    <calendar_duration/>
    <calendar_stc/>
    <close_notes/>
    <closed_at/>
    <closed_by/>
    <cmdb_ci/>
    <comments/>
    <comments_and_work_notes/>
    <company/>
    <contact_type/>
    <correlation_display/>
    <correlation_id/>
    <delivery_plan/>
    <delivery_task/>
    <description>Variables:&#x0D;
&#x0D;
Products: - Not Applicable&#x0D;
Business Owner: - Craig Hartnett&#x0D;
Full Details/Special Instructions: - Please find attached work order&#x0D;
Reason Codes: - Not Applicable&#x0D;
Business Area: - TVPROD Retention &#x0D;
Priority: - Low&#x0D;
Retailers: - Not Applicable&#x0D;
Due by Date: - 14/10/2016 13:12:14&#x0D;
Correspondence: - Not Applicable&#x0D;
Rules: - Not Applicable&#x0D;
Title: - TVPROD29572 HD PACK TEST WEEK 16-17&#x0D;
Offers: - Not Applicable</description>
    <due_date/>
    <escalation>Normal</escalation>
    <expected_start/>
    <follow_up/>
    <group_list/>
    <impact/>
    <knowledge>false</knowledge>
    <location/>
    <made_sla>true</made_sla>
    <number>RQTASK1381986</number>
    <opened_at>2016-10-10 12:12:30</opened_at>
    <opened_by/>
    <order/>
    <parent>REQ0739816</parent>
    <priority/>
    <reassignment_count>0</reassignment_count>
    <request>REQ0739816</request>
    <request_item/>
    <sc_catalog/>
    <short_description>TVPROD29572 HD PACK TEST WEEK 16-17</short_description>
    <skills/>
    <sla_due/>
    <state>Work in Progress</state>
    <sys_class_name>Request Task</sys_class_name>
    <sys_created_by>system</sys_created_by>
    <sys_created_on>2016-10-10 12:12:30</sys_created_on>
    <sys_domain>global</sys_domain>
    <sys_id>64a86bfa375eea40eea40c5754990e80</sys_id>
    <sys_mod_count>1</sys_mod_count>
    <sys_tags/>
    <sys_updated_by>cal25</sys_updated_by>
    <sys_updated_on>2016-10-14 11:47:57</sys_updated_on>
    <time_worked/>
    <u_affected_ci_list/>
    <u_approval_group/>
    <u_ass_to_man>true</u_ass_to_man>
    <u_attachment_key/>
    <u_causal_ci_list/>
    <u_customer_satisfaction_comments/>
    <u_customer_satisfaction_result/>
    <u_dep_sys_mod>false</u_dep_sys_mod>
    <u_department/>
    <u_div_sys_mod>false</u_div_sys_mod>
    <u_division/>
    <u_enhancement/>
    <u_enters_on_hold_status/>
    <u_expected_delivery_time_durat>2016-10-15 12:12:30</u_expected_delivery_time_durat>
    <u_impacted_areas/>
    <u_last_work_note>Craig Allan : template done sent for andy to approval</u_last_work_note>
    <u_last_work_note_date_and_time>2016-10-14 11:47:57</u_last_work_note_date_and_time>
    <u_leaves_on___hold_status/>
    <u_nsr/>
    <u_on_hold_pending_time/>
    <u_outage_type>No Service Impact</u_outage_type>
    <u_owner/>
    <u_owner_group/>
    <u_related_release/>
    <u_req_type/>
    <u_requested_for>Kavita Nanda</u_requested_for>
    <u_requested_for_date>2016-10-14</u_requested_for_date>
    <u_ritm/>
    <u_scheduled_end_date/>
    <u_scheduled_end_duration/>
    <u_scheduled_start_date/>
    <u_start_time_duration/>
    <u_sub_status_/>
    <u_sys_ass>false</u_sys_ass>
    <u_task_priority/>
    <u_task_status>Work in Progress</u_task_status>
    <u_time_to_complete/>
    <u_updated>false</u_updated>
    <u_updated_by_user/>
    <u_user_unavailable/>
    <u_vendor>false</u_vendor>
    <upon_approval>Proceed to Next Task</upon_approval>
    <upon_reject>Cancel all future Tasks</upon_reject>
    <urgency/>
    <user_input/>
    <watch_list/>
    <work_end/>
    <work_notes/>
    <work_notes_list/>
    <work_start/>
</spark>

The part I need to delete is;

<description>Variables:&#x0D;
    &#x0D;
    Products: - Not Applicable&#x0D;
    Business Owner: - Craig Hartnett&#x0D;
    Full Details/Special Instructions: - Please find attached work order&#x0D;
    Reason Codes: - Not Applicable&#x0D;
    Business Area: - TVPROD Retention &#x0D;
    Priority: - Low&#x0D;
    Retailers: - Not Applicable&#x0D;
    Due by Date: - 14/10/2016 13:12:14&#x0D;
    Correspondence: - Not Applicable&#x0D;
    Rules: - Not Applicable&#x0D;
    Title: - TVPROD29572 HD PACK TEST WEEK 16-17&#x0D;
    Offers: - Not Applicable</description>

My VBA code so far is;

Private Sub Command2_Click()

Dim xmlhttp As New MSXML2.ServerXMLHTTP60, myurl As String, MyOutput As String

myurl = "http://10.74.18.2:8000/service=46&ticket_number=RQTASK1381986&xml=true"
xmlhttp.Open "GET", myurl, False
xmlhttp.send

  MyOutput = Mid(xmlhttp.responseText, 5)


Open "C:/Users/los07/Documents/test.xml" For Output As #1
Print #1, MyOutput

Application.ImportXML "C:/Users/los07/Documents/test.xml", acStructureAndData


End Sub

how can I select and delete the specific part?

thanks.

1 Answer 1

2

You can create an XSL template file named "dropDescription.xslt"

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output indent="yes"/>
    <xsl:strip-space elements="*"/>

    <xsl:template match="@*|node()">
        <xsl:copy>
            <xsl:apply-templates select="@*|node()"/>
        </xsl:copy>
    </xsl:template>

    <xsl:template match="description">
        <!-- omit this item -->
    </xsl:template>

</xsl:stylesheet>

and then have your VBA code call Application.TransformXML to pre-process the XML before importing it:

Application.TransformXML _
        "C:\Users\Gord\Desktop\test.xml", _
        "C:\Users\Gord\Desktop\dropDescription.xslt", _
        "C:\Users\Gord\Desktop\transformed.xml"
Application.ImportXML "C:\Users\Gord\Desktop\transformed.xml", acStructureAndData
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.