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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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

S
  • Solved
Get table name on worksheet using formulas without using VBA
2
Replies
11
Views
494
6StringJazzer
6StringJazzer
A
  • Solved
Grabbing the latest data from a Column based on a cell
Replies
4
Views
262
PeteWright
PeteWright
J
  • Solved
Help with calculation between work days based on condition
Replies
7
Views
262
Fluff
Fluff
S
  • Solved
VBA Code Help
Replies
4
Views
283
Peter_SSs
Peter_SSs
J
  • Solved
IFS
Replies
4
Views
102
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,073
Messages
6,122,975
Members
449,095
Latest member
Mr Hughes

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