Read through XML file

R

rjtaylor

New Member
Joined
Jan 27, 2004
Messages
36
I have the following XML file I get from a XMLHttpRequest.responseText
the other examples I have seen dont seem to work on this layout. here is a portion of the xml file (XMLHttpRequest.responseText)
VBA Code: 
<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
     xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
     xmlns:rs='urn:schemas-microsoft-com:rowset'
     xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
   <s:ElementType name='row' content='eltOnly' rs:CommandTimeout='30'>
      <s:AttributeType name='ows_ID' rs:name='ID' rs:number='1'>
         <s:datatype dt:type='i4' dt:maxLength='4' />
      </s:AttributeType>
      <s:AttributeType name='ows_Tracking_x0020_No_x0020_New' rs:name='Tracking No New' rs:number='2'>
         <s:datatype dt:type='string' dt:maxLength='512' />
      </s:AttributeType>
      <s:AttributeType name='ows_Auto_ID' rs:name='Tracking No' rs:number='3'>
         <s:datatype dt:type='string' dt:maxLength='512' />
      </s:AttributeType>
      <s:AttributeType name='ows_Title' rs:name='Description' rs:number='4'>
         <s:datatype dt:type='string' dt:maxLength='512' />
      </s:AttributeType>
      <s:AttributeType name='ows_LinkTitleNoMenu' rs:name='Description' rs:number='5'>
         <s:datatype dt:type='string' dt:maxLength='512' />
      </s:AttributeType>
   </s:ElementType>
</s:Schema>
<rs:data>
   <z:row ows_ID='11' ows_Tracking_x0020_No_x0020_New='4506-ORDER' ows_Auto_ID='72-Order-2/11/2015' ows_Title='Order 7110.66 National Beacon Code Allocation Plan (NBCAP)' ows_LinkTitleNoMenu='Order 7110.66 National Beacon Code Allocation Plan (NBCAP)' />
   <z:row ows_ID='15' ows_Tracking_x0020_No_x0020_New='4670-ORDER' ows_Auto_ID='22-Order-2/11/2015' ows_Title='Order 1600.2F- Classified National Security Information' ows_LinkTitleNoMenu='Order 1600.2F- Classified National Security Information' />
   <z:row ows_ID='16' ows_Tracking_x0020_No_x0020_New='4543-ORDER' ows_Auto_ID='44-Order-2/11/2015' ows_Title='Order 8260.46 Departure Procedure (DP) Program' ows_LinkTitleNoMenu='Order 8260.46 Departure Procedure (DP) Program' />
   <z:row ows_ID='17' ows_Auto_ID='23-Order-2/11/2015' ows_Title='Order 8020.16 ATO Aircraft Accident and Incident Investigation' ows_LinkTitleNoMenu='Order 8020.16 ATO Aircraft Accident and Incident Investigation' />
   <z:row ows_ID='18' ows_Auto_ID='75-Order-2/11/2015' ows_Title='Order 9550.7 Aviation Research Grants Program' ows_LinkTitleNoMenu='Order 9550.7 Aviation Research Grants Program' />
   <z:row ows_ID='19' ows_Tracking_x0020_No_x0020_New='84-DCP' ows_Auto_ID='24-DCP-2/13/2015' ows_Title='DCP 7110.65 2-3-8 Aircraft Equipment Suffix' ows_LinkTitleNoMenu='DCP 7110.65 2-3-8 Aircraft Equipment Suffix' />
   <z:row ows_ID='20' ows_Tracking_x0020_No_x0020_New='4666-MEMO' ows_Auto_ID='33-Memo-2/11/2015' ows_Title='Memo Tower Data Link System (TDLS)' ows_LinkTitleNoMenu='Memo Tower Data Link System (TDLS)' />
   <z:row ows_ID='21' ows_Auto_ID='25-DCP-2/11/2015' ows_Title='DCP 7210.3 2-6-7 Basic Watch' ows_LinkTitleNoMenu='DCP 7210.3 2-6-7 Basic Watch' />
   <z:row ows_ID='22' ows_Auto_ID='5-DCP-2/11/2015' ows_Title='DCP 7110.65 2-1-14, 5-4-5, 5-4-6 Coordinate Use of Airspace, Transferring Controller, Receiving Controller Responsiblities' ows_LinkTitleNoMenu='DCP 7110.65 2-1-14, 5-4-5, 5-4-6 Coordinate Use of Airspace, Transferring Controller, Receiving Controller Responsiblities' />
   <z:row ows_ID='24' ows_Tracking_x0020_No_x0020_New='4539-NOTICE' ows_Auto_ID='35-Notice-2/11/2015' ows_Title='Notice 3120.XX Traffic Management Coordinator Qualification Training' ows_LinkTitleNoMenu='Notice 3120.XX Traffic Management Coordinator Qualification Training' />
   <z:row ows_ID='26' ows_Tracking_x0020_No_x0020_New='4540-ORDER' ows_Auto_ID='34-Order-2/11/2015' ows_Title='Order 1050.16A Implementation Guidelines for Storage Tank Systems' ows_LinkTitleNoMenu='Order 1050.16A Implementation Guidelines for Storage Tank Systems' />
   <z:row ows_ID='28' ows_Tracking_x0020_No_x0020_New='4669-MEMO' ows_Auto_ID='2-Memo-2/11/2015' ows_Title='Memo Waiver 11-E-01A TMC Currency Cancelled' ows_LinkTitleNoMenu='Memo Waiver 11-E-01A TMC Currency Cancelled' />
   <z:row ows_ID='29' ows_Auto_ID='4-DCP-2/11/2015' ows_Title='DCP 7110.10 9-7-1 Alaska 3 Airmet Table' ows_LinkTitleNoMenu='DCP 7110.10 9-7-1 Alaska 3 Airmet Table' />
   <z:row ows_ID='32' ows_Auto_ID='8-DCP-2/10/2015' ows_Title='DCP 7210.3 6-3-1, 17-2-4, 17-4-4 Center Weather Service Unit - Cancels Order 7210.38' ows_LinkTitleNoMenu='DCP 7210.3 6-3-1, 17-2-4, 17-4-4 Center Weather Service Unit - Cancels Order 7210.38' />
   <z:row ows_ID='33' ows_Auto_ID='9-DCP-2/11/2015' ows_Title='DCP 7210.3 2-6-1 Watch Supervision (Top 5)' ows_LinkTitleNoMenu='DCP 7210.3 2-6-1 Watch Supervision (Top 5)' />
   <z:row ows_ID='34' ows_Auto_ID='10-DCP-2/11/2015' ows_Title='DCP 7210.3 2-2-4 Duty Familiarization and the Transfer of Position Responsibility' ows_LinkTitleNoMenu='DCP 7210.3 2-2-4 Duty Familiarization and the Transfer of Position Responsibility' />
   <z:row ows_ID='36' ows_Auto_ID='13-Order-2/11/2015' ows_Title='Order 1050.1 Environmental Impacts: Policies and Procedures' ows_LinkTitleNoMenu='Order 1050.1 Environmental Impacts: Policies and Procedures' />
   <z:row ows_ID='37' ows_Tracking_x0020_No_x0020_New='4499-DCP' ows_Auto_ID='14-DCP-2/11/2015' ows_Title='Waiver Renewal 12-T-09A LAS 7110.65 3-9-8b1&amp;2 Intersecting Runways ADW' ows_LinkTitleNoMenu='Waiver Renewal 12-T-09A LAS 7110.65 3-9-8b1&amp;2 Intersecting Runways ADW' />
   <z:row ows_ID='38' ows_Auto_ID='15-DCP-2/11/2015' ows_Title='Waiver Renewal 11-T-16A SEA/BFI/S46 7110.65 7-2-1a Visual Separation' ows_LinkTitleNoMenu='Waiver Renewal 11-T-16A SEA/BFI/S46 7110.65 7-2-1a Visual Separation' />
   <z:row ows_ID='48' ows_Auto_ID='29-DCP-2/11/2015' ows_Title='Waiver Renewal 98-T-60H CLT 7110.65 5-8-3c Successive or Simultaneous Departures' ows_LinkTitleNoMenu='Waiver Renewal 98-T-60H CLT 7110.65 5-8-3c Successive or Simultaneous Departures' />
   <z:row ows_ID='50' ows_Auto_ID='31-DCP-2/11/2015' ows_Title='Waiver Renewal 99-T-47G PHL 7110.65 5-8-5b1 Instrument Landing System Approaches' ows_LinkTitleNoMenu='Waiver Renewal 99-T-47G PHL 7110.65 5-8-5b1 Instrument Landing System Approaches' />
   <z:row ows_ID='52' ows_Auto_ID='36-Notice-2/11/2015' ows_Title='Notice 7110.65 3-4-20 Runway Status Lights &amp; 7210.3 10-6-10 Runway Status Lights' ows_LinkTitleNoMenu='Notice 7110.65 3-4-20 Runway Status Lights &amp; 7210.3 10-6-10 Runway Status Lights' />
   <z:row ows_ID='54' ows_Tracking_x0020_No_x0020_New='99-' ows_Auto_ID='38-Order-2/11/2015' ows_Title='Order 7110.118A Land and Hold Short Operations (LAHSO)' ows_LinkTitleNoMenu='Order 7110.118A Land and Hold Short Operations (LAHSO)' />
   <z:row ows_ID='55' ows_Auto_ID='39-DCP-2/11/2015' ows_Title='Waiver Request AWO 7110.50B Recurring Air Show or Fly-In' ows_LinkTitleNoMenu='Waiver Request AWO 7110.50B Recurring Air Show or Fly-In' />
   <z:row ows_ID='56' ows_Auto_ID='40-Notice-2/11/2015' ows_Title='Notice 7110.663 4-5-7 Altitude Information (Time to Climb)' ows_LinkTitleNoMenu='Notice 7110.663 4-5-7 Altitude Information (Time to Climb)' />
   <z:row ows_ID='57' ows_Auto_ID='41-Notice-2/11/2015' ows_Title='Notice 7110.XXX 3-9-9 Non-Intersecting Converging Runway Operations (MIA)' ows_LinkTitleNoMenu='Notice 7110.XXX 3-9-9 Non-Intersecting Converging Runway Operations (MIA)' />
   <z:row ows_ID='58' ows_Auto_ID='42-Notice-2/11/2015' ows_Title='Notice 7110.xxx 3-9-9 Non-Intersecting Converging Runway Operations (HNL)' ows_LinkTitleNoMenu='Notice 7110.xxx 3-9-9 Non-Intersecting Converging Runway Operations (HNL)' />
   <z:row ows_ID='59' ows_Auto_ID='43-SRMDM-2/9/2015' ows_Title='SRMDM 1117- Muted HERT, 1110 - Beacon Code Display to Controllers, 1089 - SSD Rev F &amp; Subsystem Allocation in Doors, 1518 - Multiple Adapted Routings and Referred Messages, 1487 - ADS-B AT Specialist Alert Changes' ows_LinkTitleNoMenu='SRMDM 1117- Muted HERT, 1110 - Beacon Code Display to Controllers, 1089 - SSD Rev F &amp; Subsystem Allocation in Doors, 1518 - Multiple Adapted Routings and Referred Messages, 1487 - ADS-B AT Specialist Alert Changes' />
   <z:row ows_ID='60' ows_Tracking_x0020_No_x0020_New='34-' ows_Auto_ID='45-Order-2/12/2015' ows_Title='Order 7230.8 LIMITED AVIATION WEATHER REPORTING STATIONS (LAWRS)' ows_LinkTitleNoMenu='Order 7230.8 LIMITED AVIATION WEATHER REPORTING STATIONS (LAWRS)' />
   <z:row ows_ID='61' ows_Tracking_x0020_No_x0020_New='127-NOTICE-4/7/2015' ows_Auto_ID='46-Notice-2/11/2015' ows_Title='Notice 7210.879 Interim Guidance in the Preparation of FAA Form 7230-4' ows_LinkTitleNoMenu='Notice 7210.879 Interim Guidance in the Preparation of FAA Form 7230-4' />
   <z:row ows_ID='62' ows_Auto_ID='47-DCP-2/11/2015' ows_Title='DCP 7110.65 7-5-3 Separation' ows_LinkTitleNoMenu='DCP 7110.65 7-5-3 Separation' />
   <z:row ows_ID='63' ows_Auto_ID='48-DCP-2/11/2015' ows_Title='DCP AIM 4-1-16 Safety Alerts ' ows_LinkTitleNoMenu='DCP AIM 4-1-16 Safety Alerts ' />
   <z:row ows_ID='64' ows_Tracking_x0020_No_x0020_New='4491-DCP' ows_Auto_ID='49-DCP-2/11/2015' ows_Title='Waiver Renewal 89-T-15H LAX 7110.65 5-8-3c Successive or Simul Depts' ows_LinkTitleNoMenu='Waiver Renewal 89-T-15H LAX 7110.65 5-8-3c Successive or Simul Depts' />
   <z:row ows_ID='65' ows_Auto_ID='50-DCP-2/11/2015' ows_Title='Waiver Renewal 04-T-16E DFW &amp; D10 7110.65 5-8-3 Successive Sumultaneous Departures' ows_LinkTitleNoMenu='Waiver Renewal 04-T-16E DFW &amp; D10 7110.65 5-8-3 Successive Sumultaneous Departures' />
   <z:row ows_ID='66' ows_Tracking_x0020_No_x0020_New='4488-DCP' ows_Auto_ID='51-DCP-2/11/2015' ows_Title='Waiver Renewal 92-T-22H MCO-F11 7110.65 5-9-6a3 Parallel Dependent ILS Approach' ows_LinkTitleNoMenu='Waiver Renewal 92-T-22H MCO-F11 7110.65 5-9-6a3 Parallel Dependent ILS Approach' />
     <z:row ows_ID='16244' ows_Tracking_x0020_No_x0020_New='4986-EXTERNAL CORRESPONDENCE REVIEW (ECR)' ows_Title='ECR Revised Draft JO 3000.22C Air Traffic Organization Technical Training Management ' ows_LinkTitleNoMenu='ECR Revised Draft JO 3000.22C Air Traffic Organization Technical Training Management ' />
</rs:data>
</xml>
Can anyone help me work through this Data. What I am trying to get is each line ows_ID and ows_Tracking_x0020_No_x0020_New and maybe the ows_Auto_ID, as well
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
For thoes interested I figured out the answer
VBA Code: 
Sub GetDataFromXML()
Dim http As New XMLHTTP60, myUrl As String
Dim doc As MSXML2.DOMDocument60, xml As String, els, el
        
        myUrl = "https://ksn2.faa.gov/ajv/home/AJV-8/_vti_bin/owssvr.dll?XMLDATA=1&List={3C03F056-CA4D-49E4-AF32-E87160118098}&View={9F3DD759-27BE-480E-859E-A8328E13AE80}&RowLimit=0&RootFolder=%2fajv%2fhome%2fAJV%2d8%2fLists%2fCorrespondence%20Tracker"
        With http
            .Open "POST", myUrl, False
            .send
            xml = .responseText
        End With
        
        Set doc = New MSXML2.DOMDocument60
        
        'You must add any namespace(s) for any desired elements
        'Watch the formatting...
        doc.SetProperty "SelectionNamespaces", _
            "xmlns:ms='http://mapserver.gis.umn.edu/mapserver' " & _
            "xmlns:wfs='http://www.opengis.net/wfs' " & _
            "xmlns:gml='http://www.opengis.net/gml' " & _
            "xmlns:ogc='http://www.opengis.net/ogc'"
        
        doc.LoadXML xml
        
'        Set els = doc.SelectNodes("//ms:OWNERSHIP")
        Set els = doc.SelectNodes("//*")
        For Each el In els
            If el.nodeName = "z:row" Then
                Set atb = el.Attributes
                a = atb.Item(0).Text
                b = atb.Item(1).Text
                c = atb.Item(2).Text
               Debug.Print a & " --- " & b & " --- " & c
'                MsgBox atb.Item(0).Text & vbCrLf & atb.Item(1).Text & vbCrLf & atb.Item(2).Text
            End If
        Next el
        
Set doc = Nothing
Set els = Nothing
Set el = Nothing
Set atb = Nothing

End Sub
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

PuntingJawa
  • Solved
If column B has # then pull data from column A beside it
Replies
4
Views
223
PuntingJawa
PuntingJawa
E
  • Solved
Ranking Invoices By Highest Amount By "Year to Date"
Replies
2
Views
142
ellison
E
D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
283
etaf
etaf
D
  • Question Question
Compile error: Wrong number of arguments or invalid property assignment
Replies
2
Views
71
daveyc18
D
J
  • Solved
Conditionally sum multiple values
Replies
5
Views
242
Fluff
Fluff

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top