XML은 산업 표준의 데이터 교환 포맷이다. 대부분의 DBMS는 XML 문서를 저장, 변환하는 기능을 제공한다. XML 타입의 필드로 적법한(Well formed) 문서를 저장할 수 있고 스키마를 지정하면 유효성(Valid)을 점검할 수도 있다. OPENXML 함수로 XML 문서를 불러와 RDB에 넣을 수 있고 반대로 FOR XML문으로 RDB를 XML 형식으로 변환할 수도 있다.
XML에 대한 얘기는 길고 복잡해서 별도의 책 한권을 봐야 할 정도의 부피이고 이걸 SQL과 함께 사용하는 것도 기술적으로 난이도가 높다. 여기서는 실용적으로 쓸만한 FOR XML문에 대해서만 간략하게 소개한다. FOR XML 다음엔 변환 형식을 지정하는 RAW, AUTO, EXPLICIT, PATH 등의 키워드와 여러 가지 옵션이 온다. 도시 목록을 가지는 tCity 테이블로 실습해 보자.
SELECT * FROM tCity;
RDB는 표 형태로 테이블을 보여 준다. 이 정보를 다른 시스템과 교환하려면 XML로 변환해야 한다. 쿼리문 끝에 FOR XML 문을 붙여 변환 형식을 지정한다. 가장 기본적인 형식은 다음과 같다.
SELECT * FROM tCity FOR XML RAW;
<row name="부산 " area="765" popu="1234" metro="y" region="경상 "/>
<row name="서울 " area="605" popu="977" metro="y" region="경기 "/>
<row name="순천 " area="910" popu="27" metro="n" region="전라 "/>
<row name="오산 " area="42" popu="21" metro="n" region="경기 "/>
<row name="전주 " area="205" popu="65" metro="n" region="전라 "/>
<row name="청주 " area="940" popu="83" metro="n" region="충청 "/>
<row name="춘천 " area="1116" popu="27" metro="n" region="강원 "/>
<row name="홍천 " area="1819" popu="7" metro="n" region="강원 "/>
레코드 하나당 row 엘리먼트 하나로 출력되며 필드는 속성으로 표현한다. RAW에 인수를 지정하면 엘리먼트 이름이 되고 ROOT 옵션에 인수를 지정하면 전체 레코드를 감싸는 루트 엘리먼트를 만들어 준다.
SELECT * FROM tCity FOR XML RAW('city'), ROOT('korea');
<korea> 루트 태그 안에 <city> 태그를 배치한다.
<korea>
<city name="부산 " area="765" popu="1234" metro="y" region="경상 "/>
<city name="서울 " area="605" popu="977" metro="y" region="경기 "/>
....
</korea>
area, popu 같은 필드가 XML 문서의 필드로 표현되는데 각각의 엘리먼트로 정의하고 싶으면 ELEMENTS 옵션을 추가한다. 옵션의 순서는 아무래도 상관 없다.
SELECT * FROM tCity FOR XML RAW('city'), ROOT('korea'), ELEMENTS;
<korea>
<city>
<name>부산 </name>
<area>765</area>
<popu>1234</popu>
<metro>y</metro>
<region>경상 </region>
</city>
....
</korea>
FOR XML AUTO는 레코드 엘리먼트의 이름을 테이블명으로 자동 지정한다. FOR XML RAW('tCity')와 같되 테이블명을 자동으로 조사해 주는 정도만 다르다. ROOT나 ELEMENTS 옵션도 물론 사용할 수 있다.
SELECT * FROM tCity FOR XML AUTO;
<tCity name="부산 " area="765" popu="1234" metro="y" region="경상 "/>
<tCity name="서울 " area="605" popu="977" metro="y" region="경기 "/>
<tCity name="순천 " area="910" popu="27" metro="n" region="전라 "/>
....
RAW와 AUTO는 ELEMENTS 옵션 여부에 따라 전체 필드를 속성으로 표현할지, 아니면 엘리먼트로 표현할지를 결정하며 개별 지정은 할 수 있다. EXPLICIT는 각 필드를 어떻게 표현할지 상세하게 지정할 수 있으나 필드마다 표현 형식을 지정해야 하므로 문법이 복잡하다. 이를 좀 단순하게 표현하는 옵션이 PATH이다.
여기서는 FOR XML PATH를 사용하여 행별로 나열되어 있는 값을 하나로 합쳐 표시하는 실습을 단계별로 해 보자. 다음 쿼리문은 도시의 이름만 나열한다.
SELECT name FROM tCity;
수직으로 나열된 이름을 하나로 합쳐 보자. XML로 바뀌면서 도시명이 옆으로 주욱 나열된다.
SELECT name FROM tCity FOR XML PATH;
결과 : <row><name>부산 </name></row><row><name>서울 </name></row>....
row 엘리먼트는 굳이 필요치 않으니 PATH에 빈 태그명을 주어 제거한다.
SELECT name FROM tCity FOR XML PATH('');
결과 : <name>부산 </name><name>서울 </name>....
row는 제거되었지만 아직 name 태그가 붙어 있다. 앞 뒤에 뭘 덧붙여 필드를 가공하면 name 엘리먼트도 사라진다. 다음은 도시명 사이에 콤마를 넣어 서로 구분한다.
SELECT ',' + name FROM tCity FOR XML PATH('');
결과 : ,부산 ,서울 ,순천 ,오산 ,전주 ,청주 ,춘천 ,홍천
name 필드가 CHAR(10)이어서 일정한 폭만큼 차지한다. 콤마 사이가 너무 벌어져 보기 싫은데 이럴 때 TRIM 함수로 뒷부분의 공백을 제거한다. name이 VARCHAR라면 이 처리는 필요치 않다.
SELECT ',' + TRIM(name) FROM tCity FOR XML PATH('');
결과 : ,부산,서울,순천,오산,전주,청주,춘천,홍천
도시명 사이에 콤마를 삽입한 건 좋은데 제일 앞에 있는 콤마는 불필요하다. 전체 문자열에서 앞 부분 한 문자를 제거한다.
SELECT STUFF((SELECT ',' + TRIM(name) FROM tCity FOR XML PATH('')),1,1,'');
결과 : 부산,서울,순천,오산,전주,청주,춘천,홍천
SUBSTRING이나 RIGHT 함수를 쓸 수도 있지만 길이를 조사해야 하는 불편함이 있어 특정 위치에서 원하는 길이만큼 대체하는 STUFF 함수로 앞 첫 글자를 빈 문자열로 대체했다. 출력 결과를 STUFF로 감쌌으니 가공 후의 결과를 출력할 바깥쪽의 SELECT문이 하나 더 필요하다.
다음 쿼리는 지역별로 도시를 분류하여 보여 주고 도시명 다음에 공백도 하나 넣는다. 바깥쪽 SELECT문에서 tCity를 읽고 안쪽 서브쿼리에서 바깥쪽 tCity와 지역이 같은 도시명만 추출하여 콤마로 합친다. 상관 서브 쿼리이다. 그리고 지역별로 중복 없이 출력하였다.
SELECT DISTINCT region, STUFF(
(SELECT ',' + TRIM(name) + ' ' FROM tCity WHERE region = A.region FOR XML PATH('')
),1,1,'') AS B
FROM tCity AS A;
최종 결과는 다음과 같다. 깔끔하게 잘 출력되었다.
이런 쿼리를 한번에 만들어 내기는 어렵고 단계별로 안쪽부터 실행해 가며 하나씩 만들어 와야 한다. 이 문법은 MSSQL 전용이며 오라클이나 마리아는 지원하지 않는다.
돈 못 벌어도 좋다. 즐겁게 살면 된다.
|
|
