XML File
<SectorL1 xmlns:fo="http://www.w3.org/1999/XSL/Format"><label>Corporates</label>
<cmsID>1001</cmsID>
<order/>
<referenceListStatus>1</referenceListStatus>
<owlCode>Corporates</owlCode>
<frameOfReference>C</frameOfReference>
<SectorL2>
<label>Aerospace/Defense</label>
<cmsID>1002</cmsID>
<order/>
<referenceListStatus>1</referenceListStatus>
<owlCode>Aerospace_Defense</owlCode>
<SectorL3>
<label>Aircraft & Aerospace Equipment</label>
<cmsID>1003</cmsID>
<order/>
<referenceListStatus>1</referenceListStatus>
<owlCode>AnA_Equipment_ind</owlCode>
<SectorCodes>
<brd_busn_ln_cd>136885</brd_busn_ln_cd>
<spec_busn_ln_cd>5169810</spec_busn_ln_cd>
<spec_busn_ln_cd>136976</spec_busn_ln_cd>
<newSpecificCode>02O</newSpecificCode>
<spec_busn_ln_cd>5169813</spec_busn_ln_cd>
<sic87Code>3728</sic87Code>
<newSpecificCode>042</newSpecificCode>
<naics2012Code>336412</naics2012Code>
<spec_busn_ln_cd>137030</spec_busn_ln_cd>
<sic87Code>3724</sic87Code>
<naics2012Code>336411</naics2012Code>
<newSpecificCode>0V3</newSpecificCode>
<newSpecificCode>0V6</newSpecificCode>
</SectorCodes>
</SectorL3>
<SectorL3>
<label>Aircraft & Aerospace Parts</label>
<cmsID>1004</cmsID>
<order/>
<referenceListStatus>1</referenceListStatus>
<owlCode>AnA_Parts</owlCode>
<SectorCodes>
<brd_busn_ln_cd>136885</brd_busn_ln_cd>
<spec_busn_ln_cd>137023</spec_busn_ln_cd>
<sic87Code>3728</sic87Code>
<spec_busn_ln_cd>137068</spec_busn_ln_cd>
<newSpecificCode>01O</newSpecificCode>
<newSpecificCode>02V</newSpecificCode>
<naics2012Code>336413</naics2012Code>
</SectorCodes>
</SectorL3>
<SectorL3>
<label>Aircraft & Aerospace Services</label>
<cmsID>1005</cmsID>
<order/>
<referenceListStatus>1</referenceListStatus>
<owlCode>AnA_Services</owlCode>
<SectorCodes>
<brd_busn_ln_cd>136885</brd_busn_ln_cd>
<brd_busn_ln_cd>136883</brd_busn_ln_cd>
<newSpecificCode>089</newSpecificCode>
<spec_busn_ln_cd>139942</spec_busn_ln_cd>
<spec_busn_ln_cd>147197</spec_busn_ln_cd>
<newSpecificCode>09B</newSpecificCode>
<spec_busn_ln_cd>138522</spec_busn_ln_cd>
<newSpecificCode>0T5</newSpecificCode>
<naics2012Code>488119</naics2012Code>
<sic87Code>4581</sic87Code>
<newSpecificCode>05W</newSpecificCode>
<spec_busn_ln_cd>137041</spec_busn_ln_cd>
<spec_busn_ln_cd>5160524</spec_busn_ln_cd>
<naics2012Code>488190</naics2012Code>
<spec_busn_ln_cd>5160525</spec_busn_ln_cd>
<newSpecificCode>02F</newSpecificCode>
<newSpecificCode>0T6</newSpecificCode>
</SectorCodes>
</SectorL3>
<SectorL3>
<label>Defense Equipment</label>
<cmsID>1006</cmsID>
<order/>
<referenceListStatus>1</referenceListStatus>
<owlCode>Defense_Equipment_ind</owlCode>
<SectorCodes>
<brd_busn_ln_cd>136885</brd_busn_ln_cd>
<newSpecificCode>00E</newSpecificCode>
<sic87Code>3795</sic87Code>
<spec_busn_ln_cd>147201</spec_busn_ln_cd>
<naics2012Code>336415</naics2012Code>
<naics2012Code>336992</naics2012Code>
<spec_busn_ln_cd>139932</spec_busn_ln_cd>
<newSpecificCode>0AB</newSpecificCode>
<newSpecificCode>0A9</newSpecificCode>
<spec_busn_ln_cd>147202</spec_busn_ln_cd>
<newSpecificCode>002</newSpecificCode>
<spec_busn_ln_cd>147200</spec_busn_ln_cd>
<newSpecificCode>09L</newSpecificCode>
<spec_busn_ln_cd>137111</spec_busn_ln_cd>
<spec_busn_ln_cd>137115</spec_busn_ln_cd>
<newSpecificCode>018</newSpecificCode>
<naics2012Code>336414</naics2012Code>
<naics2012Code>336419</naics2012Code>
<newSpecificCode>0AA</newSpecificCode>
<newSpecificCode>00I</newSpecificCode>
<sic87Code>3764</sic87Code>
<spec_busn_ln_cd>137084</spec_busn_ln_cd>
<sic87Code>3769</sic87Code>
<spec_busn_ln_cd>137127</spec_busn_ln_cd>
<sic87Code>3761</sic87Code>
</SectorCodes>
</SectorL3>
<SectorL3>
<label>Defense Services</label>
<cmsID>1007</cmsID>
<order/>
<referenceListStatus>1</referenceListStatus>
<owlCode>Defense_Services</owlCode>
<SectorCodes>
<brd_busn_ln_cd>136885</brd_busn_ln_cd>
<newSpecificCode>0RK</newSpecificCode>
<spec_busn_ln_cd>5160230</spec_busn_ln_cd>
<newSpecificCode>009</newSpecificCode>
<spec_busn_ln_cd>137120</spec_busn_ln_cd>
</SectorCodes>
</SectorL3>
<SectorCodes>
<sic87Code>3721</sic87Code>
<naics2012Code>33641</naics2012Code>
</SectorCodes>
</SectorL2>
<SectorL2>
<label>Agricultural Products</label>
<cmsID>1008</cmsID>
<order/>
<referenceListStatus>1</referenceListStatus>
<owlCode>Agricultural_Products</owlCode>
<SectorL3>
<label>Agricultural Commodities</label>
<cmsID>1009</cmsID>
<order/>
<referenceListStatus>1</referenceListStatus>
<owlCode>Agricultural_Commodities</owlCode>
<SectorCodes>
<spec_busn_ln_cd>5169814</spec_busn_ln_cd>
<brd_busn_ln_cd>136885</brd_busn_ln_cd>
<newSpecificCode>0V7</newSpecificCode>
<newSpecificCode>010</newSpecificCode>
<spec_busn_ln_cd>137092</spec_busn_ln_cd>
<sic87Code>6221</sic87Code>
<naics2012Code>52313</naics2012Code>
</SectorCodes>
</SectorL3>
<SectorL3>
<label>Agriculture</label>
<cmsID>1010</cmsID>
<order/>
<referenceListStatus>1</referenceListStatus>
<owlCode>Agriculture_PnA</owlCode>
<SectorCodes>
<sic87Code>1</sic87Code>
<naics2012Code>111</naics2012Code>
<brd_busn_ln_cd>136885</brd_busn_ln_cd>
<newSpecificCode>0O7</newSpecificCode>
<spec_busn_ln_cd>136898</spec_busn_ln_cd>
<newSpecificCode>05L</newSpecificCode>
<spec_busn_ln_cd>136939</spec_busn_ln_cd>
<newSpecificCode>0V8</newSpecificCode>
<spec_busn_ln_cd>5169815</spec_busn_ln_cd>
<newSpecificCode>05G</newSpecificCode>
<spec_busn_ln_cd>5156215</spec_busn_ln_cd>
<spec_busn_ln_cd>136905</spec_busn_ln_cd>
<naics2012Code>1151</naics2012Code>
<newSpecificCode>04U</newSpecificCode>
</SectorCodes>
</SectorL3>
<SectorL3>
<label>Protein</label>
<cmsID>1011</cmsID>
<order/>
<referenceListStatus>1</referenceListStatus>
<owlCode>Protein</owlCode>
<SectorCodes>
<brd_busn_ln_cd>136885</brd_busn_ln_cd>
<spec_busn_ln_cd>136927</spec_busn_ln_cd>
<newSpecificCode>03Y</newSpecificCode>
<sic87Code>2</sic87Code>
<naics2012Code>112</naics2012Code>
<naics2012Code>1152</naics2012Code>
<spec_busn_ln_cd>136983</spec_busn_ln_cd>
<newSpecificCode>053</newSpecificCode>
</SectorCodes>
</SectorL3>
<SectorCodes/>
</SectorL2>
</SectorL1>
VBA
Public Sub Convert_XML_To_Excel_Through_VBA()
'Add referece from Menu: "Tools -> References -> Microsoft XML Vn.0"
Dim iRow As Integer, iCol As Integer
Dim xmlDoc As MSXML2.DOMDocument, xmlRoot As MSXML2.IXMLDOMNode
Dim xmlNodes, xmlNodes2, xmlNodes3 As MSXML2.IXMLDOMNode, xmlData As MSXML2.IXMLDOMNode
Set xmlDoc = New MSXML2.DOMDocument
'Load & Wait till complete XML Data is loaded
xmlDoc.async = False
xmlDoc.validateOnParse = False
'Change the XML file path
xmlDoc.Load ("C:\Users\SRIVAPAW\Desktop\sector.xml")
'XML Loaded. Now Read Elements One by One into XML DOM Objects
Set xmlRoot = xmlDoc.DocumentElement
Set xmlNodes = xmlRoot.FirstChild
' Set lists = xmlDoc.SelectNodes("//SectorL1/SectorL2/SectorL3/label")
' Debug.Print xmlNodes2.FirstChild.ChildNodes.Length
'Read XML Data and Load into Excel Sheet by each Node and Chile Node
'ActiveSheet.Cells(1, 1) = xmlNodes.XML
'Debug.Print xmlNodes.XML
'Debug.Print lists.ChildNodes.Length
Debug.Print xmlRoot.FirstChild.ChildNodes(6).ChildNodes(0).Text
Debug.Print xmlRoot.FirstChild.ChildNodes(6).ChildNodes(5).ChildNodes(0).Text
Debug.Print xmlRoot.FirstChild.ChildNodes(6).ChildNodes(5).ChildNodes(5).ChildNodes(0).Text
iRow = 1
For Each xmlNodes In xmlRoot.ChildNodes
iCol = 0
iRow = iRow + 1
i_6 = iRow
'Debug.Print xmlNodes2.FirstChild.Length
'******************** Sector L1
For Each xmlData In xmlNodes.ChildNodes
If xmlData.BaseName = "label" Then
ThisWorkbook.Sheets(1).Cells(iRow, 1) = xmlData.Text
ElseIf xmlData.BaseName = "cmsID" Then
ThisWorkbook.Sheets(1).Cells(iRow, 2) = xmlData.Text
ElseIf xmlData.BaseName = "owlCode" Then
ThisWorkbook.Sheets(1).Cells(iRow, 3) = xmlData.Text
ElseIf xmlData.BaseName = "frameOfReference" Then
ThisWorkbook.Sheets(1).Cells(iRow, 4) = xmlData.Text
'******************** Sector L2
ElseIf xmlData.BaseName = "SectorL2" Then
iRow = iRow + 1
i_6 = iRow
For Each xmldata1 In xmlData.ChildNodes
If xmldata1.BaseName = "label" Then
ThisWorkbook.Sheets(1).Cells(iRow, 1) = xmldata1.Text
ElseIf xmldata1.BaseName = "cmsID" Then
ThisWorkbook.Sheets(1).Cells(iRow, 2) = xmldata1.Text
ElseIf xmldata1.BaseName = "owlCode" Then
ThisWorkbook.Sheets(1).Cells(iRow, 3) = xmldata1.Text
ElseIf xmldata1.BaseName = "frameOfReference" Then
ThisWorkbook.Sheets(1).Cells(iRow, 4) = xmldata1.Text
'******************** Sector L3
ElseIf xmldata1.BaseName = "SectorL3" Then
iRow = iRow + 1
i_6 = iRow
For Each xmldata2 In xmldata1.ChildNodes
If xmldata2.BaseName = "label" Then
ThisWorkbook.Sheets(1).Cells(iRow, 1) = xmldata2.Text
ElseIf xmldata2.BaseName = "cmsID" Then
ThisWorkbook.Sheets(1).Cells(iRow, 2) = xmldata2.Text
ElseIf xmldata2.BaseName = "owlCode" Then
ThisWorkbook.Sheets(1).Cells(iRow, 3) = xmldata2.Text
ElseIf xmldata2.BaseName = "frameOfReference" Then
ThisWorkbook.Sheets(1).Cells(iRow, 4) = xmldata2.Text
'******************** Sector L4
ElseIf xmldata2.BaseName = "SectorL4" Then
iRow = iRow + 1
i_6 = iRow
For Each xmldata3 In xmldata2.ChildNodes
If xmldata3.BaseName = "label" Then
ThisWorkbook.Sheets(1).Cells(iRow, 1) = xmldata3.Text
ElseIf xmldata3.BaseName = "cmsID" Then
ThisWorkbook.Sheets(1).Cells(iRow, 2) = xmldata3.Text
ElseIf xmldata3.BaseName = "owlCode" Then
ThisWorkbook.Sheets(1).Cells(iRow, 3) = xmldata3.Text
ElseIf xmldata3.BaseName = "frameOfReference" Then
ThisWorkbook.Sheets(1).Cells(iRow, 4) = xmldata3.Text
'******************** Sector Codes For L4
ElseIf xmldata3.BaseName = "sectorPurpose" Then
If ThisWorkbook.Sheets(1).Cells(i_6, 10) <> "" Then i_6 = i_6 + 1
ThisWorkbook.Sheets(1).Cells(i_6, 10) = xmldata3.Text
iRow = i_6
ElseIf xmldata3.BaseName = "SectorCodes" Then
Dim i_1, i_2, i_3, i_4, i_5 As Long
i_1 = iRow
i_2 = iRow
i_3 = iRow
i_4 = iRow
i_5 = iRow
For Each xmlData4 In xmldata3.ChildNodes
If xmlData4.BaseName = "brd_busn_ln_cd" Then
If ThisWorkbook.Sheets(1).Cells(i_1, 5) <> "" Then i_1 = i_1 + 1
ThisWorkbook.Sheets(1).Cells(i_1, 5) = xmlData4.Text
ElseIf xmlData4.BaseName = "spec_busn_ln_cd" Then
If ThisWorkbook.Sheets(1).Cells(i_2, 6) <> "" Then i_2 = i_2 + 1
ThisWorkbook.Sheets(1).Cells(i_2, 6) = xmlData4.Text
ElseIf xmlData4.BaseName = "newSpecificCode" Then
If ThisWorkbook.Sheets(1).Cells(i_3, 7) <> "" Then i_3 = i_3 + 1
ThisWorkbook.Sheets(1).Cells(i_3, 7) = xmlData4.Text
ElseIf xmlData4.BaseName = "sic87Code" Then
If ThisWorkbook.Sheets(1).Cells(i_4, 8) <> "" Then i_4 = i_4 + 1
ThisWorkbook.Sheets(1).Cells(i_4, 8) = xmlData4.Text
ElseIf xmlData4.BaseName = "naics2012Code" Then
If ThisWorkbook.Sheets(1).Cells(i_5, 9) <> "" Then i_5 = i_5 + 1
ThisWorkbook.Sheets(1).Cells(i_5, 9) = xmlData4.Text
End If
Next xmlData4
iRow = Application.WorksheetFunction.Max(i_1, i_2, i_3, i_4, i_5)
End If
Next xmldata3
'******************** Sector Codes For L3
ElseIf xmldata2.BaseName = "sectorPurpose" Then
If ThisWorkbook.Sheets(1).Cells(i_6, 10) <> "" Then i_6 = i_6 + 1
ThisWorkbook.Sheets(1).Cells(i_6, 10) = xmldata2.Text
iRow = i_6
ElseIf xmldata2.BaseName = "SectorCodes" Then
i_1 = iRow
i_2 = iRow
i_3 = iRow
i_4 = iRow
i_5 = iRow
For Each xmldata3 In xmldata2.ChildNodes
If xmldata3.BaseName = "brd_busn_ln_cd" Then
If ThisWorkbook.Sheets(1).Cells(i_1, 5) <> "" Then i_1 = i_1 + 1
ThisWorkbook.Sheets(1).Cells(i_1, 5) = xmldata3.Text
ElseIf xmldata3.BaseName = "spec_busn_ln_cd" Then
If ThisWorkbook.Sheets(1).Cells(i_2, 6) <> "" Then i_2 = i_2 + 1
ThisWorkbook.Sheets(1).Cells(i_2, 6) = xmldata3.Text
ElseIf xmldata3.BaseName = "newSpecificCode" Then
If ThisWorkbook.Sheets(1).Cells(i_3, 7) <> "" Then i_3 = i_3 + 1
ThisWorkbook.Sheets(1).Cells(i_3, 7) = xmldata3.Text
ElseIf xmldata3.BaseName = "sic87Code" Then
If ThisWorkbook.Sheets(1).Cells(i_4, 8) <> "" Then i_4 = i_4 + 1
ThisWorkbook.Sheets(1).Cells(i_4, 8) = xmldata3.Text
ElseIf xmldata3.BaseName = "naics2012Code" Then
If ThisWorkbook.Sheets(1).Cells(i_5, 9) <> "" Then i_5 = i_5 + 1
ThisWorkbook.Sheets(1).Cells(i_5, 9) = xmldata3.Text
End If
Next xmldata3
iRow = Application.WorksheetFunction.Max(i_1, i_2, i_3, i_4, i_5)
End If
Next xmldata2
ElseIf xmldata1.BaseName = "sectorPurpose" Then
If ThisWorkbook.Sheets(1).Cells(i_6, 10) <> "" Then i_6 = i_6 + 1
ThisWorkbook.Sheets(1).Cells(i_6, 10) = xmlData.Text
iRow = i_6
ElseIf xmldata1.BaseName = "SectorCodes" Then
i_1 = iRow
i_2 = iRow
i_3 = iRow
i_4 = iRow
i_5 = iRow
For Each xmldata3 In xmldata1.ChildNodes
If xmldata3.BaseName = "brd_busn_ln_cd" Then
If ThisWorkbook.Sheets(1).Cells(i_1, 5) <> "" Then i_1 = i_1 + 1
ThisWorkbook.Sheets(1).Cells(i_1, 5) = xmldata3.Text
ElseIf xmldata3.BaseName = "spec_busn_ln_cd" Then
If ThisWorkbook.Sheets(1).Cells(i_2, 6) <> "" Then i_2 = i_2 + 1
ThisWorkbook.Sheets(1).Cells(i_2, 6) = xmldata3.Text
ElseIf xmldata3.BaseName = "newSpecificCode" Then
If ThisWorkbook.Sheets(1).Cells(i_3, 7) <> "" Then i_3 = i_3 + 1
ThisWorkbook.Sheets(1).Cells(i_3, 7) = xmldata3.Text
ElseIf xmldata3.BaseName = "sic87Code" Then
If ThisWorkbook.Sheets(1).Cells(i_4, 8) <> "" Then i_4 = i_4 + 1
ThisWorkbook.Sheets(1).Cells(i_4, 8) = xmldata3.Text
ElseIf xmldata3.BaseName = "naics2012Code" Then
If ThisWorkbook.Sheets(1).Cells(i_5, 9) <> "" Then i_5 = i_5 + 1
ThisWorkbook.Sheets(1).Cells(i_5, 9) = xmldata3.Text
End If
Next xmldata3
iRow = Application.WorksheetFunction.Max(i_1, i_2, i_3, i_4, i_5)
End If
Next xmldata1
ElseIf xmlData.BaseName = "sectorPurpose" Then
If ThisWorkbook.Sheets(1).Cells(i_6, 10) <> "" Then i_6 = i_6 + 1
ThisWorkbook.Sheets(1).Cells(i_6, 10) = xmlData.Text
iRow = i_6
ElseIf xmlData.BaseName = "SectorCodes" Then
i_1 = iRow
i_2 = iRow
i_3 = iRow
i_4 = iRow
i_5 = iRow
For Each xmldata3 In xmlData.ChildNodes
If xmldata3.BaseName = "brd_busn_ln_cd" Then
If ThisWorkbook.Sheets(1).Cells(i_1, 5) <> "" Then i_1 = i_1 + 1
ThisWorkbook.Sheets(1).Cells(i_1, 5) = xmldata3.Text
ElseIf xmldata3.BaseName = "spec_busn_ln_cd" Then
If ThisWorkbook.Sheets(1).Cells(i_2, 6) <> "" Then i_2 = i_2 + 1
ThisWorkbook.Sheets(1).Cells(i_2, 6) = xmldata3.Text
ElseIf xmldata3.BaseName = "newSpecificCode" Then
If ThisWorkbook.Sheets(1).Cells(i_3, 7) <> "" Then i_3 = i_3 + 1
ThisWorkbook.Sheets(1).Cells(i_3, 7) = xmldata3.Text
ElseIf xmldata3.BaseName = "sic87Code" Then
If ThisWorkbook.Sheets(1).Cells(i_4, 8) <> "" Then i_4 = i_4 + 1
ThisWorkbook.Sheets(1).Cells(i_4, 8) = xmldata3.Text
ElseIf xmldata3.BaseName = "naics2012Code" Then
If ThisWorkbook.Sheets(1).Cells(i_5, 9) <> "" Then i_5 = i_5 + 1
ThisWorkbook.Sheets(1).Cells(i_5, 9) = xmldata3.Text
End If
Next xmldata3
iRow = Application.WorksheetFunction.Max(i_1, i_2, i_3, i_4, i_5)
End If
Next xmlData
Next xmlNodes
End Sub
Comments
Post a Comment