[MSSQL] Query로 XML 파싱하기

 

일반적으로 레거시에서 ETL할 때 XML값을 파싱 및 처리해서 가져오지만,

애드혹으로 들어온 데이터에서 SQL로 XML 파싱이 필요하여 정리합니다.

 

XML 문서의 구조와 특징을 간단히 정리하고 SQL로 XML을 파싱해보겠습니다.

 

---

 

1. Element / Attribute

XML은 Element와 Attribute 로 구성되어 있습니다.

• XML Element는 시작 태그부터 종료 태그까지의 모든 것을 의미합니다.
• XML Attribute는 Element에 대한 추가적인 정보를 제공합니다.

# Element 예시
<Employees>
   <Employee>
      <EmpID>511111</EmpID>
      <FirstName>Jay</FirstName>
      <LastName>Kim</LastName>
  </Employee>
  <Employee>
      <EmpID>511112</EmpID>
      <FirstName>David</FirstName>
      <LastName>Kim</LastName>
  </Employee>
  <Employee>
      <EmpID>511113</EmpID>
      <FirstName>Ted</FirstName>
      <LastName>Park</LastName>
  </Employee>
</Employees>

# Attribute 예시
<Attribute>
  <key age=“22”>
   <key region="kor">
   <key phone="Samsung", number="111222333">
</Attribute>

위와 같은 단순한 형태의 Element를 파싱하는 경우, MSSQL에서 제공하는 XML 함수(value)를 사용합니다.

MS SQL에서 제공하는 XML 함수는 아래 문서에서 확인 가능합니다.
MS SQL XML Function Docs

# MS SQL 기본 함수 사용하기 - value 함수

# 예시를 위한 XML 선언
DECLARE @xml XML ='
<Employees>
   <Employee>
      <EmpID>511111</EmpID>
      <FirstName>Jay</FirstName>
      <LastName>Kim</LastName>
  </Employee>
  <Employee>
      <EmpID>511112</EmpID>
      <FirstName>David</FirstName>
      <LastName>Kim</LastName>
  </Employee>
  <Employee>
      <EmpID>511113</EmpID>
      <FirstName>Ted</FirstName>
      <LastName>Park</LastName>
  </Employee>
</Employees>
'

SELECT
  FirstName = item.row.value('FirstName[1]','nvarchar(max)'),
  LastName = item.row.value('LastName[1]','nvarchar(max)')
FROM @xml.nodes('Employees/Employee') item(row)

 

2. Attribute 처리

단순한 형태의 XML 데이터라면 위의 함수로 처리가 될 것 입니다.

 

다만, 주어진 XML 데이터에서 한가지 고려할 사항은 Attribute의 특정 컬럼 값이 리스트인 점 입니다.

해당 값에 대하여 1:n 형태의 결과값이 필요하였고 Loop 형식으로 처리하였습니다.

# Attribute 형태 값
<data key="note">      
  <value>        
    <List>          
      <String>111111</String>          
      <String>111112</String>
      <String>111113</String>
      <String>111114</String>
     </List>      
  </value>    
</data>

# 도출할 결과값 Table 형태
note | 111111
note | 111112
note | 111113
note | 111114

#========================================#
# 실행 쿼리
# 방법 1 - value 함수 사용
SELECT  
  CONVERT(xml, Attributes).query('/Attributes/Map/entry[@key = ''note'']').value('(/entry/@value)[1]','nvarchar(max)') AS note
FROM
  tbl_name
  
# 방법 2 - replace 함수 사용 (권장x)
SELECT
  REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(nvarchar(MAX),CONVERT(xml, Attributes).query('Attributes/Map/entry[@key = ''note'']/value/List')),'<List>',''),'</List>',''),'<String>',''),'</String','')
FROM
  tbl_name

 

3. 발생한 이슈 및 해결방안

단순히 Loop 형식으로 값을 처리하니 쿼리 실행시간에 대한 이슈가 발생하였습니다.

이유는 실행결과를 통해 확인해보니 MS SQL의 XML 함수가 내부적으로 Loop 로직으로 작동하였고,
이는 중복 Loop가 발생시켜 엄청난 오버헤드를 일으켰습니다.

 

다음와 같이 시도해보고 해결하였습니다.

임시테이블을 활용하여 처리 과정 두 단계로 나누었고, 일차적으로 임시테이블에 List 부분을 XML 값으로 넣어주었습니다.

이후 본 테이블로 Insert 할 때 cross apply로 처리하였습니다.

# 임시테이블 생성
DROP TABLE  if exists #tmp_table;

# 데이터 삽입
SELECT * INTO #tmp_table
FROM (
    SELECT
      note
    FROM
      (SELECT
      	REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(nvarchar(MAX),CONVERT(xml, Attributes).query('Attributes/Map/entry[@key = ''note'']/value/List')),'<List>',''),'</List>',''),'<String>',''),'</String','') AS note
       FROM tbl_xml
       ) T1
) T2

# 메인테이블에 데이터 삽입
INSERT INTO tbl_main
SELECT *
FROM (
	SELECT 
          A.VALUE AS note
        FROM 
          #tmp_table
       ) T3
 CROSS APPLY STRING_SPLIT(note,'>') as A
 ) Z
 ;

 

4. 느낀 점

XML은 이미 많이 사용하는 형태이기에 MS SQL에서 제공하는 기본함수로도 여러 형태를 커버할 수 있었습니다.
기본 함수에 대한 활용법을 익힌 후 사용하면 본인에게 필요한 형태를 대부분 처리할 수 있다고 생각합니다.

다만 XML을 전처리하는 것은 데이터베이스에 상당한 부하를 줄 수 있으므로 미리 파싱해서 가져오는 것이 베스트입니다.

 

---

Ref.

01. [Docs] https://aws.amazon.com/ko/what-is/xml/

02. [Docs] https://bagbokman.tistory.com/10

03. [Docs] https://velog.io/@cil05265/XML%EA%B3%BC-JSON%EC%9D%98-%ED%8A%B9%EC%A7%95-%EA%B3%B5%ED%86%B5%EC%A0%90-%EC%B0%A8%EC%9D%B4%EC%A0%90

04. [Blog] https://www.ddokbaro.com/3772

05. [Blog] https://www.wrapuppro.com/programing/view/BOMKPyljW2VAtVZ

06. [Youtube] https://www.youtube.com/watch?v=fnGTnUUCUCI&list=WL&index=23