엑셀 VBA - XML Parsing(XML 6.0 기준) (1)

1. XML 문서 형식

book1.xml

0.00MB

 

 

위 파일의 내용은 아래와 같습니다.

[https://learn.microsoft.com/en-us/dotnet/standard/data/xml/xml-document-object-model-dom  참고]

<?xml version="1.0"?>
  <books>
    <book>
        <author>Carson</author>
        <price format="dollar">31.95</price>
        <pubdate>05/01/2001</pubdate>
    </book>
    <pubinfo>
        <publisher>MSPress</publisher>
        <state>WA</state>
    </pubinfo>
  </books>

 

[XML document structure]

 

위 그림의 각 원은 XmlNode 개체라고 하는 노드를 나타냅니다. XmlNode 개체는 DOM 트리에서 기본 개체입니다. XmlNode를 확장한 XmlDocument 클래스는 XML을 메모리에 로드하거나 파일로 저장하는 것처럼 문서 전체에 수행되는 메서드를 지원합니다.

 

• 노드에는 하나의 부모 노드(parent node)가 있습니다. 부모 노드는 해당 노드의 바로 위에 있는 노드입니다. Document 루트는 문서 자체와 문서 조각을 포함하는 최상위 노드이므로 부모가 없는 유일한 노드입니다.
• 대부분의 노드에는 자식 노드(child nodes), 즉 바로 아래에 있는 노드가 여러 개 있을 수 있습니다. 
• 다이어그램에 표시된 book 및 pubinfo 노드와 같이 동일한 수준의 노드는 형제 관계(siblings)입니다.

 

2. 준비사항

개발도구 - Visual Baisc 메뉴를 누른 후

​

도구 - 참조 메뉴를 클릭하면 Microsoft XML의 버전이 3.0과 6.0이 있는데, 버전에 따라 프로그램 작성방법이 다르므로 6.0에 체크합니다.

 

​

​

3. 주요 명령어

xml_parsing1.xlsm

0.03MB

 

book.xml

0.00MB

 

 

위에서 예제로 든 book1.xml은 book과 pubinfo로 되어 있어 복잡하므로, 아래와 같이 book만 있는 xml 파일을 대상으로 작업하겠습니다.

<?xml version="1.0"?>
<catalog>
   <book id="bk101">
      <author>Gambardella, Matthew</author>
      <title>XML Developer's Guide</title>
      <genre>Computer</genre>
      <price>44.95</price>
      <publish_date>2000-10-01</publish_date>
      <description>An in-depth look at creating applications 
      with XML.</description>
   </book>
   <book id="bk102">
      <author>Ralls, Kim</author>
      <title>Midnight Rain</title>
      <genre>Fantasy</genre>
      <price>5.95</price>
      <publish_date>2000-12-16</publish_date>
      <description>A former architect battles corporate zombies, 
      an evil sorceress, and her own childhood to become queen 
      of the world.</description>
   </book>
</catalog>

 

 

 

개발도구 - Visual Basic을 눌러 VBA로 들어갑니다.

​

가. 변수 선언

① HTTP 통신을 위한 변수

Dim xmlHttp As New MSXML2.ServerXMLHTTP60

​

② XML 6.0 문서 변수 선언

Dim xDoc As MSXML2.DOMDocument60

​

③ XML 문서 분석을 위한 노드 선언

- 노드 리스트

Dim xNodes As MSXML2.IXMLDOMNodeList, yNodes As MSXML2.IXMLDOMNodeList

​

- 노드 리스트내 노드 선언

Dim xNode As MSXML2.IXMLDOMNode, yNode As MSXML2.IXMLDOMNode

​

나. 변수 값 대입

① xml doc 문서 생성

Set xDoc = New MSXML2.DOMDocument60

​

② URL 검색결과인지 파일인지에 따라 로드 방법이 다름

'URL 검색결과를 xDoc에 로드

xDoc.LoadXML (xmlHttp.responseText)

​

'File을 xDoc에 로드

xDoc.Load ("d:\naver blog\book.xml")

book.xml 파일을 저장한 폴더를 지정해야 합니다.

​

다. Nodes와 Node

Nodes는 복수형이므로 여러 개의 Node를 가르킵니다.

​

Nodes는 Set 문을 이용해 아래와 같이 값을 대입합니다.

Set xNodes = xDoc.SelectNodes(Root Nodename)

​

Root Nodename은 2개가 있는데, xml은 의미가 없으므로, caltalog로 합니다.

Set xNodes = xDoc.SelectNodes("catalog")

​

Nodes 리스트내 Node를 처리하려면 아래와 같이 For Each 반복문을 사용하면 됩니다.

For Each xNode In xNodes

​

라. nodeName과 Text

nodename은 caltalog, book, author 등을 가르키며,

text는 node에 해당하는 값(문자)을 가르킵니다.

​

① nodeName

xml 문서에 처음에 xml 문서 선언이 있기 때문에

xDoc.ChildNodes.Item(0).nodeName 은 xml 이고,

​

두번째 인덱스인 Item(1) 다시 말해

xDoc.ChildNodes.Item(1).nodeName 은 catalog 가 됩니다.

​

따라서, 위에서 직접 catalog로 입력한 구문은

Set xNodes = xDoc.SelectNodes(xDoc.ChildNodes.Item(1).nodeName) 으로

바꿔쓸 수 있습니다.

​

② text

xDoc.childnodes.item(0).childnodes.item(0).selectSingleNode("title").text

​

catalog의 자식 노드 중 첫번째는 xDoc.childnodes.item(0)로 표시할 수 있으며, 노드명을 구하려면 .nodeName을 끝에 붙이며, book이 여러 개 있는데 item(0)이므로 첫번째 book을 가리킵니다.

​

첫번째 book의 title을 구하려고 하면

xDoc.childnodes.item(0).childnodes.item(0). 뒤에 selectSingleNode("title").text 라고 써서, title에 해당하는 한 개 노드의 문장(text)을 구합니다.

​

결과 값은 XML Developer's Guide 입니다. 위 XML 파일의 내용을 확인해보기 바랍니다.

​

변수의 결과를 직접 실행창에서 확인하려면 Debug.Print 명령을 사용하며,

Debug.Print xDoc.ChildNodes.Item(1).ChildNodes.Item(1).SelectSingleNode("title").Text

식으로 작성하면 됩니다.

​

"title"을 xDoc을 이용해 지정하려면

xDoc.ChildNodes.Item(1).ChildNodes.Item(1).ChildNodes.Item(1).nodeName 라고

3단계로 지정해야 합니다.

​

catalog가 1단계, book이 2단계, author 등이 3단계 입니다.

​

마. nodes(NodeList)의 갯수 구하기

Debug.Print (xNodes.Length)

xNodes는 첫번째 단계인 catalog를 가르키므로 xNodes의 갯수는 1이 됩니다.

​

Debug.Print (xNodes.Item(0).ChildNodes.Length)

xNodes.Item(0).ChildNodes는 catalog의 한단계 아래인 book을 가르키므로 book의 갯수를 구하는 것이며,

결과는 12입니다.

Option Explicit


Sub XML_Basic()
    'xml parsing을 위한 변수 설정
    Dim xmlHttp As New MSXML2.ServerXMLHTTP60
    Dim xDoc As MSXML2.DOMDocument60
    Dim xNodes As MSXML2.IXMLDOMNodeList, yNodes As MSXML2.IXMLDOMNodeList, zNodes As MSXML2.IXMLDOMNodeList
    Dim xNode As MSXML2.IXMLDOMNode, yNode As MSXML2.IXMLDOMNode, zNode As MSXML2.IXMLDOMNode
    
   
'    Call ClearImmediateWindow
    
    'xml doc 문서 생성
    Set xDoc = New MSXML2.DOMDocument60
    
    'File을 xDoc에 로드
    '경로를 안쓰면 안되는 경우가 있다. 왜?
    xDoc.Load ("d:\naver blog\book.xml")
    
    
    'nodeName을 직접 입력
    Set xNodes = xDoc.SelectNodes("catalog")
    
    'nodeName을 xDoc을 이용해 입력
    Set xNodes = xDoc.SelectNodes(xDoc.ChildNodes.Item(1).nodeName)

    
    Debug.Print xDoc.ChildNodes.Item(0).nodeName
    Debug.Print xDoc.ChildNodes.Item(1).nodeName
        
    Debug.Print xDoc.ChildNodes.Item(1).ChildNodes.Item(1).SelectSingleNode("author").Text
    Debug.Print xDoc.ChildNodes.Item(1).ChildNodes.Item(1).SelectSingleNode("title").Text
    
    Debug.Print xDoc.ChildNodes.Item(1).ChildNodes.Item(1).ChildNodes.Item(1).nodeName
    
    Debug.Print xDoc.ChildNodes.Item(1).ChildNodes.Item(1).SelectSingleNode(xDoc.ChildNodes.Item(1).ChildNodes.Item(1).ChildNodes.Item(1).nodeName).Text

    
    Debug.Print (xNodes.Length)
    Debug.Print (xNodes.Item(0).ChildNodes.Length)
    
End Sub

 

 

위 코드를 실행하면 아래와 같이 직접 실행창에 내용이 표시됩니다.

 

바. 파일이 없을 경우 에러 메시지

​해당 경로에 파일, 위의 경우는 d:\naver blog\book.xml,이 없으면 아래와 같은 '개체 변수가 설정되어 있지 않다'는 에러가 발생합니다.

 

2편에서는 for each 문을 사용해 간단하게 만들어 보겠습니다.