SQL을 이용한 DB2 XML 데이터 쿼리
난이도 : 초급
Cynthia M. Saracco, Senior Software Engineer, IBM
2006 년 12 월 26 일
DB2® 9(코드명 ‘Viper’)는 XML 데이터의 저장, 관리, 쿼리에 대한 새로운 지원 사항이 추가되었습니다. SQL과 SQL/XML을 사용하여 XML 칼럼에 저장된 데이터를 쿼리하는 방법을 배워봅시다. 후속 기술자료에서는 XQuery를 사용하여 XML 데이터를 쿼리하는 방법을 설명합니다.
DB2의 하이브리드(hybrid) 아키텍처는 이전 릴리스와는 많이 달라졌지만, 새로운 XML 기능을 활용하는 것은 그렇게 어려운 일은 아니다. SQL에 대해 잘 알고 있다면, DB2에 저장된 XML 데이터로 지금 즉시 작업할 수 있다.
DB2 9(코드명 ‘Viper’)의 XML 기능에는 새로운 스토리지 관리, 인덱싱, 쿼리 언어 지원이 포함된다. 이 글에서 SQL 과 SQL with XML extensions (SQL/XML)를 사용하여 DB2 XML 칼럼의 데이터를 쿼리하는 방법을 설명한다. 후속 기술자료에서는 XQuery에 대해 자세히 다룰 예정이다.
DB2가 Bilingual 쿼리(SQL과 XQuery의 식들을 결합한 쿼리)도 지원한다는 사실에 놀랄 것이다. 어떤 언어(또는 언어들의 조합)를 사용하든지 간에, 애플리케이션의 필요와 기술에 맞춰 선택해야 한다. 두 개의 쿼리 언어의 엘리먼트들을 하나의 쿼리로 결합하는 것은 생각만큼 어렵지 않다. 전통적인 SQL과 XML 데이터를 검색 및 통합하는 강력한 기능을 경험할 수 있을 것이다.
샘플 데이터베이스
이 글에서는 "DB2 Viper 시작하기 (한글)" (developerWorks, 2006년 7월)에서 만들었던 샘플 테이블에 액세스 할 것이다. 샘플 “아이템”과 “클라이언트” 테이블 샘플은 다음과 같이 정의된다.
Listing 1. 테이블 정의
create table items ( id int primary key not null, brandname varchar(30), itemname varchar(30), sku int, srp decimal(7,2), comments xml ) create table clients( id int primary key not null, name varchar(50), status varchar(10), contactinfo xml ) |
"items.comments" 칼럼에 포함된 XML 데이터는 그림 1에 나타나 있다. "clients.contactinfo" 칼럼에 포함된 XML 데이터는 그림 2를 참조하라. 그 뒤에 나오는 쿼리 예제들은 이 XML 문서들에 있는 특정 엘리먼트들을 참조할 것이다.
그림 1. "items" 테이블의 "comments" 칼럼에 저장된 샘플 XML 문서
그림 2. "clients" 테이블의 "contactinfo" 칼럼에 저장된 XML 문서 샘플
쿼리 환경
이 글의 모든 쿼리들은 대화형으로 실행된다. DB2 명령행 프로세서(CLP)나 DB2 Control Center의 DB2 Command Editor를 통해서 이를 수행할 수 있다. 본문의 스크린 이미지와 명령어는 나중에 설명하겠다. (DB2 Viper에는 Eclipse 기반 Developer Workbench가 포함된다. 이 글에서는 애플리케이션 개발 문제 또는 Developer Workbench에 대해서는 다루지 않겠다.)
DB2 Command Editor를 사용하려면, Control Center를 시작하고 Tools > Command Editor를 선택한다. 그림 3과 비슷한 창이 나타날 것이다. 위쪽 창에 쿼리 내용을 입력하고, 왼쪽 상단 코너에 있는 녹색 화살표를 클릭하여 실행하고, 아래쪽 창 또는 개별 "Query results" 탭에서 아웃풋을 본다.
그림 3. DB2 Control Center에서 시작한 DB2 Command Editor
순수한 SQL 쿼리
SQL에 대해 잘 모르더라도, XML 데이터를 쿼리 할 수 있다. 다음 쿼리로는, "contactinfo" 칼럼에 저장된 XML 정보를 포함하여, “clients” 테이블의 전체 콘텐트를 선택한다.
Listing 2. SELECT 문
물론, 관계형 투영(projection)과 제약(restriction) 연산을 결합한, 보다 선택적인 SQL 쿼리들을 작성할 수 있다. 다음 쿼리는 “Gold”로 되어있는 모든 고객들의 아이디, 이름, 연락처 정보를 검색한다. "contactinfo"에는 XML 데이터가 있지만, 다른 두 개의 칼럼에는 없다는 것에 주의하라.
Listing 3. 투영과 제약을 이용한 SELECT 문
select id, name, contactinfo from clients where status = 'Gold' |
아래 "goldview"에서 보듯, 이 같은 쿼리를 기반으로 뷰를 만들 수 있다.
Listing 4. XML 칼럼을 포함하고 있는 뷰 만들기
create view goldview as select id, name, contactinfo from clients where status = 'Gold' |
SQL만 가지고는 할 수 없는 많은 일들이 있다. 플레인 SQL 문으로 전체 XML 문서를 가져올 수 있지만, XML 기반 쿼리 술어(predicate)를 지정할 수 없고, 부분적인 XML 문서를 가져오거나 XML 문서에서 특정 엘리먼트 값을 가져올 수 없다. 다시 말해서, 플레인 SQL을 사용해서는 XML 문서 조각들의 투영, 제한, 조인(join), 조합(aggregate), 순서 정하기(order)를 할 수 없다. 예를 들어, Gold 고객들의 이메일 주소나 우편번호 "95116”에 살고 있는 클라이언트의 이름조차도 가져올 수 없다. 이러한 유형의 쿼리를 실행하려면, SQL with XML extensions (SQL/XML), XQuery, 또는 이 두 가지를 결합한 것을 사용해야 한다.
다음 섹션에서는 SQL/XML의 기본적인 여러 기능들을 설명하겠다. XQuery 작성 방법과 XQuery와 SQL을 결합하는 방법을 설명한다.
SQL/XML 쿼리
이름에서 암시하듯, SQL/XML은 SQL과 XML을 결합한 것이다. SQL 표준 작업의 일환이며, 이제는 SQL 문에 XQuery와 XPath 식을 삽입하는 스팩도 포함되어 있다. XPath는 XML 문서들을 검색하여 엘리먼트나 애트리뷰트를 찾는 언어이다.
XQuery(XPath) 식은 대/소문자를 가린다. 예를 들어, XML 엘리먼트 “zip”을 참조하는 XQuery는 "ZIP" 또는 "Zip" 이라는 이름의 XML 엘리먼트에는 적용되지 않을 것이다. 대/소문자 구분이 된다는 사실은 SQL 프로그래머가 기억하기 어렵기 때문에, SQL 쿼리 신택스에서는 "zip", "ZIP", "Zip"을 사용하여 같은 칼럼 이름을 참조하도록 하고 있다.
DB2 Viper는 15개 이상의 SQL/XML 함수들을 갖고 있다. 이것으로, XML 문서 내의 특정 데이터를 검색할 수 있고, 관계형 데이터를 XML로 변환하고, XML 데이터를 관계형 데이터로 변환하고, 기타 유용한 태스크들을 수행할 수 있다. 이 글에서는 SQL의 모든 부분을 설명하지는 않을 것이다. 일반적인 쿼리 문제들을 짚어보고, 핵심 SQL/XML 함수들이 그러한 문제들을 어떻게 해결하는가를 볼 것이다.
XML 엘리먼트 값에 따라 결과 “제한하기”
SQL 프로그래머들은 특정 조건에 기반하여 DBMS에서 리턴된 행을 제한하는 쿼리를 작성하곤 한다. 예를 들어, Listing 3의 SQL 쿼리는 “clients” 테이블에서 가져온 행을 제한하여 "Gold" 상태에 있는 고객들만 포함하도록 제한시켰다. 이 경우, 고객의 상태는 SQL VARCHAR 칼럼에 캡쳐된다. 하지만, XML 칼럼에 있는 데이터에 적용할 조건에 기반하여 검색을 제한하고 싶다면 어떻게 하겠는가? SQL/XML의 XMLExists 함수가 이를 해결할 수 있다.
XMLExists는 XML 문서 내에 있는 엘리먼트를 검색하고, 특정 조건에 맞추어 테스트를 한다. XMLExists가 WHERE문의 일부로서 지정되면, 이것은 리턴된 값을 특정 XML 엘리먼트 값을 갖고 있는 XML 문서를 포함한 행들로만 제한한다. (지정된 값은 “true”가 된다.)
앞서 발생했던 샘플 쿼리 문제에 대해 살펴보자. 특정 Zip Code에 살고 있는 모든 클라이언트의 이름을 배치해야 한다고 해보자. 기억하겠지만, “clients” 테이블은 XML 칼럼에 고객들의 주소(Zip Code 포함)를 저장한다. (그림 2) XMLExists를 사용하여, 목표 Zip Code에 대한 XML 칼럼을 검색하고, 리턴된 결과 세트를 이에 따라 제한할 수 있다. 다음 SQL/XML 쿼리는 Zip Code 95116에 살고 있는 클라이언트의 이름을 리턴한다.
Listing 5. XML 엘리먼트 값에 기반한 결과 제한
select name from clients where xmlexists('$c/Client/Address[zip="95116"]' passing clients.contactinfo as "c") |
첫 번째 라인은 “clients” 테이블의 "name” 칼럼에 있는 정보만 검색한다는 것을 지정하는 SQL 문이다. WHERE 문은 XMLExists 함수를 호출하면서, DB2가 “zip” 엘리먼트를 검색하고 95116 값을 체크하도록 XPath 식을 지정하고 있다. "$c/Client/Address" 문은 DB2가 “zip” 엘리먼트를 두는 곳인 XML 문서 계층에 있는 경로를 가리킨다. "$c" 노드에서 액세스 할 수 있는 데이터를 사용하여, DB2는 "Client" 엘리먼트와 "Address" 하위 엘리먼트를 검색하여 Zip Code(“zip” 값)을 찾는다. 마지막 라인은 "$c" 의 값을 해석한 것이다. “clients” 테이블의 "contactinfo" 칼럼이라는 의미이다. 따라서, DB2는 "contactinfo" 칼럼에 저장된 XML 데이터를 보고, 루트 "Client" 엘리먼트에서 "Address"로, 그리고 "zip”으로 검색하고, 그 고객이 목표 Zip Code에 살고 있는지를 결정한다. 만약 그렇다면, XMLExists 함수는 "true”로 연산되고, DB2는 그 행과 관련된 클라이언트의 이름을 리턴한다.
일반적으로 실수하는 것 중에는 XMLExists 쿼리 술어에 대한 수식도 포함된다. (Listing 6)
Listing 6. XML 엘리먼트 값에 기반하여 값을 제한하는 틀린 신택스
select name from clients where xmlexists('$c/Client/Address/zip="95116" ' passing clients.contactinfo as "c") |
쿼리는 성공적으로 실행되겠지만, 결과를 Zip Code 95116에 살고 있는 클라이언트로 제한하지 않는다. (표준에서 이렇게 정의하고 있다. DB2는 표준을 준수할 뿐이다.) 값을 Zip Code 95116에 살고 있는 클라이언트로 제한하려면, Listing 5에서 보았던 신택스를 사용해야 한다.
애플리케이션에서 XML 데이터를 제한하는 쿼리를 포함시키는 방법도 궁금할 것이다. 이 글에서는 애플리케이션 개발에 대해서는 자세히 설명하지 않을 것이다. 아웃풋을 주어진 Zip Code에 살고 있는 고객에 대한 정보로 제한하는 SQL/XML 문 내에 매개변수 마커를 사용하는 자바 예제가 포함되어 있다.
XML 엘리먼트 값의 “투영(Projecting)”
약간 다른 상황에 대해 생각해 보자. XML 값을 리턴된 결과 세트에 투영(project)한다고 해보자. 다시 말해서, 우리 XML 문서에서 한 개 이상의 엘리먼트 값을 가져와야 한다고 해보자. 여러 가지 방법들이 있다. 먼저, XMLQuery 함수를 사용하여 한 엘리먼트에 대한 값을 가져오고, XMLTable 함수를 사용하여 여러 엘리먼트에 대한 값을 가져오고, 이들을 SQL 결과 세트의 칼럼에 매핑시킨다.
이전에 발생했던 문제를 해결해 보자. Gold 고객들의 이메일 주소를 기록한 리포트를 만드는 방법에 대해 알아보자. Listing 7의 다음 쿼리는 XMLQuery 함수를 호출하여 이 태스크를 수행한다.
Listing 7. 해당 고객의 이메일 정보 가져오기
select xmlquery('$c/Client/email' passing contactinfo as "c") from clients where status = 'Gold' |
첫 번째 라인은 루트 "Client" 엘리먼트에서 "email" 하위 엘리먼트에 대한 값을 리턴 한다고 지정한다. 두 번째와 세 번째 라인에서는 DB2가 그 정보를 찾을 수 있는 곳을 가리킨다. "clients" 테이블의 "contactinfo" 칼럼이다. 네 번째 라인은 Gold 고객의 이메일 주소에만 관심이 있다는 것을 나타내고 있다. 이 쿼리는 XML 엘리먼트 세트와 값을 리턴한다. 예를 들어, 500 명의 Gold 고객들이 있으면, 각각 한 개의 이메일 주소가 있고, 아웃풋은 500개의 행을 가진 한 개 칼럼의 결과 세트가 될 것이다. (Listing 8)
Listing 8. 이전 쿼리에 대한 샘플 아웃풋
1 -------------------------------------------- <email>user5976@anyprovider.com</email> . . . <email>someID@yahoo.com</email> |
Gold 고객들이 각각 여러 개의 이메일 주소를 갖고 있을 경우, 주 이메일 주소만 리턴하도록 DB2에 명령할 수도 있다. (고객의 "contactinfo" 문서에서 찾은 첫 번째 이메일 주소) 이렇게 하려면, 쿼리의 첫 번째 라인에서 XPath 식을 변경한다.
Listing 9. 해당 고객의 첫 번째 이메일 주소 가져오기
select xmlquery('$c/Client/email[1]' passing contactinfo as "c") from clients where status = 'Gold' |
마지막으로, 일부 Gold 고객들의 이메일 주소가 없다면, 결과 세트에서 null을 배제하도록 쿼리를 작성해야 한다. 소실된 이메일 정보가 있는지 테스트하기 위해 WHERE 문에 또 다른 술어를 추가하여, 이전 쿼리를 수정한다. 이미 이렇게 할 수 있는 SQL/XML 함수는 익숙할 것이다. 바로 XMLExists이다. Listing 10은 이전 쿼리를 다시 써서, (XML로 저장된) 연락처 정보에 이메일 주소가 없는 Gold 고객들의 행을 거르는 방법이다.
Listing 10. 최소 한 개의 이메일 주소를 갖고 있는 해당 고객의 첫 번째 이메일 주소 가져오기
select xmlquery('$c/Client/email[1]' passing contactinfo as "c") from clients where status = 'Gold' and xmlexists('$c/Client/email' passing contactinfo as "c") |
약간 다른 상황을 생각해 보자. 여러 XML 엘리먼트 값을 가져와야 하는 경우를 생각해 보자. XMLTable은 XML 칼럼에 저장된 데이터에서 테이블 형태의 아웃풋을 만들고, 프로그래머들이 XML 데이터의 관계를 볼 수 있도록 한다. XMLExists와 XMLQuery와 마찬가지로, XMLTable 함수는 DB2가 XML 문서 계층을 검색하여 관심 있는 데이터를 배치하도록 한다. 하지만, XMLTable에는 대상 XML 데이터를 SQL 데이터 유형의 결과 세트 칼럼으로 매핑하는 문도 포함된다.
다음 쿼리(Listing 11)를 생각해 보자. "items" 테이블에 저장된 관계형 데이터와 XML 데이터에서 칼럼을 투영한다. (그림 1의 "items" 테이블 참조) comment ID, customer ID, 메시지가 "comments" 칼럼에 있는 XML 문서에 저장된다. 아이템 이름은 SQL VARCHAR 칼럼에 저장된다.
Listing 11. 여러 XML 엘리먼트를 가져와서 각각 SQL 데이터 유형으로 변환하기
select t.Comment#, i.itemname, t.CustomerID, Message from items i, xmltable('$c/Comments/Comment' passing i.comments as "c" columns Comment# integer path 'CommentID', CustomerID integer path 'CustomerID', Message varchar(100) path 'Message') as t |
첫 번째 라인은 결과 세트에 포함될 칼럼들을 지정하고 있다. 다음의 쿼리 라인이 가리키는 것처럼, 인용 부호가 있고 "t" 변수가 앞에 붙은 칼럼은 XML 엘리먼트 값에 기반하고 있다. 두 번째 라인은 XMLTable 함수를 호출하여 목표 데이터 ("i.comments")를 포함하고 있는 DB2 XML 칼럼을 지정하고, 해당 엘리먼트가 배치 (루트 "Comments" 엘리먼트의 "Comment" 하위 엘리먼트 내)되는 칼럼의 XML 문서 안의 경로를 지정한다. 세 번째 라인에서 다섯 번째 라인까지 이어지는 "columns" 문은 첫 번째 라인에서 지정된 SQL 결과 세트에 있는 아웃풋 칼럼으로 매핑 될 XML 엘리먼트를 구분한다. 매핑의 일부는 XML 엘리먼트 값이 변환될 데이터 유형들을 지정한다. 이 예제에서, 모든 XML 데이터는 SQL 데이터 유형들로 변환된다.
그림 4는 이 쿼리를 실행한 결과이다. 아웃풋은 단순한 SQL 결과 세트이다. 칼럼 이름들이 대문자로 표시되는데, 이는 SQL에서는 흔히 있는 일이다.
그림 4. XMLTable 함수를 사용한 쿼리의 결과
XMLTable을 사용하여 XML 칼럼도 포함한 결과 세트를 만들 수 있다. 예를 들어, 다음 문은 이전 것과 비슷한 결과 세트를 만든다. 단, "Message" 데이터가 SQL VARCHAR 칼럼이 아닌 XML 칼럼에 포함되는 것만 다르다.
Listing 12. 여러 XML 엘리먼트를 가져와서, 이들을 SQL 또는 XML 데이터 유형으로 변환하기
select t.Comment#, i.itemname, t.CustomerID, Message from items i, xmltable('$c/Comments/Comment' passing i.comments as "c" columns Comment# integer path 'CommentID', CustomerID integer path 'CustomerID', Message XML by ref path 'Message') as t |
XML 데이터의 관계 뷰 만들기
SQL/XML 함수들은 뷰를 정의하는데도 사용된다. SQL 애플리케이션 프로그래머들에게 XML 데이터의 관계 모델을 보여줄 때 특히 유용하다.
XML 칼럼에 있는 데이터들의 관계 뷰를 만드는 것은 XML 엘리먼트 값을 투영하는 것 보다 복잡하지 않다. XMLTable 함수를 호출하는 SQL/XML SELECT 문을 작성하고, 이것을 뷰 정의의 기초로 사용한다. Listing 13에 나와있는 예제는 "items" 테이블의 XML 및 비 XML 칼럼에 있는 정보에 기반한 뷰를 만든다. (Listing 11의 쿼리와 비슷하다.)
Listing 13. XMLTable의 결과에 기반하여 뷰 만들기
create view commentview(itemID, itemname, commentID, message, mustrespond) as select i.id, i.itemname, t.CommentID, t.Message, t.ResponseRequested from items i, xmltable('$c/Comments/Comment' passing i.comments as "c" columns CommentID integer path 'CommentID', Message varchar(100) path 'Message', ResponseRequested varchar(100) path 'ResponseRequested') as t; |
XML 칼럼 데이터에 대한 관계 뷰를 만드는 것은 쉽지만, 사용에 대해서는 신중하게 생각해야 한다. 이 같은 뷰에 대해 쿼리가 실행될 때 DB2는 XML 칼럼 인덱스를 사용하지 않는다. 따라서, ResponseRequested 엘리먼트를 인덱싱 하고, "mustrespond" 칼럼의 결과를 특정 값으로 제한하는 SQL 쿼리를 실행한다면, DB2는 모든 XML 문서를 읽고 알맞은 "ResponseRequested" 값을 찾을 것이다. 데이터 양이 많다면 런타임 성능이 느려진다. 하지만, 쿼리에 전통적인 SQL 유형(이 예제에서는 "i.id" 또는 "i.itemname")의 칼럼을 포함한 매우 제한적인 술어가 포함된다면, 결과 셋이 적어지기 때문에 성능이 많이 악화되는 것을 막을 수 있다. DB2는 관계형 인덱스를 사용하여 해당 행들을 작은 수로 필터링 하고, 최종 결과 세트를 리턴하기 전에, 추가 XML 쿼리 술어를 중간 결과에 적용한다.
XML과 관계형 데이터 조인(join)
XML 데이터를 비 XML 데이터(예를 들어, 전통적인 SQL 유형에 기반한 관계형 데이터)와 합치는(join) 것도 궁금할 것이다. DB2에서는 SQL/XML 문으로 이를 수행한다. 데이터베이스 스키마와 위크로드에 따라, 조인(join)을 수행하는 여러가지 방법들이 있지만, 여기에서는 한 가지 예제만 설명하겠다. 사실상, 여러분은 이 작업의 수행을 통해 SQL/XML에 대해 이미 알고 있음을 인지하게 될 것이다.
"items" 테이블에 있는 XML 칼럼에 "CustomerID" 엘리먼트가 포함된다는 것을 기억하는가? 이것이 "clients" 테이블의 정수 기반 "id" 칼럼에 대한 조인 키(join key)로서 작동할 수 있다. 한 개 이상의 제품들에 대해 코멘트를 달았던 고객의 이름과 상태에 대한 리포트가 필요하면, 한 테이블에서 나온 XML 엘리먼트 값을 다른 테이블에서 나온 SQL 정수 값과 합쳐야 한다. 한 가지 방법은 XMLExists 함수를 사용하는 것이다. (Listing 14)
Listing 14. XML과 비 XML 데이터 합치기
select clients.name, clients.status from items, clients where xmlexists('$c/Comments/Comment[CustomerID=$p]' passing items.comments as "c", clients.id as "p") |
첫 번째 라인은 쿼리 결과 세트와 쿼리에서 참조되는 소스 테이블에 포함될 SQL 칼럼을 지정한다. 두 번째 라인에는 join 문이 포함된다. XMLExists는 한 개의 목표 소스에 있는 "CustomerID" 값이 또 다른 목표 소스에서 가져온 값과 같은지를 결정한다. 세 번째 라인은 이러한 소스들을 지정한다. 첫 번째는 “items” 테이블에 있는 "comments" XML 칼럼이고, 두 번째는 "clients" 테이블의 정수 "id" 칼럼이다. 따라서, 고객이 아이템에 대해 코멘트를 달았고 이 고객에 대한 정보가 "clients" 테이블에서 사용할 수 있다면, XMLExists 식은 "true" 가 되고 클라이언트의 이름과 상태 정보가 그 리포트에 포함될 것이다.
SQL/XML에서 "FLWOR" 식 사용하기
일부 함수들만 설명했지만, SQL/XML은 XML 데이터를 쿼리하고, 그 데이터를 관계형 데이터로 통합하는 강력한 기능들을 갖고 있다. 몇 가지 예제를 통해 이를 수행하는 방법을 배웠지만, 이 부분에서는 더 자세히 설명하도록 하겠다.
XMLExists와 XMLQuery 함수를 사용하여 XQuery를 SQL로 결합할 수 있다. 이전 예제에서는 간단한 XPath 식으로 이러한 함수들을 사용하여 해당 XML 문서 부분을 검색하는 방법을 설명했다. 이제는, XQuery를 SQL 쿼리에 포함시키는 예제를 생각해 보자.
XQuery에는 "for," "let," "where," "order by", "return" 문들의 일부 또는 전체가 포함된다. 이들이 모여서 FLWOR (flower로 발음함) 식을 만든다. SQL 프로그래머들은 XQuery를 SELECT 리스트로 결합하여 XML 문서 조각들을 결과 세트로 추출(또는 투영)하는 것이 간단하다는 것을 깨닫게 될 것이다. 이것이 XMLQuery 함수가 사용되는 유일한 방법은 아니지만, 이 글에서는 이 부분에 대해서만 설명하겠다. (다음 글에서 XQuery에 대해 자세히 설명하겠다.)
"Gold" 고객들의 이름과 기본 이메일 주소를 가져와야 한다. 이 작업은 XML 엘리먼트 값을 투영하는 방법과 비슷하다. (Listing 9) 여기에서는 인풋으로 XQuery("for"와 "return" 문과 함께)를 XMLQuery 함수로 전달한다.
Listing 15. XQuery의 "for"와 "return" 문을 사용하여 XML 데이터 가져오기
select name, xmlquery('for $e in $c/Client/email[1] return $e' passing contactinfo as "c") from clients where status = 'Gold' |
첫 번째 라인은 XMLQuery 함수에서 온 고객 이름과 아웃풋이 결과 세트에 포함되어야 할 것을 지정하고 있다. 두 번째 라인은 "Client" 엘리먼트의 하위 엘리먼트인 "email"이 리턴 되어야 한다고 지정하고 있다. 세 번째 라인은 XML 데이터의 소스인 "contactinfo" 칼럼을 가리키고 있다. 네 번째 라인은 이 칼럼이 "clients” 테이블에 있다는 것을 알려준다. 마지막으로, 다섯 번째 라인은 “Gold” 고객들만 해당한다는 것을 나타내고 있다.
이 예제는 매우 간단하기 때문에, 여러분도 같은 쿼리를 작성할 수 있다. 대신, 더 간소하게 같은 쿼리를 작성할 수 있다.
Listing 16. 이전 쿼리를 더 간단하게 작성하기
select name, xmlquery('$c/Client/email[1]' passing contactinfo as "c") from clients where status = 'Gold' |
XQuery의 return 문으로 XML 아웃풋을 변형할 수 있다. 예를 들어, 이메일 엘리먼트 값을 추출하여 이들을 HTML로 표현할 수 있다. 다음 쿼리는 각 Gold 고객들의 첫 번째 이메일 주소가 HTML로 리턴된 결과 세트를 만들어 낼 것이다.
Listing 17. XML을 HTML로 변형하기
select xmlquery('for $e in $c/Client/email[1]/text() return <p>{$e}</p>' passing contactinfo as "c") from clients where status = 'Gold' |
첫 번째 라인은 해당 고객의 첫 번째 이메일 주소의 텍스트 표현에 관심이 있다는 것을 나타내고 있다. 두 번째 라인은, 이 정보가 리턴 되기 전에 HTML 태그로 되어 있어야 한다고 지정하고 있다. 특히, 중괄호( { } )는 리터럴 스트링으로서 취급하기 보다는 식("$e")을 계산하도록 명령하고 있다. 중괄호를 생략한다면, DB2는 모든 해당 고객 레코드에 대해 "<p>$e</p>"를 포함하고 있는 결과 세트를 리턴할 것이다.
XML을 관계형 데이터로서 공개(publish)하기
지금까지는 DB2 XML 칼럼에 저장된 데이터를 쿼리, 추출, 변형하는 방법을 설명했다. 이러한 기능들은 SQL/XML을 사용하면 모두 가능하다.
SQL/XML은 다른 기능들도 제공한다. 이 중에서 관계형 데이터를 XML로 변환 및 공개(publish)하는 기능이 있다. 여기에서 다룰 함수들은 XMLElement, XMLAgg, XMLForest이다.
XMLElement는 SQL 칼럼에 저장된 데이터를 XML 조각들로 변환한다. (XML 애트리뷰트가 있거나 또는 없는) XML 엘리먼트를 기본 SQL 데이터에서 구현할 수 있다. 다음 예제는 XMLElement 함수를 사용하여 아이템 엘리먼트를 만드는 예제이다. 각각 "items" 테이블에서 가져온 아이디, 브랜드 이름, stock keeping unit ("sku") 하위 엘리먼트 값이 있다.
Listing 18. XMLElement를 사용하여 관계형 데이터를 XML로 전개하기
select xmlelement (name "item", xmlelement (name "id", id), xmlelement (name "brand", brandname), xmlelement (name "sku", sku) ) from items where srp < 100 |
이 쿼리를 실행하면 다음과 같은 결과가 나온다.
Listing 19. 이전 쿼리의 결과
<item> <id>4272</id> <brand>Classy</brand> <sku>981140</sku> </item> . . . <item> <id>1193</id> <brand>Natural</brand> <sku>557813</sku> </item> |
XMLElement를 다른 SQL/XML 퍼블리싱 함수들과 결합하여 XML 값들을 구현 및 그룹핑 하고, 원하는 대로 계층에 포함시킬 수 있다. Listing 20의 예제는 XMLElement를 사용하여 customerList 엘리먼트를 만든다. 이것의 콘텐트는 "status" 칼럼에 있는 값들로 그룹핑 된다. 각 "customerList" 레코드에서, XMLAgg 함수는 일련의 고객 엘리먼트를 리턴하는데, 각각 "name"과 "status" 칼럼에 기반하여 하위 엘리먼트를 포함하고 있다. 그리고, 고객 엘리먼트 값은 고객 이름 순서대로 되어있다.
Listing 20. 데이터 모으기 및 그룹핑
select xmlelement(name "customerList", xmlagg (xmlelement (name "customer", xmlforest (name as "fullName", status as "status") ) order by name ) ) from clients group by status |
"clients" 테이블에 세 개의 "status" 값들이 있다고 생각해 보자. 각각 "Gold”, "Silver”, "Standard” 이다. 이전 쿼리를 실행하면 DB2는 세 개의 customerList 엘리먼트를 리턴할 것이다. 이때, 각각 이름과 상태 정보를 포함하고 있는 고객의 하위 엘리먼트들이 있을 것이다. 따라서 결과는 다음과 같다.
Listing 21. 이전 쿼리의 결과
<customerList> <customer> <fullName>Chris Bontempo</fullname> <status>Gold</status> </customer> <customer> <fullName>Ella Kimpton</fullName> <status>Gold</status> </customer> . . . </customerList> <customerList> <customer> <fullName>Lisa Hansen</fullName> <status>Silver</status> </customer> . . . </customerList> <customerList> <customer> <fullName>Rita Gomez</fullName> <status>Standard</status> </customer> . . . </customerList> |
Update 및 Delete 연산
이 글의 목적은 SQL을 사용하여 XML 칼럼에 저장된 데이터를 검색 및 가져오는 방법을 설명하는 것이지만, XML 칼럼에 있는 데이터를 업데이트 및 삭제하는 방법도 알아 둘 필요가 있다.
DB2에서는 SQL과 SQL/XML 문을 사용하여 XML 데이터를 업데이트 및 삭제할 수 있다. 실제로, XQuery 표준의 초안은 이 문제를 다루지 않기 때문에, DB2 사용자들은 이러한 태스크를 수행할 때 SQL에 의지해야만 한다.
XML 데이터 업데이트
DB2는 SQL UPDATE 문 또는 시스템에서 제공하는 저장 프로시저(DB2XMLFUNCTIONS.XMLUPDATE)를 사용하여 XML 칼럼을 업데이트 한다. 두 경우 모두, XML 칼럼 업데이트는 엘리먼트 레벨이 아닌 문서 레벨에서 수행한다. 하지만 저장 프로시저를 사용하여 업데이트 하는 프로그래머들은 전체 XML 문서를 DB2에 제공할 필요가 없다. 업데이트 될 XML 엘리먼트를 지정하고, DB2는 변경되지 않은 문서 데이터를 보존하고, 지정된 엘리먼트만 업데이트 하면 된다. UPDATE 문을 실행하는 프로그래머는 전체 문서를 지정해야 한다. (변경하고자 하는 엘리먼트만 지정하는 것이 아니다.)
예를 들어, 특정 고객의 연락처 정보에서 이메일 주소를 변경하기 위해 UPDATE 문을 실행해야 한다면, 새로운 이메일 엘리먼트 값뿐만 아니라, XML 칼럼에 포함된 전체 연락처 정보를 제공해야 한다. 그림 2를 보면, "Address" 정보, "phone" 정보, "fax" 정보, "email" 정보가 포함되어 있다.
다음 문을 보자.
Listing 22. UPDATE 문 샘플
update clients set contactinfo=( xmlparse(document '<email>newemail@someplace.com</email>' ) ) where id = 3227 |
“DB2 Viper 시작하기 (한글)” 에서 XML 데이터를 삽입하는 방법을 떠올려 보면, 유사하다는 것을 알 수 있다. 다른 SQL UPDATE 문과 마찬가지로, 이 예제는 업데이트 될 테이블과 칼럼을 먼저 지정하고 있다. 목표 칼럼에 XML 데이터가 포함되기 때문에, 새로운 목표 값으로 완성된 XML 문서를 주어야 한다. 대부분의 실행 환경은 호스트 변수 또는 매개변수 마커를 애플리케이션에 사용하여 XML 데이터를 업데이트 하는 반면, 나는 여기에서 이를 번갈아 가며 수행하는 방법을 설명했다. 두 번째 라인은 XMLParse 함수를 사용하여 인풋 스트링을 XML로 변환한다. 마지막 라인은 업데이트를 테이블의 특정 행으로 제한하는 표준 SQL 문이다.
앞의 UPDATE 문을 실행하면, 3227 고객의 "contactinfo" 칼럼에 이메일 정보만 포함된다. (Listing 23)
Listing 23. UPDATE 문 실행 결과
<email>newemail@someplace.com</email> |
고객의 주소, 전화 번호, 팩스 번호는 소실된다. (그림 2) 게다가, 고객의 이메일 주소를 추출하기 위해 작성했던 이전 쿼리 일부가 이것을 찾아내지 못한다. 이전 쿼리에는 특정 문서 계층을 통해 검색했던 XPath 또는 XQuery 식이 포함되었기 때문이다. Client는 루트 엘리먼트였고 이메일은 하위 엘리먼트였다. 이 문서를 업데이트 한 후에는, 이메일은 이제 이 고객의 XML 레코드의 루트 엘리먼트가 된다. 따라서, 그 계층의 위치에서는 값을 찾을 수 없는 것이다.
고객의 이메일 주소를 업데이트 하고, 기존의 다른 연락처 정보는 유지하고 싶다면 쿼리를 다시 작성해야 한다. (Listing 24)
Listing 24. UPDATE 문 수정
update clients set contactinfo= (xmlparse(document '‘<Client> <Address> <street>5401 Julio Ave.</street> <city>San Jose</city> <state>CA</state> <zip>95116</zip> </Address> <phone> <work>4084633000</work> <home>4081111111</home> <cell>4082222222</cell> </phone> <fax>4087776666</fax> <email>newemail@someplace.com</email> </Client>' ) ) where id = 3227 |
전체 XML 문서를 주지 않고 뷰를 통해 업데이트 하는 방법이 궁금할 것이다. 예를 들어, Listing 13에서 정의된 commentview는 XMLTable 함수를 사용하여 XML 칼럼의 특정 엘리먼트를 추출하고, 이들을 뷰에 있는 SQL 칼럼으로 변형한다. SQL 칼럼들 중 한 개의 값을 업데이트 하고, 그 결과를 원래 XML 문서의 정확한 하위 엘리먼트에 작성하는 것이 가능한가? 그렇지 않다. DB2는 SQL 유형에 기반한 뷰 칼럼과 함수(이 경우, XMLTable 함수)의 결과에서 가져온 뷰 칼럼을 구분한다. 후자의 업데이트 방식은 지원되지 않는다.
XML 데이터 삭제하기
XML 칼럼을 포함한 행을 삭제하는 것은 간단하다. SQL DELETE 문을 사용하여 WHERE 문에서 삭제하고 싶은 행을 지정(또는 제한)할 수 있다. 이 문에는 XML 칼럼 값이나 SQL/XML 함수들을 지정하는 술어가 포함되어 XML 칼럼 내에 포함된 XML 엘리먼트 값을 구분하고 있다.
다음은 고객 ID 3227에 대한 모든 고객 정보를 삭제하는 방법이다.
Listing 25. 특정 클라이언트의 데이터 삭제하기
delete from clients where id = 3227 |
Zip Code 95116에 살고 있는 고객들에 대한 행만 리턴하도록 SQL SELECT 문을 제한하는 방법을 기억하는가? 그렇다면, 그러한 고객들을 트래킹 하는 행을 삭제하는데도 적용할 수 있다. 다음은 XMLExists를 사용하는 방법이다.
Listing 26. 특정 Zip Code 내에 있는 클라이언트용 데이터 삭제하기
delete from clients where xmlexists('$c/Client/Address[zip="95116"]' passing clients.contactinfo as "c"); |
인덱싱(Indexing)
마지막으로, 특별한 XML 인덱스를 만들어서 XML 칼럼에 저장된 데이터로의 액세스 속도를 높이는 방법도 있다. 이 글은 Indexing에 대한 개요이고, 샘플 데이터도 작기 때문에 이 부분은 설명하지 않겠다. 하지만, 실행 환경에서, 알맞은 인덱스를 정의하는 것은 최적의 성능을 얻는데 있어 중요한 요소이다. 참고자료 섹션에서 새로운 DB2 인덱싱 기술을 참조하기 바란다.
요약
SQL/XML의 핵심 부분들과, 이를 사용하여 XML 칼럼에 있는 데이터를 쿼리하는 방법을 설명했다. 여기에서 설명한 것 이상으로 SQL과 SQL/XML 함수들을 사용하여 할 수 있는 일들이 많이 있다. 본문에 포함된 자바 예제들을 통해 SQL/XML의 매개변수 마커를 사용하여 XML 칼럼에 있는 데이터를 쿼리하는 방법을 설명했다. 향후 기술자료에서는 애플리케이션 개발에 대해 자세히 설명하도록 하겠다. 물론, DB2 Viper에서 지원되는 새로운 쿼리 언어인 XQuery에 대해서도 설명할 것이다.
감사의 말
도움을 주신 George Lapis, Matthias Nicola, Sriram Padmanabhan, Gary Robinson, Hardeep Singh, Bert Van der Linden에게 감사의 말을 전하고 싶다.
기사의 원문보기
참고자료
교육
- DB2 Viper Web site
- "What's new in DB2 Viper: XML to the Core" (developerWorks, February 2006)
- "DB2 Viper 시작하기 (한글)" (developerWorks, 2006년 7월)
- "Firing up the Hybrid Engine" (DB2 Magazine, Quarter 3, 2005)
- System RX: One Part Relational, One Part XML (SIGMOD 2005 Conference)
- "Native XML Support in DB2 Universal Database" (VLDB conference, 2005)
- "Managing XML for Maximum Return" (IBM, November 2005)
- (Howard Katz, et. al., Addison-Wesley, 2003): book excerpt
- developerWorks Information Management zone (한글페이지)
- developerWorks technical events and webcasts
- DB2 Express-C
제품 및 기술 얻기
토론
필자소개
|
|
|
|
|
C. M. Saracco는 IBM Silicon Valley Laboratory의 DB2 XML 분야에서 일하고 있다. 데이터베이스 관리, XML, 웹 애플리케이션 개발을 수행하고 있다. |
출처 : 한국 developerWorks
