FILTERXML 함수 - XML에서 특정 데이터 가져오기

엑셀 FILTERXML 함수는 XML 데이터에서 원하는 부분을 가져옵니다. 웹서비스에서 데이터를 가져오는 WEBSERVICE 함수와 같이 사용하면 외부의 XML 데이터를 엑셀로 가져와서 손쉽게 가공할 수 있습니다.

 

구문(Syntax)

함수 사용 시 주의 사항

• XML이 유효하지 않으면 #VALUE 오류 발생
• XML에 유효하지 않은 네임스페이스가 포함된 경우 #VALUE 오류 발생

 

사용 예

1. 기본 사용법

다음과 같은 XML 데이터가 있을 때 FILTERXML 함수로 데이터를 추출해 보겠습니다.

<?xml version="1.0" encoding="UTF-8"?>
<factory>
<product>
	<name>무선 마우스</name>
	<code>A002</code>
	<price>45000</price>
</product>
<product>	
	<name>외장 SSD</name>
	<code>A006</code>
	<price>155000</price>	
</product>
</factory>

 

product 노드 하위의 name을 가져오도록 [B9] 셀에 다음 수식을 입력합니다.

=FILTERXML($B$5,"//product/name")

 

product 노드 하위의 name을 가져왔습니다. 수식을 한번만 입력했는데 2개의 데이터를 가져왔습니다.

엑셀 2021, Microsoft 365 이상 버전부터는 동적배열을 지원하므로 위와 같이 수식을 한번만 입력해도 데이터를 배열형태로 가져올 수 있습니다.

동적배열을 지원하지 않는 엑셀 2019 이하 버전에서는 데이터를 가져올 영역을 충분히 넓게 선택한 후 수식을 입력하고 [Ctrl+Shift+Enter]를 눌러야 합니다.

 

나머지 code와 price를 가져오기 위해 [C9], [D9] 셀에 각각 다음 수식을 입력합니다.

=FILTERXML($B$5,"//product/code")

=FILTERXML($B$5,"//product/price")

 

 

2. 외부 데이터 가져와서 필요한 부분 추출하기

이번에는 외부에서 서비스되는 RSS(XML 형식) 데이터를 가져와서 필요한 부분을 추출해 보겠습니다.

예시는 '한국경제신문'의 증권 카테고리 RSS 서비스 데이터입니다.

이 RSS의 XML은 다음과 같은 구조로 되어 있습니다. item 노드의 하위에 있는 title(기사 제목), link(링크), pubDate(발행일)를 가져오겠습니다.

<item>
	<title><![CDATA["1억 넣은 투자자는 휴대폰 한 대 값도 못 건져" 비명 [박의명의 불개미 구조대]]]></title>
	<link><![CDATA[https://www.hankyung.com/finance/article/202203195745i?rss=r]]></link>
	<image><![CDATA[//img.hankyung.com/photo/202203/99.27321732.1.jpg]]></image>
	<author><![CDATA[박의명]]></author>
	<pubDate>Sat, 19 Mar 2022 08:35:40 +0900</pubDate>
</item>

 

item 노드 하위의 title을 가져오도록 [B20] 셀에 다음 수식을 입력합니다.

=FILTERXML($B$16,"//item/title")

 

나머지 link와 pubDate를 가져오기 위해 [C9], [D9] 셀에 각각 다음 수식을 입력합니다.

=FILTERXML($B$16,"//item/link")

=FILTERXML($B$16,"//item/pubDate")

 

 

---

관련 글