MSSQL에서 XML 데이터를 운용하는 레퍼런스를 간략하게나마 정리해보았다.
특히 상업적인 수요가 거의 없는 "인문학데이터를 XML을 기반으로 만들어서 RDB에서 사용"하기 위한 방법론 요약정리이다.
+ XML 기반 관계형 데이터베이스 설계의 3원칙
1. 단위 노드의 XML 문서는 더 이상 분해하지 않고, 하나의 XML 컬럼에 적재한다.
단위 노드는 일반적으로 고유한 ID 값을 가지는 항목이면서 독립적인 관리의 대상이다.
예를 들어서 종이사전의 하나하나의 명사("계절", "가구" 등)가 하나의 "단위 노드"가 된다. 물론 A로 시작하는 모든 명사("air, att....")를 "단위 노드"로 설정해도 무방하나, 실무에서 원고위탁등의 행위을 할 때 일반적으로 하나의 명사 단위로 관리를 하기 때문에 하나하나의 명사가 "단위 노드"가 되는 것이 관리하기 편하다.
2. 주요 메타데이터 요소는 별도 테이블의 독립 컬럼 데이터로 관리한다.
일반적으로 자주 쓰이는 메타데이터 요소(ID, 이름 등)은 작업의 편이성을 위해서 독립적인 테이블로 만드는 것이 편하다. 또한 특정 서비스(갤러리, 지리정보서비스)의 구현을 위해서 필요한 데이터만을 추출하여 독립적인 테이블로 만드는 것도 작업의 편이성을 보장한다.
다만 3번 항목을 충족시키기 위하여 View 테이블을 사용하는 것을 강력하게 권장한다.
3. XML 본문 컬럼과 메타데이터 컬럼의 데이터는 항상 일치되도록 한다.
XML 데이터에서 자주 사용되는 데이터를 View 테이블이 아닌 일반 테이블로 만들 경우 XML 데이터와 서비스되는 데이터 간의 상이성이 발생한다. 데이터 무결성은 DB 관리의 기본이지만, 기본이 지켜지지 않는 경우가 왕왕 발생하기에 다시 한번 강조된다.
정말 왠만하면 View 테이블을 사용하라. 물론 View가 아닌 트리거를 사용할 수도 있다. 그러나 시스템부하나 일반적인 서비스환경을 생각하면 역시 View 기능을 사용하는 것이 비교적 합리적이다.
+ XML 데이터 입력
INSERT INTO (테이블명)((컬럼명)) VALUES(
CONVERT(xml, -- XML이라고 명시
N' -- 유니코드 명시
(XML 데이터)
', 1) -- data_type 설정(data_type 참고)
)
* CONVERT xml data_type(http://msdn.microsoft.com/ko-kr/library/ms187928.aspx)
0(기본값) : 불필요한 공백을 삭제하고 내부 DTD 하위 집합을 허용하지 않는 기본 구문 분석 동작을 사용합니다.
1 : 불필요한 공백을 유지합니다. 이 스타일 설정에 따라 xml:space="preserve"가 대신 지정된 경우와 동일하게 동작하도록 기본 xml:space 처리가 설정됩니다.
2 : 제한된 내부 DTD 하위 집합 처리를 설정합니다
3 : 불필요한 공백을 유지하고 제한된 내부 DTD 하위 집합 처리를 설정합니다.
+ XML 스키마 컬렉션
--스키마 만들기
CREATE XML SCHEMA COLLECTION 스키마명 AS '
XML스키마내용
'
-- 스키마 사용
CREATE TABLE 테이블명 (
컬럼명 속성값 IDENTITY PRIMARY KEY,
컬럼명 xml 스키마명 -- XML 스키마 컬렉션 지정
)
+ XML 데이터 조작 메소드
query() : XML 인스턴스에 대해 쿼리 : 검색된 XML 인스턴스
value() : XML 인스턴스에서 값을 검색 : 검색된 XML 인스턴스의 값
exist() : NULL이 아닌 결과를 반환하는지 여부를 확인 : TRUE/FALSE
modify() : XML 데이터의 업데이트를 수행하도록 DML 문을 지정 : 반환값 없음
nodes() : 여러개의 XML 인스턴스를 여러개의 행으로 반환 :노드별로 추출된 XML 인스턴스를 담은 행 집합(테이블)
1. 기본적인 query()
select 칼럼명.query('/') -- 루트노드의 XML값 반환
select 칼럼명.query('/노드A') -- 자식노드 A의 XML값 반환
select 칼럼명.query('/노드A/노드B') -- 자식노드 B의 XML값 반환
select 칼럼명.query('/노드A/@속성B') -- 자식노드 A의 속성 B의 XML값 반환
2. 상대경로 지정 query()
select 칼럼명.query('/노드A/노드B/.') -- 자식노드 B 자체 XML값 반환
select 칼럼명.query('/노드A/노드B/..') -- 자식노드 B의 상위노드(A노드)의 XML값 반환
3. 재귀적내림 연산자 query()
select 칼럼명.query('//노드A') -- 모든 A 노드의 XML값 반환
select 칼럼명.query('//노드A[1]') -- 문서내 1번째 A 노드의 XML값 반환
select 칼럼명.query('//노드A[2]') -- 문서내 2번째 A 노드의 XML값 반환
select 칼럼명.query('//@속성A') -- 문서내 모든 속성A의 XML값 반환
4. 필터 사용 query()
select 칼럼명.query('//노드A[노드B]') -- 노드 B를 포함한 모든 노드 A의 XML값 반환
select 칼럼명.query('//노드A[@속성B]') -- 속성 B를 포함한 모든 노드 A의 XML값 반환
select 칼럼명.query('/노드A/노드B[노드C]') -- 노드 C를 포함한 노드 A의 자식노드 B의 XML값 반환
select 칼럼명.query('//노드A[.="키워드B"]') -- 노드값이 B인 모든 노드 A의 XML값 반환
select 칼럼명.query('//노드A[@속성B="키워드C"]') -- 속성 B의 값이 C인 모든 노드 A의 XML값 반환
select 칼럼명.query('//노드A[노드B="키워드C"]') -- 노드B의 값이 C인 모든 노드 A의 XML값 반환
5. Xpath 함수사용 query()
select 칼럼명.query('/노드A/node()') -- 노드 A의 XML값을 반환
select 칼럼명.query('/노드A/text()') -- 노드 A의 모든 하위 노드를 제외한 XML값을 반환
select 칼럼명.query('/노드A[last()]') -- 마지막 노드 A의 XML 값을 반환
select 칼럼명.query('count(/노드A)') -- 노드 A의 출현횟수를 count한 XML 값을 반환
select 칼럼명.query('sum(노드A/@속성B)') -- 노드 A의 모든 속성B을 sum 한 XML 값을 반환
6. 기본적인 value()
select 칼럼명.value('(/노드A)[1]', 'nvarchar(100)') -- 1번째 자식노드 A의 값을 nvarchar(100) 형식으로 반환
select 칼럼명.value('(/노드A)[1]', 'real') -- 1번째 자식노드 A의 값을 real형식으로 반환
select 칼럼명.value('(/@속성A)[1]', 'real') -- 1번째 속성 A의 값을 real형식으로 반환
7. 여러개의 값 반환 value() + nodes()
SELECT 지정노드_임시명.value('/노드B', 'nvarchar(100)')
-- "지정노드_임시명"의 노드A의 위치의 모든 노드 B의 값을 nvarchar(100) 형식으로 반환
FROM 테이블명 CROSS APPLY 칼럼명.nodes('/노드A') AS R(지정노드_임시명)
- 노드A의 위치를 "지정노드_임시명"으로 저장
* 추천
text데이터(한글+한자) = nvarchar
geo(위경도)데이터 = real
8. 기본적인 exist()
-- exist()는 where 조건절에 쓰여서 특정값을 가져올 때 쓰인다
SELECT 칼럼명 FROM DB명 WHERE 칼럼명.exist('노드A') = (0 or 1)
9. 기본적인 modify()
9.1. replace
UPDATE DB명
SET 칼럼명.modify('replace value of (노드A())[1] with "입력값" ')
WHERE 조건값
9.2. insert
UPDATE DB명
SET 칼럼명.modify ('insert 입력값 after (노드A)[1]')
WHERE 조건값
9.3. delete
UPDATE DB명
SET 칼럼명.modify('delete 대상위치값[1]')
WHERE 조건값
% value()도 더 자세히 해야겠고 exist()나 modify()도 넣어야겠네.....나중에...언젠가?!
+ 실무작업 예시
-- [Create Table] 기본테이블 세팅
create table jeju_xml (
id nvarchar(10) NOT NULL, -- ID 컬럼, 기본키
xmltxt xml NOT NULL, -- XML 항목 2G까지 입력가능.
primary key(id)
)
-- [Trigger] XML 데이터를 입력했을 때 해당 XML 데이터에서 자동으로 ID값 추출
CREATE TRIGGER jeju_getid ON jeju_xml INSTEAD OF INSERT
AS
INSERT INTO jeju_xml(id, xmltxt)
SELECT
t.xmltxt.value('(/항목/@ID)[1]', 'nvarchar(10)') AS id,
t.xmltxt AS xmltxt
FROM inserted t
-- [Data Input] 예시데이터 입력
INSERT INTO jeju_xml(xmltxt) VALUES(
CONVERT(xml,
N'<항목 ID="2011-5-01">
<항목명>용연(龍淵)</항목명>
<메타데이터>
<일시>2011. 10. 10. 09:30</일시>
<주소>제주특별자치도 제주시 용담동</주소>
<위치>
<경도>126.5144225</경도>
<위도>33.51488194</위도>
<고도>26.1</고도>
</위치>
</메타데이터>
<본문>
첫 방문지. <지명>용연(龍淵)</지명>은 <지명>한천(漢川)</지명>이라는 이름의 하천이 바다와 만나는 지점. 양안이 절벽인 하천 하구에 바닷물이 들어 연못처럼 보인다. 옛날에는 <지명>제주</지명>에 부임한 관리들이 달밤에 이곳에서 배를 띄우고 놀았다고.... 지금은 연인들이 즐겨 찾는 여행 코스인 듯. 다리 난간 와이어에 이른바 「사랑의 자물쇠」를 채우는 일이 빈번해지자 시에서는 별도의 자물쇠 걸이를 설치하였다.
<삽도 url="L1080486m.jpg">용연(龍淵) 안내판</삽도>
<삽도 url="L1080477m.jpg">용연(龍淵)</삽도>
<삽도 url="IMG_9073.jpg">용연(龍淵)</삽도>
<삽도 url="IMG_9077.JPG">용연(龍淵) 석벽에는 목사 심원택이라 새겨져 있다.</삽도>
<삽도 url="L1080485m.jpg">시에서 설치한 자물쇠 걸이.</삽도>
</본문>
</항목>', 1)
)
select * from jeju_xml -- 입력 확인용
-- [Meta View] 기본정보 제공을 위한 view 테이블 생성
create view jeju_meta as
select
id,
xmltxt.value( '(/항목/항목명)[1]', 'nchar(80)' ) as 항목명,
xmltxt.value( '(/항목/메타데이터/일시)[1]', 'nchar(20)' ) as 일시,
xmltxt.value( '(/항목/메타데이터/주소)[1]', 'nchar(40)' ) as 주소
from jeju_xml
select * from jeju_meta -- 추출내용 확인용
-- [GIS View] GIS 서비스를 위한 view 테이블 생성
create view jeju_gis as
select
id,
xmltxt.value( '(/항목/메타데이터/위치/경도)[1]', 'real' ) as 경도,
xmltxt.value( '(/항목/메타데이터/위치/위도)[1]', 'real' ) as 위도,
xmltxt.value( '(/항목/메타데이터/위치/고도)[1]', 'real' ) as 고도
from jeju_xml
select * from jeju_gis -- 추출내용 확인용
-- [Photo View] 갤러리 서비스를 위한 view 테이블 생성
create view jeju_photo as
SELECT id as txt_id,
photo.value('./@url', 'nchar(40)' ) as 파일,
photo.value('.', 'nchar(100)' ) as 캡션
FROM jeju_xml CROSS APPLY xmltxt.nodes('/항목/본문/삽도') AS R(photo)
select * from jeju_photo -- 추출내용 확인용
-- [Keyword Index] 인덱스 서비스를 위한 인덱스 생성
-- 지명추출
create view jeju_place as
SELECT name.value('.', 'nchar(40)' ) as 지명, id as txt_id
FROM jeju_xml CROSS APPLY xmltxt.nodes('/항목/본문/지명') AS R(name)
-- 인덱스 추출
create view jeju_pndx as
select distinct 지명, count(*) as 빈도 from jeju_place group by 지명
select * from jeju_pndx order by 지명 -- 인덱스 정보 확인용
