| DBA: XML
SQL/XML의 이해
저자 - Tim Quinlan
SQL/XML의 연동 방법, 오라클 데이터베이스에 저장된 XML 문서를 조회, 업데이트하는 방법을 배워 보십시오.
게시일: 2006년 12월
XML 데이터는 오늘날의 기업 시스템 환경에서 널리 사용되고 있으며 데이터베이스 환경에서도 매우 중요한 역할을 담당하고 있습니다. 과거에는 관계형 데이터베이스에서 XML 데이터를 저장, 조회, 업데이트하기 위해 비구조형 LOB를 사용하거나 데이터를 관계형 테이블에 분산하여 저장하는 방법을 사용해야 했습니다. 이러한 방법은 복잡한 프로그래밍을 요구할 뿐 아니라 비효율적인 액세스 메커니즘을 사용한다는 문제가 있습니다.
Oracle9i Database Release 2에서 처음 소개된 Oracle XML DB는 XML 데이터를 데이터베이스에서 저장, 인출, 업데이트하기 위한 혁신적인 환경을 제공하였습니다. 하지만 XML 데이터가 BLOB 타입의 데이터로만 취급된다는 문제가 있었습니다. Oracle Database 10g Release 2 XML DB는 이러한 문제를 해결한, 전혀 새로운 환경을 구현하고 있습니다.
본 문서는 오라클 환경에서 XML을 처음 사용해 보는 DBA 및 개발자를 위해 작성되었습니다. 본 문서는 SQL, XML 호환성에 대한 개요 정보와 함께, Oracle 10g Release 2 데이터베이스에 저장된 XML 문서를 처리하는 방법, 또 데이터베이스에서 XML을 활용하기 위해 DBA와 개발자들이 반드시 알고 있어야 하는 Oracle Database 10g Release 2의 신기능을 소개하고 있습니다. 관계형 데이터베이스에 관련한 기존의 스킬과 SQL/XML 기능을 함께 조합함으로써 한층 개선된 운영 환경을 실현할 수 있을 것입니다.
오라클은 XML을 어떤 방법으로 구현하고 있습니까?
오라클은 Oracle Database Enterprise Edition과 Standard Edition에 기본적으로 포함된 XML DB 컴포넌트를 통해 XML을 지원하고 있습니다. Oracle XML DB는 Database Configuration Assistant(DBCA)를 통해, 또는 catqm.sql 카탈로그 스크립트를 통해 설치됩니다. XML DB는 본 문서에서 설명되는 모든 기능을 포함하고 있습니다.
XML DB가 설치되었는지 확인하는 방법이 아래와 같습니다:
select comp_name from dba_registry where comp_name like '%XML%';
COMP_NAME
Oracle XML Database
또는 XML DB 스키마와 오브젝트를 조회해서? Oracle XML DB의 설치 여부를 확인할 수도 있습니다.
SQL과 XML의 연동
SQL/XML 표준은 관계형 쿼리로부터 XML을 생성하거나, 또는 역으로 XML 문서로부터 SQL 데이터를 생성하기 위한 메커니즘을 제공하기 위한 목적에서 개발되어 왔습니다. Oracle Database 10g Release 2의 Oracle XML DB는 SQL 2003 표준과 향후 공개될 SQL/XML 표준의 기능을 구현하고 있습니다. 오라클은 이러한 표준을 통해, 기존의 SQL 표준에서는 전혀 불가능했던 방법으로 XML 문서를 조회할 수 있도록 지원하고 있습니다.
새로운데이터타입의지원. Oracle9i Database Release 1에서 처음 소개된 XMLType은 SQL을 통해 데이터베이스에 저장된 XML 문서를 조회하고, 동시에 XML 개발자들이 문서에서 XML 표준을 활용할 수 있게 합니다. XMLType 데이터타입은 데이터베이스의 컨텐트가 XML 포맷으로 저장되었음을 명시하고 XML 문서에 대해 쿼리를 수행하기 위한 환경을 제공합니다. 관계형 타입 또는 CLOB 데이터타입 대신 XMLType을 사용함으로써, 애플리케이션/스토리지 모델을 명확하게 분리할 수 있습니다. 따라서 데이터를 CLOB/관계형 모델에 바인딩하지 않고 별도의 스토리지 모델에서 데이터 이동 작업을 수행할 수 있습니다. 테이블, 컬럼, 뷰를 생성할 때 XMLType을 이용할 수도 있습니다. 또 매개변수 및 변수를 위한 데이터타입으로 활용하는 것도 가능합니다.
빌트인 XML 메소드는 문서에 저장된 XML 데이터의 생성, 추출, 인덱싱을 위해 사용됩니다. 인덱싱의 경우 B-트리, 텍스트 인덱싱, 함수 기반 인덱스 등을 사용할 수 있습니다. XMLType 데이터와 XPath 액세스를 조합하여 문서를 조회할 수도 있습니다. 이 기능은 PL/SQL, Java API 등을 통해 지원됩니다. XMLType은 PL/SQL, (JDBC를 이용한) Java, Oracle Data Provider for .Net 등의 환경에서 사용될 수 있습니다. 본 문서에서는 XMLType의 활용 방법에 대해 자세히 설명하고 있습니다.
스토리지구조. 오라클 XML 환경은 비구조형/구조형의 두 가지 저장 방법을 지원합니다. XMLType 데이터를 단일 LOB 컬럼에 저장하는 경우에는 비구조형으로, 오브젝트 셋의 형태로 구현하는 경우에는 구조형으로 저장됩니다.
XMLType 테이블 역시 두 가지 스토리지 모델 중 하나를 선택할 수 있습니다. XMLType을 비구조형 스토리지에 저장하려면 아래와 같이 "XMLType store as CLOB" 구문을 사용합니다.
create table table_name of XMLtype
XMLType store as CLOB;
XML 데이터의 구조형/비구조형 저장 방식에 대해 좀 더 자세히 알아보겠습니다.
구조형스토리지. 구조형 XML 스토리지는 일련의 오브젝트로 구현됩니다. 이 오브젝트는 테이블에 관계형 포맷으로 구현되며, 각 오브젝트는 참조 제약조건을 통해 서로 연결됩니다. 또 XMLSchema를 이용하여 XMLType 문서를 여러 개의 오브젝트에 분할(decompose)할 수도 있습니다.
이때 XML 데이터의 저장을 위한 관계형 테이블을 새로이 생성할 수도 있고, 또는 기존의 테이블을 그대로 활용할 수도 있습니다. 문서는 관계형 테이블에 "가상" 문서로 저장되며, 문서의 논리적 구조는 그대로 보존됩니다. 이러한 접근법은 문서의 바이트 단위 물리적 표현에 대응되지는 않으나, Document Object Model(DOM) 무결성(fidelity)을 보장합니다. 이를 위해 기존 관계형 데이터에 XMLType 뷰를 생성할 수도 있습니다.
구조형 스토리지는 비구조형 스토리지에 비해 성능적인 이점을 제공합니다. 또 테이블 및 인덱스 설계를 통해 쿼리/업데이트 작업의 최적화를 수행할 수 있습니다. XML 환경을 구조형 스토리지로 구현하는 경우 XML 태그를 데이터베이스에 저장할 필요가 없으며, 따라서 메모리/스토리지 용량의 절감이 가능합니다. B-트리, 함수 기반 인덱스를 이용하여 인덱스 성능을 개선하고, XPath rewrite 기능을 이용하여 분할된 문서에 "in-place" 업데이트를 수행할 수 있습니다.?
구조형 스토리지에는 단점도 있습니다. 먼저, 전체 문서의 삽입/인출 작업에 더 많은 오버헤드가 수반됩니다. 또 XMLSchema에 대응되는 문서만 저장할 수 있으므로 유연성에도 제약이 따릅니다(하지만 이것이 오히려 장점이 되는 경우도 있습니다). 이 문서는 원본과 물리적으로 동일한 형태로 저장되지 않으며, 따라서 문서의 데이터 순서는 보장되지 않습니다. 하지만 데이터는 어떠한 경우에도 손실되지 않음을 보장할 수 있습니다.
비구조형스토리지. XML의 비구조형 스토리지는 CLOB 데이터타입을 통해 구현됩니다. 이 경우 SQL 쿼리는 데이터의 구조를 이해하지 못합니다. 여기서 XMLType 데이터타입을 이용하여 데이터를 저장하는 것도 가능합니다.
비구조형 데이터는 데이터를 문서와 동일한 물리적 포맷을 갖도록 저장하는 경우에 사용됩니다. 데이터가 자주 업데이트되지 않는 경우, 또는 (문서의 일부가 아닌) 전체 문서에 대해 대량의 INSERT 또는 읽기 작업이 수행되는 경우라면, 데이터를 분할하지 않고 하나로 저장하는 것이 더 효율적일 수 있습니다. 또 문서의 유연성을 보장해야 하는 경우에도 XMLType 테이블/컬럼이 유용하게 활용됩니다.
하지만 비구조형 스토리지는, 문서의 일부를 업데이트하는 작업에서 구조형 스토리지에 비해 효율성이 떨어진다는 문제점을 갖습니다. 또 CLOB 데이터타입으로부터 DOM을 구성하기 위한 XPath 작업을 수행하는 경우 시스템 리소스가 많이 필요할 수 있습니다. 비구조형 데이터의 또 다른 문제로, SQL 제약조건의 구현이 불가능하며 메모리 관리가 비효율적이라는 점을 지적할 수 있습니다.
일반적으로 비구조형 데이터의 대부분은 XML 데이터로 취급되지 않으며, 대부분 또는 전체 데이터가 단순한 CLOB으로 관리됩니다.
지금까지 스토리지 모델에 대해 설명하였습니다. 이제 몇 가지 명명(naming) 기능에 대해 알아봅시다.
네임스페이스(namespace)란 무엇인가?
네임스페이스는 XML DB에서 사용되는 개념 중에서도 이해하기 까다로운 편에 속합니다. 본 문서에서는 몇 가지 네임스페이스의 개념과 그 활용 방안에 대해 간략히 설명하기로 합니다.
네임스페이스는 XML 문서 내에서 서로 관계를 갖는 일련의 속성 또는 엘리먼트를 정의하는 용도로 사용됩니다. 네임스페이스를 이용하면 문서의 컨스트럭트(construct)가 글로벌하게 유니크한 이름을 가짐을 보장할 수 있습니다. XMLSchema는 이 기능과 깊은 연관을 갖습니다. 일반적으로 타겟 네임스페이스는 XMLSchema의 URI와 동일한 이름을 가집니다. 네임스페이스의 활용 예는 아래에서 확인하실 수 있습니다. 아래 예에서 네임스페이스의 명명 방식이 인터넷 URL과 매우 유사함을 확인하실 수 있을 것입니다. 이러한 명명 표준은 W3C(World Wide Web Consortium)이 권장하고 있는 방식이긴 하지만 필수는 아닙니다. URI는 데이터베이스에 등록된 XMLSchema의 이름을 유니크하게 구분하기 위해 사용되며, 반드시 문서가 위치한 물리적 URI와 일치할 필요는 없습니다.
XMLType 메소드와 XML 함수는 네임스페이스 접두어(namespace prefix)를 사용합니다. XML 문서가 타겟 네임스페이스를 갖지 않는 경우, 네임스페이스 접두어는 noNameSpace 네임스페이스로 설정됩니다. 스키마 URI를 위해서는 xsi:noNamespaceSchemaLocation 속성이 사용될 수 있습니다.
엘리먼트가 다른 네임스페이스 접두어를 갖지 않는 경우, 선언되는 엘리먼트에 대해 디폴트 네임스페이스를 적용하는 것도 가능합니다.
자주 사용되는 두 가지 스페셜 네임스페이스(special namespace)로 전체 XMLSchema 네임스페이스를 의미하는 http://www.w3.org/2001/XMLSchema와 오라클이 제공하는 XML DB 네임스페이스를 의미하는 http://xmlns.oracle.com/xdb가 있습니다. http://xmlns.oracle.com/xdb 네임스페이스는 하부 SQL 함수에서 활용되는 함수들을 가집니다. ora:contains, ceiling, floor, not, string-length, substring, translate 등의 일부 XPath 함수는 재작성 됩니다. XML DB에 의해 사용되는 속성 또한 이 네임스페이스에 포함됩니다.
엘리먼트를 저장할 때에는, 해당 엘리먼트에 대해 네임스페이스를 명시할 수 있습니다. XML에서 엘리먼트의 네임스페이스를 정의하는 방법이 다음과 같습니다:
<elementName xmlns:ab="http.name.com" />
위에서 elementName은 현재 작업 중인 엘리먼트(예: "orders")를 의미합니다. Xmlns는 네임스페이스 정의를 의미하는 예약어입니다. "ab"는 접두어(prefix)로 사용되고 있습니다. 이 접두어는 URI에 바인드되는 쇼트 네임(short name)으로 사용됩니다. 위의 경우 "ab"는?http.name.com’에 바인드되며, 엘리먼트 네임의 접두어로 "ab"를 사용할 수 있습니다.
아래에서는 매우 작은 스키마를 생성하고 http://www.w3.org/2001/XMLSchema에 대해 xs 접두어를, http://xmlns.oracle.com/xdb에 대해 xdb 접두어를 바인딩하게 될 것입니다. 네임스페이스를 정의하기 위해서는, xmlns를 사용합니다. 아래 예제에서는 스키마 엘리먼트 네임으로 xs 접두어가 사용되고 있음을 참고하시기 바랍니다.
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xdb= "http://xmlns.oracle.com/xdb" version="1.0">
<xs:element name="INVOICESCHEMA" xdb:defaultTable="INVOICESCHEMA">
<xs:complexType>
<xs:sequence>
<xs:element name="MailAddressTo">
<xs:complexType>
<xs:sequence>
<xs:element name="Person" type="xs:string"/>
<xs:element name="Street" type="xs:string"/>
</xs:sequence>
<xs:attribute name="id" type="xs:string" use="required"/>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
지금까지 네임스페이스의 개략적인 개념에 대해서 설명했습니다. 네임스페이스에 대한 자세한 정보는 OTN의 <Oracle XML DB Developer’s Guide 10g Release 2>를 참고하시기 바랍니다.
XML과의 연동
XMLType 테이블/컬럼에데이터입력하기. XML 데이터를 데이터베이스에 입력하는 방법에는 여러 가지가 있습니다. 데이터는 SQL, PL/SQL, Java, C 프로그램을 통해 입력될 수 있습니다. 또 SQL*Loader나 XMLType 테이블의 Import/Export 기능을 이용해서 데이터베이스에 데이터를 로드할 수도 있습니다. Oracle Streams 또는 DBMS_XDB 함수를 이용하여 XML 데이터를 이동하는 것도 가능합니다. XML 데이터를 로드하는 간단한 방법을 예제를 통해 알아보겠습니다.
아래 예에서는, SQL INSERT 구문을 이용하여 bfile에 저장된 XML 문서를 테이블에 로드하고 있습니다. 이 파일은 데이터베이스 디렉토리를 통해 접근할 수 있습니다. 이를 위해 XML 문서 파일이 저장된 파일을 가리키는 디렉토리를 먼저 생성해야 합니다. 본 문서의 예제를 실행하기 위해서는, 사용자 계정에 xdbadmin 역할이 할당되어 있어야 함을 참고하시기 바랍니다.
create or replace directory xmldir as '/u01/app/oracle/admin/db01/sql';
이제 XMLType 데이터를 포함하는 테이블을 두 가지 방법을 통해 생성해 보겠습니다. 첫 번째로 XMLType의 컬럼을 갖는 테이블을 이용하는 방법을, 두 번째로 XMLType으로 정의된 테이블을 이용하는 방법이 사용됩니다.
1. XML 컬럼을 갖는 테이블을 생성.
create table invoiceXML_col (
inv_id number primary key,
inv_doc XMLType);
2. XML 테이블의 생성.
create table invoiceXML_tbl of XMLtype;
위 두 가지 구문 모두 각 테이블에 프라이머리 키와 LOB를 위한 두 가지 인덱스를 암시적으로 생성합니다.
이제 invoice 문서를 두 테이블에 INSERT해 보겠습니다. 예제에서는 AL32UTF8 문자 셋을 사용하고 있으며, 파일의 읽기 작업 수행 시 이 문자 셋을 전달하도록 설정합니다. 데이터는 서로 유사한 두 가지 SQL 구문을 통해 두 테이블에 INSERT 됩니다.
Insert into invoicexml_col values (1,
XMLType(bfilename('XMLDIR', 'invoicexml.txt'),
nls_charset_id('AL32UTF8') ));
Insert into invoicexml_tbl values
(XMLType(bfilename('XMLDIR', 'invoicexml.txt'),
nls_charset_id('AL32UTF8')));
이제 XMLType 테이블/컬럼을 이용하여 데이터베이스에 두 개의 XML 문서를 데이터베이스에 로드하였습니다.??
Schema 타입 데이터의 등록 및 구현
네임스페이스, 스토리지를 이용하는 방법 이외에도, "schema-typed" 데이터 또는 "untyped" 데이터로 XMLType 데이터를 구현할 수도 있습니다. XMLSchema는 문서의 구조와 컨텐트에 대한 정보를 제공합니다. 이를 통해 문서의 생성, 검증, 컨트롤 작업을 보다 쉽게 수행할 수 있습니다. 스키마의 정보는 쿼리/업데이트 작업의 효율성을 개선하기 위한 용도로 활용될 수도 있습니다. XMLSchema는 XML 문서가 구조형 스토리지 데이터로 저장되는 것을 허용합니다. 이때 문서는 XMLSchema 내에서 일련의 오브젝트로 분할됩니다. 문서를 저장하기 위해 사용되는 오브젝트 모델은 스키마 정의에 의해 결정됩니다. XMLType 메소드인 schemaValidate()와 isSchemaValid()를 이용하면 스키마 정의를 기반으로 XMLType 문서를 편집할 수 있습니다.
스키마를 생성하고 XML 문서에서 활용하려면 어떻게 해야 할까요? 먼저 XMLSchema 정의를 생성해야 합니다.
- 그런 다음 스키마를 등록합니다.
- XMLSchema 기반 테이블을 생성합니다.
- 테이블에 데이터를 입력합니다.
아래 예제에서 위 4 단계의 작업을 실행하는 방법이 예시되고 있습니다.
1. 먼저, XML_DIR 디렉토리의 invoiceformtest.xsd 파일에 스키마 정의를 생성합니다.
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xdb=http://xmlns.oracle.com/xdb version="1.0">
<xs:element name="INVOICEFORMTEST" xdb:defaultTable="INVOICEFORMTEST">
<xs:complexType>
<xs:sequence>
<xs:element name="MailAddressTo">
<xs:complexType>
<xs:sequence>
<xs:element name="Person" type="xs:string"/>
<xs:element name="Street" type="xs:string"/>
<xs:element name="City" type="xs:string"/>
<xs:element name="State" type="xs:string"/>
<xs:element name="Zipcode" type="xs:string"/>
</xs:sequence>
<xs:attribute name="id" type="xs:string" use="required"/>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
2. DBMS_XMLSCHEMA.registerSchema 프로시저를 이용하여 데이터베이스에 스키마를 등록합니다. 데이터베이스 연결에 사용하는 계정에 스키마 등록에 필요한 권한이 할당되어 있어야 합니다.
BEGIN
DBMS_XMLSCHEMA.registerSchema(
SCHEMAURL => 'http://xmlns.oracle.com/xdb/invoiceformtest.xsd',
SCHEMADOC => bfilename('XMLDIR','invoiceformtest.xsd'),
CSID => nls_charset_id('AL32UTF8'));
END;
/
스키마를 삭제하려면 아래와 같이 DBMS_XMLSCHEMA.deleteSchema 구문을 실행합니다:
BEGIN
DBMS_XMLSCHEMA.deleteSchema(
SCHEMAURL => 'http://xmlns.oracle.com/xdb/invoiceformtest.xsd',
DELETE_OPTION => dbms_xmlschema.DELETE_CASCADE_FORCE);
END;
/
3. 위 구문을 실행하면 invoiceformtest 테이블이 함께 생성됩니다(xdb:defaultTable="INVOICEFORMTEST" 구문을 참고하십시오). 위 구문이 없다면, 자동으로 생성된 테이블 네임이 적용될 것입니다.
4. 이제 XMLSchema 기반 테이블에 XML_DIR 디렉토리에 저장된 문서를 입력할 차례입니다.
Insert into invoiceformtest values
(XMLType(bfilename('XMLDIR', 'invoiceformtest.txt'),
nls_charset_id(' AL32UTF8')));
Let’s look at what we’ve just created using the object_value pseudocolumn on the XML document.
SQL> select object_value from invoiceformtest;
OBJECT_VALUE
<INVOICEFORMTEST>
<MailAddressTo id="1">
<Person>Joe Smith</Person>
<Street>10 Apple Tree Lane</Street>
<City>New York</City>
<State>NY</State>
<Zipcode>12345</Zipcode>
</MailAddressTo>
</INVOICEFORMTEST>
이것으로 모든 작업이 완료되었습니다!
XQuery 함수/표현식의 이해
XQuery는 XML 파일로부터 정보를 추출하기 위한 표준 쿼리 언어로 W3C에 의해 개발되었습니다. XQuery를 이용하면 물리적인 XML 문서, 또는 XML 뷰를 통해 XML 문서로 가상화된 관계형 데이터에 접근할 수 있습니다. 표현식은 간단한 쿼리로, 또는 복잡한 쿼리의 일부분으로 구현될 수 있습니다. 그 밖에 날짜/시간, 산술, 문자열 함수 및 사용자 정의 함수 등의 표준 함수가 제공됩니다. XQuery는 Oracle Database 10g Release 2에서 처음으로 구현되었으며, XMLQuery(), XMLTable() 함수를 통해 지원됩니다. 이 두 가지 함수를 사용한 예제는 뒷부분의 "XMLQuery()와 XMLTable()” 섹션에서 확인할 수 있습니다.
XQuery는 XPath 표현식을 이용하여 XML 문서 내의 아이템을 검색합니다. XQuery는 Oracle Database 10g Release 2에 구현된 XMLQuery(), XMLTable() 함수의 기반으로 활용됩니다.
XQuery의 표현식 중 자주 활용되는 것으로 XPath, XMLSequence, FLWOR 등이 있습니다.
XPath, XMLSequence, FLWOR 표현식
XPath. XPath는 어드레싱 테크닉을 이용하여 XML 문서 내의 아이템을 검색하기 위해 사용됩니다. XPath는 문서 구조에 대한 논리적인 경로를 제공합니다. 프로그래머는 XPath를 이용함으로써, 특정 엘리먼트를 직접 참조하는 대신 문서에 대한 추상화된 경로를 지정할 수 있습니다. XPath는 노드(node)의 개념을 사용하며, 경로는 속성, 자신, 부모, 자식, 조상과 같은 관계로 이루어진 "논리적 트리(logical tree)"로 구성됩니다. XPath는 XML 문서를 여러 노드로 이루어진 트리로 모델링 합니다. 노드는 엘리먼트(element), 속성(attribute), 텍스트(text) 등의 여러 가지 타입을 갖습니다. XPath를 이용하면 각 노드의 문자열 값을 정확하게 계산할 수 있습니다.
XPath 표현식은 표준적인 방법으로 XML 문서를 쿼리/업데이트하기 위해 사용됩니다. XPath의 기능을 예시하기 위해 앞에서 생성한 invoicexml_tbl 문서에 대해 extract, extractValue, existsNode, XMLSequence 함수를 적용해 보기로 합시다.
XPath 표현식을이용하여문서를조회. XPath 함수를 이용하여 XML (XMLType) 데이터를 검색해 봅시다. 이번 예제에서는 object_value 유사컬럼(pseudocolumn)을 이용하여 문서를 조회해 보겠습니다.
SQL> select object_value from invoicexml_tbl;OBJECT_VALUE
<Invoice>
<MailAddressTo id="PA">
<Person>Joe Smith</Person>
<Street>10 Apple Tree Lane</Street>
<City>New York</City>
<State>NY</State>
<Zipcode>12345</Zipcode>
</MailAddressTo>
<MailAddressFrom id="PA">
<Person>Ed Jones</Person>
<Street>11 Cherry Lane</Street>
<City>Newark</City>
<State>NJ</State>
<Zipcode>67890</Zipcode>
</MailAddressFrom>
<Details id="2006Sept1to30PA">
<FromTo>Sept 1, 2006 to Sept 30, 2006</FromTo>
<Hours>70</Hours>
<Rate>30</Rate>
<Taxes>210</Taxes>
<TotalDue>2310</TotalDue>
<InvDate>Oct 1, 2006</InvDate>
<Contractor>Ed Jones</Contractor>
</Details>
</Invoice>
extract의활용. extract와 object_value를 조합하여, 개별 노드와 노드의 리프 노드(leaf node)를 선택할 수 있습니다. 다시 말해, XMLType으로 저장된 XML 문서의 내부를 조회할 수 있습니다. 데이터가 구조형이든 비구조형이든, 또는 데이터가 스키마를 기반으로 하고 있든 그렇지 않든 관계없이 데이터의 조회가 가능합니다. MailAddressTo 노드와 이 노드의 리프 노드를 추출하는 예가 아래와 같습니다.
select extract(object_value, '/Invoice/MailAddressTo') from invoicexml_tbl;
EXTRACT(OBJECT_VALUE,'/INVOICE/MAILADDRESSTO')
<MailAddressTo id="PA"><Person>Joe Smith</Person><Street>10
Apple Tree Lane</Street><City>New York</City><State>NY</Stat
e><Zipcode>12345</Zipcode></MailAddressTo>
위 예제에서는 MailAddressTo 노드에 관련한 출력이 그리 깔끔하게 포맷되지 않은 상태입니다. 또 적용된 문법도 매우 단순화되어 있습니다. 여기서 중요한 것은 전체 데이터를 덤프하지 않고도 문서 내부를 조회할 수 있다는 점입니다.
extractValue의활용. extractValue를 이용하면 리프 노드에 존재하는 데이터 값을 추출할 수 있습니다. MailAddressTo와 같은 상위 레벨 노드는 이 함수를 통해 추출이 불가능합니다. 결과로 출력되는 데이터는 XML 포맷이 아니며 단순히 데이터 값만을 포함하고 있습니다.
select extractValue(object_value, '/Invoice/MailAddressTo/Person')
Person from invoicexml_tbl;
PERSON
Joe Smith
existsNode의활용 ExistsNode는 문서의 노드 레벨에 존재하는 특정 값을 검색하기 위해 사용됩니다. extistNode는 검색의 성공 여부에 따라 True 또는 False 플래그를 반환합니다. "=1" 조건자는 숫자가 아닌 True 조건을, "=0"은 False를 의미합니다.
Select count(*) from invoicexml_tbl
where existsNode(
object_value, '/Invoice/MailAddressTo[Person="Joe Smith"]') = 1;
COUNT(*)
1
XMLSequence의활용. 단일 노드로부터 값을 추출하는데 사용되는 extractValue와 달리, XMLSequence는 다수의 노드 또는 문서의 일부를 조회하는 용도로 사용됩니다. 이를 위해 XMLType 오브젝트를 포함하는 가상 테이블이 생성됩니다. MailAddressTo 브랜치 노드를 이용한 예제를 통해 extractValue와 XMLSequence를 비교해 보겠습니다.
select extractValue(object_value, '/Invoice/MailAddressTo') from invoicexml_tbl;
from invoicexml_tbl
*
ERROR at line 2:
ORA-19025: EXTRACTVALUE returns value of only one node
ORA-19025 메시지가 발생하지 않도록 하려면 아래와 같이 XMLSequence를 사용하도록 쿼리를 수정해야 합니다:
select value(addr)
from invoicexml_tbl i,
table(XMLSequence(
extract(i.object_value, '/Invoice/MailAddressTo'))) addr
where existsNode(i.object_value, '/Invoice/Details[@id="2006Sept1to30PA"]') = 1;
VALUE(ADDR)
<MailAddressTo id="PA"><Person>Joe Smith</Person><Street>10
Apple Tree Lane</Street><City>New York</City><State>NY</Stat
e><Zipcode>12345</Zipcode></MailAddressTo>
XMLSequnce에 의해 가상 테이블이 생성되었으므로, 이제 이 함수를 리프 노드에 대해 실행할 수 있습니다.
select value(person)
from invoicexml_tbl i,
table(XMLSequence(
extract(i.object_value, '/Invoice/MailAddressTo/Person'))) person
where existsnode(i.object_value, '/Invoice/Details[@id="2006Sept1to30PA"]') = 1;
VALUE(PERSON)
<Person>Joe Smith</Person>
FLWOR. XQuery에서 사용되는 가장 대표적이고 강력한 표현식으로 FOR, LET, WHERE, ORDER BY, RETURN(FLWOR; “flower”로 발음)을 들 수 있습니다. FOR, LET 둘 중 하나는 반드시 사용되어야 합니다. WHERE, ORDER BY는 옵션이며, RETURN은 반드시 필요합니다. 본 문서에서는 FLOWR를 이용하는 방법을 개략적으로만 예시하고 있습니다.
FOR는 하나 또는 그 이상의 변수를 반복적으로 바인딩합니다. FOR 표현식은 SQL From 절과 비슷한 구조를 갖는 것으로 이해할 수 있습니다. FOR 표현식의 목록에서 앞에서 사용된 값은 뒤의 다른 값에 대해 다시 활용될 수 있습니다. 그 예가 아래와 같습니다:
For $var in (1,2,3) , $varPlus5 in (5+$var, 5+$var, 5+$var)
3 회의 반복 작업을 통해 $var, $varPlus5는 각각 1,6; 2.7; 3,8로 설정됩니다.
LET 역시 FOR와 마찬가지로 변수를 반복적으로 바인딩하며, 앞에서 계산된 값을 이용하여 새로운 값을 계산할 수 있습니다. 또 FOR와 마찬가지로 LET 역시 SQL의 FROM 절과 비슷하게 이해할 수 있습니다. 또 JOIN 작업을 위해 LET을 사용하는 것도 가능합니다.
WHERE는 SQL WHERE와 같은 방법으로 데이터의 필터링을 수행합니다.
옵션으로 사용되는 ORDER BY는 데이터의 정렬을 수행합니다.
RETURN은 최종적으로 필터링, 정렬된 FLWOR 표현식 결과를 반환합니다.
XMLQuery()를이용한 FLWOR 표현식. party.xml과 orders.xml이라는 두 개의 문서를 쿼리하고 JOIN 하는 예를 살펴 보기로 합시다. 이 XML 데이터는 Oracle XML DB Repository에 저장되어 있습니다. 쿼리와 JOIN을 위해 XMLQuery(), FLWOR, 그리고 doc/count/avg/integer와 같은 XQuery 함수를 사용하기로 합니다. 이 함수와 표현식은 빌트인 XQuery 함수(http://www.w3.org/2003/11/xpath_functions)를 위한 네임스페이스에 위치하고 있습니다.
작성된 쿼리는 아래와 같습니다: fn:doc 함수를 이용하여, partys.xml의 모든 partyno 속성에 대해(FOR), partyno와 매치되는 orders.xml의 모든 order 엘리먼트를 JOIN합니다(LET). ($p 변수는 FOR 구문에 바인드 됩니다.) 이 쿼리를 수행하면 일련의 아이템($p와 $o)이 생성되며, 여기서 $p는 party number를, $o는 해당 party의 order를 의미합니다. 여기서 1 개 이상의 order가 존재하는 아이템만을 조회합니다(WHERE). 또 fn 네임스페이스의 avg XQuery 함수를 이용하여 평균 amt를 내림 차순으로 정렬합니다(ORDER BY). amt는 order 엘리먼트 $o와 연결됩니다. 결과로, ($p에 바인드 된) party number와 자식 엘리먼트인 ordercount가 반환됩니다.
SELECT XMLQuery()('for $p in fn:doc("/public/partys.xml")/partys/party/@partyno
let $o := fn:doc("/public/orders.xml")/orders/order[@partyno = $p]
where fn:count($o) > 1
order by fn:avg($o/@amt) descending
return <big-party>{$p,
<ordercount>{fn:count($o)}</ordercount>,
<avgamt>{xs:integer(fn:avg($o/@amt))}</avgamt>}
</big-party>'
RETURNING CONTENT) ORDERS FROM DUAL;
ORDERS
<big-party>1111<ordercount>2</ordercount><avgamt>3500</avgamt></big-party>
위 쿼리만으로도 XML 문서에 대해 FLWOR 표현식이 갖는 강력한 기능을 확인할 수 있을 것입니다.
XQuery ora: 함수
그 밖에도 Oracle XML DB는 개발자들이 활용할 수 있는 다섯가지 유용한 XQuery 함수를 제공하고 있습니다. 이 함수들은 ora: 접두어를 사용하는 http://xmlns.oracle.com/xdb 네임스페이스에 구현되어 있습니다. 또 이와 별도로 두가지 XPath 함수가 제공됩니다. 이 함수들은 현재 SQL/XML 표준에 포함되어 있지 않으나 향후에는 포함될 것으로 예상되고 있습니다. ora:view 함수는 관계형 데이터를 XML 포맷으로 전환하기 위한 함수로 특히 유용하게 활용됩니다. 각 함수에 대한 설명은 아래에서 확인하실 수 있습니다.
ora:view XQuery 함수. ora:view 함수는 다른 관계형 테이블에 대한 뷰를 생성하며, 관계형 테이블을 마치 XML 문서인 것처럼 조회할 수 있게 하는 기능을 제공합니다
Syntax: ora:view ([db-schema STRING, ] db_table STRING)
RETURNS document-node (element())
Examples that use ora:view are shown later in the article.
ora:contains XQuery 함수. 네임스페이스에 사용되는 ora:" 접두어와? "contains"를 함께 사용하여 XQL/XML existsNode, extract, extractValue 함수에 XPath 매개변수로 전달하기 위한 XPath 함수를 생성할 수 있습니다. 이를 통해 text 조건절을 이용하여 구조적인 검색을 수행할 수 있습니다.
ora:contains 함수는 text_query가 input_text와 매치되는 경우 양수 값을 반환합니다. 매치되지 않는 경우에는 0이 반환됩니다. existsNode, extract, extractValue 함수를 위해 ora:contains를 사용할 때에는 네임스페이스 매핑 매개변수 xmlns:ora="http://xmlns.oracle.com/xdb"를 포함시켜야 합니다. ora:contains의 사용 방법이 아래와 같습니다. policy_owner가 정의되지 않은 경우 이 함수의 소유자는 현재 사용자로 설정됩니다. policy_name은 적용되는 매칭 룰의 이름을 의미합니다. 디폴트 매칭 룰은 ctxsys.default_policy_oracontains에 의해 정의됩니다.
Syntax: ora:contains (input_text, text_query [, policy_name] [, policy_owner])
아래 예제는 ora:contains를 existsNode의 매개변수로 사용하는 예를 보여 주고 있습니다.
SELECT count(*) FROM invoicexml_tbl
WHERE existsNode(object_value, '/Invoice/MailAddressTo/Person
[ora:contains(text(), "Joe Smith") > 0]',
'xmlns:ora="http://xmlns.oracle.com/xdb"') = 1;
COUNT(*)
1
ora:matches XQuery 함수. 이 함수는 정규 표현식을 사용하여 텍스트 매칭 작업을 수행합니다. 이 함수는 SQL REGEXP_LIKE 조건과 유사하며, SQL 데이터타입 대신 XQuery 매개변수를 사용한다는 점이 다를 뿐입니다. target_string이 match_pattern 정규 표현식과 일치하는 경우에는 True가, 그렇지 않은 경우에는 False가 반환됩니다. 아래 문법에서 확인할 수 있듯, match_parameter를 추가적인 검색 조건으로 활용하는 것도 가능합니다. match_parameter를 이용하는 예로 대소문자 구분이 필요한 경우를 들 수 있습니다.
Syntax: ora:matches (target_string, match_pattern [, match_parameter])
ora:replace XQuery 함수. match_pattern을 만족하는 경우 target_string을 replace_string으로 변경함으로써 ora:matches 함수의 기능을 확장할 수 있게 합니다. oral:matches와 마찬가지로 이 함수는 정규 표현식을 사용하여 텍스트 매칭 작업을 수행합니다.
Syntax: ora:replace (target_string, match_pattern,
replace_string [, match_parameter])
ora:sqrt XQuery 함수. 제공된 숫자의 루트 값을 계산하는 함수입니다.
Syntax: ora:sqrt (number)
XPath 확장함수: ora:instanceof, ora:instanceof-only. Oracle XML DB는 속성과 엘리먼트의 데이터타입이 알려진 경우, 스키마 기반 데이터를 지원할 수 있습니다. XPath 1.0은 데이터 타입 정보를 인식하지 못하므로, XML 문서 노드를 특정 데이터타입으로 제한하기 위한 XML DB 확장 함수가 http://xmlns.oracle.com/xdb 네임스페이스를 통해 제공되고 있습니다. ora:instanceof 함수를 이용하면 노드를 특정 데이터타입 또는 서브타입으로 제한할 수 있습니다. ora:instanceof-only는 노드의 데이터타입만을 제한합니다. 서브타입은 타입을 확장 또는 제한하기 위해 사용되는 기능입니다.
Syntax: ora:instanceof(nodeset-expr, typename [, schema-url])
Syntax: ora:instanceof-only(nodeset-expr, typename [, schema-url])
위에서 nodeset-expr은 일반적으로 관계형 XPath 표현식으로 적용됩니다. 이 함수는 노드의 데이터타입 중 하나가 typename과 매치하는 경우 True를, 그렇지 않은 경우 False를 반환합니다. typename에는 네임스페이스의 접두어가 사용될 수 있습니다.
이 함수는 스키마를 기반으로 하지 않는 데이터에 대해 False를 반환합니다. 스키마를 기반으로 하지 않는 데이터는 엘리먼트 및 속성과 연계된 데이터타입을 갖지 않기 대문입니다.
fn:doc, fn:collection 함수를 이용하여 XML DB Repository의 XML 데이터 쿼리하기
XML DB 리포지토리의 전체 리소스를 쿼리하는데 사용되는 두 가지 중요한 XQuery 함수가 있습니다. fn:doc은 XML 데이터를 포함하는 리포지토리 파일을 조회하기 위한 XQuery 함수입니다. 이 파일 리소스는 URI 매개변수를 통해 참조됩니다. XQuery 변수는 FOR, LET 표현식을 통해 데이터에 바인드 될 수 있습니다. fn:doc XQuery 함수를 이용하여 XML DB 리포지토리에 저장된 단일 XML 문서를 조회하는 것도 가능합니다.
두 번째로, fn:collection은 리포지토리의 리소스를 조회하기 위한 XQuery 함수입니다. 이 함수는 리포지토리의 유사한 폴더에 저장된 유사한 문서들을 반환합니다.
fn:doc, fn:collection의 활용 방법을 예시하기 위해, 먼저 리포지토리 리소스를 생성해 봅시다. 우선 DBMS_XDB PL/SQL 패키지를 이용하여 리소스를 생성합니다. 이 패키지는 XML DB의 리소스 관리를 위해 사용됩니다. 아래에서는 Orders, Partys XML 문서를 포함하는 새로운 파일 리소스를 생성하기 위해 createResource 프로시저를 사용하고 있습니다.
아래 예제에서 사용되는 orders, partys 리소스 네임을 생성합니다. 또 이와 별도로 ordersnamespace.xml이라는 리소스가 생성됩니다.
DECLARE
res BOOLEAN;
ordersxmlstring VARCHAR2(500):=
'<?xml version="1.0"?>
<orders>
<order orderno="15" partyno="1111" itemname="Widget" amt="5000"/>
<order orderno="25" partyno="1111" itemname="Do dad" amt="2000"/>
<order orderno="35" partyno="2222" itemname="All purpose item" amt="7000"/>
<order orderno="45" partyno="3333" itemname="The best thing" amt="15000"/>
</orders>';
partysxmlstring VARCHAR2(500):=
'<?xml version="1.0"?>
<partys>
<party partyno="1111" partyname="ABC Corp" partycity="Toronto"/>
<party partyno="2222" partyname="Freds Inc" partycity="Chicago"/>
<party partyno="3333" partyname="Gofaster Corp" partycity="Montreal"/>
</partys>';
ordersxmlnsstring VARCHAR2(500):=
'<?xml version="1.0"?>
<orders xmlns="http://order.com">
<order orderno="15" partyno="1111" itemname="Widget" amt="5000"/>
<order orderno="25" partyno="1111" itemname="Do dad" amt="2000"/>
<order orderno="35" partyno="2222" itemname="All purpose item" amt="7000"/>
<order orderno="45" partyno="3333" itemname="The best thing" amt="15000"/>
</orders>';
BEGIN
res := DBMS_XDB.createResource('/public/orders.xml', ordersxmlstring);
res := DBMS_XDB.createResource('/public/partys.xml', partysxmlstring);
res := DBMS_XDB.createResource('/public/ordersnamespace.xml', ordersxmlnsstring);
END;
아래와 같이 resource_view를 통해 방금 생성된 리소스들을 확인할 수 있습니다:
SQL> select any_path, res from resource_view where any_path like '%partys%';
ANY_PATH
RES
/public/partys.xml
<Resource xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd">
<CreationDate>2006-06-19T17:12:00.414000</CreationDate>
<ModificationDate>2006-06-19T17:12:00.414000</ModificationDate>
<DisplayName>partys.xml</DisplayName>
<Language>en-US</Language>
<CharacterSet>WINDOWS-1252</CharacterSet>
<ContentType>text/xml</ContentType>
<RefCount>1</RefCount>
</Resource>
다음으로, fn:doc을 이용하여 party.xml 문서를 디스플레이합니.
SELECT XMLQuery('for $p in fn:doc("/public/partys.xml")
return $p'
RETURNING CONTENT) partys FROM DUAL;
PARTYS
---------------------------------------------------------------------
<partys><party partyno="1111" partyname="ABC Corp"
partycity="Toronto"/><party partyno="2222" partyname="Freds Inc"
partycity="Chicago"/> <party partyno="3333" partyname="Gofaster Corp"
partycity="Montreal"/></partys>
fn:collection은 fn:doc과 조금 다른 방식으로 사용됩니다. 다시 말해, fn:collection을 이용하여 리포지토리의 동일한 폴더에 저장된 모든 문서를 반환할 수 있습니다. 위 구문을 통해 출력되는 결과는 깔끔하게 포맷되지 않은 상태입니다. 아래에서는 결과의 가독성을 높이기 위해 수작업으로 포맷 작업을 수행했습니다.
SELECT XMLQuery('for $p in fn:collection("/public")
return $p'
RETURNING CONTENT) collection_public FROM DUAL;
COLLECTION_PUBLIC
----------------------------------------------------------------------
<orders>
<order orderno="15" partyno="1111" itemname="Widget" amt="5000"/>
<order orderno="25" partyno="1111" itemname="Do dad" amt="2000"/>
<order orderno="35" partyno="2222" itemname="All purpose item" amt="7000"/>
<order orderno="45" partyno="3333" itemname="The best thing" amt="15000"/>
</orders>
<orders xmlns="http://order.com">
<order orderno="15" partyno="1111" itemname="Widget" amt="5000"/>
<order orderno="25" partyno="1111" itemname="Do dad" amt="2000"/>
<order orderno="35" partyno="2222" itemname="All purpose item" amt="7000"/>
<order orderno="45" partyno="3333" itemname="The best thing" amt="15000"/>
</orders>
<partys>
<party partyno="1111" partyname="ABC Corp" partycity="Toronto"/>
<party partyno="2222" partyname="Freds Inc" partycity="Chicago"/>
<party partyno="3333" partyname="Gofaster Corp" partycity="Montreal"/>
</partys>
전체 XML DB 리소스를 관리하기 위해 사용되는 DBMS_XDB 프로시저는 이 밖에도 여러 가지가 있으며, 리소스를 삭제하거나 폴더를 생성하는 등의 기능이 제공됩니다. DBMS_XDB 프로시저에 대한 자세한 정보는 OTN의 <Oracle XML DB Developer’s Guide 10g Release 2>에서 확인하실 수 있습니다.
XMLQuery() and XMLTable()
XMLQuery(), XMLTable() 함수는 Oracle Database 10g Release 2에서 처음으로 소개되었습니다. 이 두 함수는 SQL과 XML 간의 강력한 인터페이스를 제공하며, 관계형 데이터를 마치 XML처럼, 또 XML 데이터를 마치 관계형 데이터인 것처럼 조회, 구성, 변환할 수 있게 합니다. 보통 XMLQuery()는 관계형 데이터로부터 XML 문서를 생성하는 용도로, XMLTable()은 XML 데이터로부터 관계형 뷰를 생성하기 위한 목적으로 활용됩니다. 이 함수들은 현재 SQL/XML 표준에 포함되어 있지 않으나 향후에는 포함될 것으로 예상되고 있습니다.
XMLQuery() 함수 XMLQuery() 함수는 XML 데이터를 구성하고 XQuery를 이용하여 조회하는 기능을 제공합니다. XMLQuery()를 이용하면 SQL의 컨텍스트에서 XQuery 표현식을 실행할 수 있습니다. 또 ora:view를 이용하여 관계형 데이터를 위한 다이내믹 XML 뷰를 생성하고, 전체 문서가 아닌 XML 문서의 일부에 대해서만 작업을 수행하는 것이 가능합니다.
XMLQuery()는 XQuery 표현식을 문자열로, 옵션으로 사용되는 XQuery "context item"을 SQL 표현식으로 처리합니다. 컨텍스트 아이템은 XQuery 표현식이 실행되는 XPath 컨텍스트를 설정하기 위해 사용됩니다. 이 함수는 XQuery 표현식의 결과를 XMLType 인스턴스로 반환합니다. XMLQuery() 함수는 SQL 표현식을 매개변수로 취할 수도 있습니다. 이 경우 값은 표현식이 실행되는 과정에서 XQuery 변수에 바인딩 됩니다. 실행 결과는 XMLType 인스턴스로 반환됩니다.
이제 XMLQuery()를 관계형/XML 데이터에 대해 활용하는 두 가지 예제를 소개하겠습니다.
? XMLQuery()와 ora:view 함수 FLWOR 표현식의 활용 사례. 첫 번째 예제에서는 관계형 테이블에 XMLQuery()를 적용하고 있습니다. XMLQuery()는 XML 데이터에 대해 동작하며, 이를 위해 HR 스키마의 Employee, Department 테이블에 대해 ora:view 함수를 적용합니다. ora:view가 적용된 두 개의 관계형 테이블은 XML처럼 조회되며, 여기에 "nested" FLWOR 표현식을 포함하는 XQuery 표현식을 사용할 수 있습니다.
아래 쿼리가 수행하는 작업이 다음과 같습니다: 각각의 department에 대해 department id를 가져오고, department id와 매치되고 commission이 30 퍼센트를 넘는 모든 employee에 대해 fist name과 last name을 반환합니다. 이 쿼리에서 FOR 표현식이 두 차례 사용되었음을 주목하시기 바랍니다.
SELECT XMLQuery(
'FOR $dep in ora:view("DEPARTMENTS")/ROW
RETURN <Department id="{$dep/DEPARTMENT_ID}">
<Employee>
{FOR $emp in ora:view("EMPLOYEES")/ROW
WHERE $emp/DEPARTMENT_ID eq $dep/DEPARTMENT_ID
and $emp/COMMISSION_PCT > .3
RETURN ($emp/FIRST_NAME, $emp/LAST_NAME)}
</Employee>
</Department>'
RETURNING CONTENT) HIGH_COMMISSION_EMP_NAMES FROM DUAL;
HIGH_COMMISSION_EMP_NAMES
---------------------------------------------------------------
<Department id="10"><Employee></Employee></Department><Department
id="20"><Employee></Employee></Department><Department
id="30"><Employee></Employee></Department><Department
id="40"><Employee></Employee></Department><Department
id="50"><Employee></Employee></Department><Department
id="60"><Employee></Employee></Department>
결과는 그리 깔끔하게 출력되지 않았습니다. 그리 간단한 예제는 아니었지만, ora:view 함수를 이용하여 관계형 데이터를 XML 데이터로 변환하고 XML 문서에 대해 JOIN 작업을 수행한 후 문서 내의 데이터에 대해 조건을 적용할 수 있음을 확인할 수 있었습니다.
? XMLQuery()와 XMLType 컬럼, FLWOR 표현식의 활용 사례. 위에서 생성된 invoicexml_col 테이블은 inv_doc XMLType 컬럼을 포함하고 있습니다. 이 컬럼에 저장되는 Invoice 데이터는 XML 포맷을 갖습니다. 이번 예제에서는 XMLQuery() 함수와 PASSING 절을 이용하여 XMLType 컬럼 inv_doc을 XQuery에 전달해 보겠습니다. 여기서 WHERE 조건절을 이용하여 Invoice 문서에서 특정 필드만을 반환하도록 할 수 있습니다. 다시 말해, XML 문서 전체를 CLOB으로 취급하는 대신 조건절을 이용하여 문서 내부의 특정 필드만을 확인할 수 있는 것입니다.
아래의 SELECT 구문은 invoicexml_col의 모든 로우에 적용됩니다. 그런 다음 FOR 구문을 이용하여 모든 invoice 로우에 대해 같은 작업을 반복합니다. 그리고 WHERE 조건절을 이용하여 Zipcode 12345를 갖는 엘리먼트를 추려낸 후, City, State, Zipcode를 반환합니다. 또 IF...THEN...ELSE 컨스트럭트를 이용하여 추가적인 기능 확장이 가능합니다.
Select XMLQuery(
'FOR $i in /Invoice
WHERE $i/MailAddressTo/Zipcode = 12345
RETURN <Details>
<Zipcode num="{$i/MailAddressTo/Zipcode}"/>
<CityName char="{$i/MailAddressTo/City}"/>
<City>{IF ($i/MailAddressTo/City = "New York")
THEN "Correct City"
ELSE "Incorrect City"}
</City>
<State>{if ($i/MailAddressTo/State = "NY")
then "Correct State" else "Incorrect State"}
</State>
</Details>'
PASSING inv_doc RETURNING CONTENT) ny_invoice
FROM invoicexml_col;
NY_INVOICE
<Details><Zipcode num="12345"></Zipcode>
<CityName char="New York"></CityName>
<City>Correct City</City>
<State>Correct State</State>
</Details>
두 가지 쿼리 모두 XML 문서에 대해 매우 세부적인 작업을 수행하고 있습니다. 이처럼 XMLQuery를 이용하여 관계형 데이터에서처럼 복잡한 작업을 실행하는 것이 가능합니다!
XMLTable() 함수. XMLTable() 함수는 XML 값을 테이블 또는 셋(se)으로 변환하는 기능을 제공합니다. XMLTable()은 XQuery의 실행 결과로 (시퀀스가 아닌) 테이블과 컬럼을 반환하기 위해 사용됩니다. XMLType 데이터를 쿼리하고, XML 결과를 관계형 포맷으로 분할(split)하여 가상적인 테이블을 생성할 수 있습니다. 이렇게 생성된 가상 테이블은 다른 테이블에 데이터를 INSERT하거나 쿼리를 수행하는 목적으로 활용됩니다. XML 데이터에 대해 관계형 뷰를 생성하는 것 또한 가능합니다. XMLTable() 함수는 SQL From 절 안에서 사용될 수도 있습니다.
? XMLTable()과 COLUMNS 구문의 활용 예제. 앞에서 생성한 invoicexml_col table을 이용하여, XML 데이터를 관계형 포맷으로 변환하기 위한 XMLTable() 활용 예제를 소개합니다. 아래 예제에서, XMLTable()은 inv_doc 컬럼에 저장된 Invoice 문서에 액세스합니다. 데이터 엘리먼트의 경로는 COLUMNS 구문을 통해 새로운 컬럼 네임과 포맷으로 매핑됩니다. XMLTable() 함수는 데이터를 가상 테이블로 반환하며, 쿼리 결과는 관계형 테이블을 쿼리한 경우와 동일합니다. 쿼리 마지막 부분의 WHERE 절을 눈 여겨 보시기 바랍니다. 예제에서는 관계형 데이터에 대해 SQL 쿼리를 적용하는 경우와 동일한 방법으로 XML 데이터를 필터링하고 있습니다.
쿼리와 그 실행 결과가 아래와 같습니다:
SELECT inv_id, a.PersonName, a.StreetName, a.CityName, a.State, a.Zipcode
FROM invoicexml_col,
XMLTABLE('/Invoice'
PASSING invoicexml_col.inv_doc
COLUMNS
PersonName varchar2(10) PATH '/Invoice/MailAddressTo/Person',
StreetName varchar2(20) PATH '/Invoice/MailAddressTo/Street',
CityName varchar2(10) PATH '/Invoice/MailAddressTo/City',
State varchar2(5) PATH '/Invoice/MailAddressTo/State',
Zipcode varchar2(7) PATH '/Invoice/MailAddressTo/Zipcode'
) a
WHERE a.CityName like 'New%';
INV_ID PERSONNAME STREETNAME CITYNAME STATE ZIPCODE
1 Joe Smith 10 Apple Tree Lane New York NY 12345
XML 쿼리의 성능 개선
지금까지 XML 문서 내부의 정보를 조회하는 방법을 확인하였습니다. 지금부터는 쿼리의 실행 속도를 개선하는 방법에 대해 설명하기로 합니다. XML 쿼리의 XML 데이터 액세스의 성능을 개선하기 위해 인덱스를 생성하고 실행 계획을 변경하는 것이 가능합니다. XPath 함수를 튜닝하는 방법은 기존의 SQL 튜닝 방식과 크게 다르지 않습니다. 경우에 따라서는 XML 쿼리의 구조를 변경하여 액세스 성능을 개선할 수도 있습니다.
함수 기반 인덱스는 구조형/비구조형 XMLType 테이블에 대해 모두 적용 가능하며, 테이블의 스키마 기반 여부에 관계없이 사용될 수 있습니다. 또 0 또는 1의 값만을 반환하는 exitstNode와 같은 함수를 위해 바이너리 인덱스의 적용을 검토해 볼 수 있을 것입니다.
이번 섹션에서는 실행 계획을 이용해서 구조형/비구조형 인덱스의 성능을 개선하는 방법에 대해 알아 보기로 합니다. 먼저 관계형 테이블에 대해 ora:view를 이용하는 XQuery 표현식을 최적화해 보겠습니다.
XQuery 표현식에서관계형데이터최적화하기. 앞에서 설명한 것과 유사한 쿼리를 이용한 튜닝 사례를 살펴 봅시다. 아래 쿼리는 ora:view 함수와 FLWOR 표현식을 이용하여 관계형 데이터에 접근하고 있습니다. 이 쿼리는 HR 스키마의 Employees, Departments 관계형 테이블을 JOIN하고 (commision > 3)의 조건을 만족하는 모든 employee를 반환합니다. 속성의 이름(예: $emp/ROW/COMMISSION_PCT의 JOB)은 대소문자를 구분함에 주의하시기 바랍니다.
아래 쿼리에서, Departments 테이블의 Employees 엘리먼트에 대해 반복 작업을 수행하기 위해 FOR를 사용하고 있습니다. Employees 테이블의 로우는 $emp 변수에, Departments 테이블의 로우는 $dep에 바인드됩니다. WHERE 절은 두 개의 테이블 간의 JOIN을 수행하고 "commission_pct > 3"의 조건을 만족하는 모든 emplyeee를 추려냅니다. RETURN은 department 정보를 반환합니다.
쿼리와 쿼리에 사용되는 실행 계획이 아래와 같습니다:
explain plan for
SELECT XMLQuery('for $emp in ora:view("EMPLOYEES"), $dep in ora:view("DEPARTMENTS")
where $emp/ROW/DEPARTMENT_ID = $dep/ROW/DEPARTMENT_ID
and $emp/ROW/COMMISSION_PCT > .3
return $dep'
RETURNING CONTENT) AS high_commission_employees
FROM DUAL;
QUERY_PLAN OBJECT_NAME COST BYTES LEVEL
SELECT STATEMENT 2 1
SORT 82 2
HASH JOIN 5 328 3
TABLE ACCESS EMPLOYEES 2 104 4
TABLE ACCESS DEPARTMENTS 2 1512 4
FAST DUAL 2 2
위에서 옵티마이저가 인덱스를 사용하고 있지 않음을 확인할 수 있습니다. ora:view 함수가 관계형 쿼리의 결과를 XML 엘리먼트로 반환하고 있기 때문에, 관계형 테이블에 B-트리 인덱스를 생성하는 방법을 고려해 볼 수 있을 것입니다.
Create index emp_idx1 on employees (department_id, commission_pct);
Create index dept_idx1 on departments (department_id);
QUERY_PLAN OBJECT_NAME COST BYTES LEVEL
SELECT STATEMENT 2 1
SORT 82 2
TABLE ACCESS DEPARTMENTS 1 56 3
NESTED LOOPS 2 328 4
INDEX EMP_IDX1 1 104 5
INDEX DEPT_IDX1 0 5
FAST DUAL 2 2
이번에는 인덱스가 사용되었으며, 코스트도 약간 줄어 들었습니다. ora:view를 이용하여 XQuery 표현식을 튜닝하는 방법은 일반 SQL 구문을 튜닝하는 경우와 매우 유사합니다. 표준 SQL과 마찬가지로 쿼리를 재작성하고 바인드 변수를 활용하면 성능을 더욱 개선할 수 있습니다.
구조형 XMLType 데이터의최적화. XPath rewrite 기능을 이용하여 구조형(오브젝트-관계형) 스토리지를 최적화하는 것이 가능합니다. 또 옵티마이저는 내부적인 성능 개선을 위한 또 다른 기능을 제공합니다. 아래 조건을 만족하는 경우, 옵티마이저를 이용해서 XPath 기반 함수를 관계형 구문으로 변환할 수 있습니다:
- XMLType 데이터가 등록된 XMLSchema를 사용하는 경우. (스키마 기반 데이터를 사용하는 경우 제공되는 여러 가지 이점 중 하나입니다.)
- 속성이 하부 관계형 테이블로 매핑되는 경우.
- 구조형 스토리지가 사용되는 경우.
이러한 경우라면, 성능을 위해 query rewrite를 활용하는 것이 가능합니다. 또 위에서 설명된 것과 같은 테크닉을 이용하여 관계형 쿼리의 성능을 개선할 수 있습니다.
이제 구조형 XMLType 데이터를 최적화해 봅시다. 먼저 앞에서 생성된 invoicexml_tbl 테이블을 이용한 간단한 예를 살펴 보겠습니다.
explain plan for
select extract(object_value, '/Invoice/MailAddressTo')
from invoicexml_tbl
where extractValue(object_value, '/Invoice/MailAddressTo/Person')= 'Joe Smith';
The access path is shown below.
QUERY_PLAN OBJECT_NAME COST BYTES LEVEL
SELECT STATEMENT 3 87 1
TABLE ACCESS INVOICEXML_TBL 3 87 2
이 쿼리의 조건절에 대해 함수 기반 인덱스를 생성할 수 있습니다. 인덱스는 쿼리에 명시된 것과 동일한 문법으로 생성되어야 합니다. 이제 다시 한 번 쿼리를 실행하여 새로운 인덱스가 사용되고 있는지 확인해 봅시다.
Create index invoicexml_tbl_idx1 on invoicexml_tbl
(extractValue(object_value, '/Invoice/MailAddressTo/Person'));
QUERY_PLAN OBJECT_NAME COST BYTES LEVEL
SELECT STATEMENT 1 87 1
TABLE ACCESS INVOICEXML_TBL 1 87 2
INDEX INVOICEXML_TBL_IDX1 1 3
새로 생성된 함수 기반 인덱스가 사용되고 있으며, 그 결과로 쿼리의 코스트가 줄어 들었습니다.
비구조형 XMLType 데이터의최적화.
이제 비구조형 XMLType 데이터를 이용해서 같은 테스트를 수행해 보겠습니다. invtest_unstruct 테이블은 invoicexml_tbl과 동일한 방법으로 생성됩니다. 단, 비구조형 데이터임을 정의하기 위해 "XMLType store as CLOB" 조건을 추가해야 합니다. 실행되는 DDL 구문, INSERT 구문, explain plan이 아래와 같습니다:
create table invtest_unstruct of XMLtype
XMLType store as CLOB;
Insert into invtest_unstruct values (
XMLType(bfilename('XMLDIR', 'invoicexml.txt'),
nls_charset_id('WE8MSWIN1252') ));
explain plan for
select extract(object_value, '/Invoice/MailAddressTo')
from invtest_unstruct
where extractValue(object_value, '/Invoice/MailAddressTo/Person')='Joe Smith'
/
QUERY_PLAN OBJECT_NAME COST BYTES LEVEL
SELECT STATEMENT 2 2002 1
TABLE ACCESS INVTEST_UNSTRUCT 2 2002 2
이제 함수 기반 인덱스를 생성하고, 생성된 인덱스가 사용되는지 확인해 보아야 합니다. "create index" 구문은 앞에서 사용된 구문과 동일합니다.
Create index invtest_unstruct_idx1 on invtest_unstruct
(extractValue(object_value, '/Invoice/MailAddressTo/Person'));
QUERY_PLAN OBJECT_NAME COST BYTES LEVEL
SELECT STATEMENT 1 2002 1
TABLE ACCESS INVTEST_UNSTRUCT 1 2002 2
INDEX INVTEST_UNSTRUCT_IDX1 1 3
위에서, 구조형/비구조형 데이터에 대해 동일한 인덱스와 쿼리를 효과적으로 활용할 수 있음을 확인하였습니다. 하지만 일반적으로 구조형 데이터에 대한 액세스가 비구조형 데이터에 대한 액세스보다 효과적임을 참고하시기 바랍니다.
결론
오라클 데이터베이스의 XML 표준 지원 기능이 갈수록 강화되고 있습니다. Oracle9i Database에서 처음 소개된 XML DB 리포지토리와 XMLType 데이터타입은 LOB/구조형 스토리지 옵션을 모두 지원합니다. Oracle XML DB는 URI를 데이터베이스 오브젝트로 매핑하는 계층형 모델을 통해 데이터베이스 내부에서 네이티브 XML 프로세싱 기능을 구현할 수 있게 합니다. 또 노드/경로의 개념을 이용하여 "논리적 트리" 구조를 갖는 문서의 개별 엘리먼트들에 대해 작업을 수행하기 위해 XPath 표현식이 사용됩니다. Oracle9i Database는 XMLSchema도 함께 지원하고 있습니다.
W3C의 XML 표준인 XQuery 언어는 Oracle Database 10g Release 2에서 처음으로 구현되었으며, XMLQuery(), XMLTable() 함수를 통해 지원됩니다. 이러한 기능을 조합하면, Oracle Database 10g Release 2에서 관계형 데이터와 XML 데이터를 호환적으로 활용할 수 있습니다. 본 문서를 통해 예시되었듯, XML 데이터에 대해 SQL 쿼리를 수행하거나 관계형 데이터에 대해 XML 쿼리를 실행할 수 있습니다. 이를 위해 활용되는 오라클의 독자적인 SQL/XML 기능으로 XMLQuery()와 XMLTable() 함수가 있습니다.
오라클의 표준 기반 접근법은 FTP, HTTP, WebDAV 등의 프로토콜을 지원하므로, 일반 클라이언트 툴과 애플리케이션을 이용하여 데이터베이스에 저장된 XML 데이터를 접근, 편집, 배포하는 것이 가능합니다.
관계형 데이터베이스에 관련한 기존의 스킬과 본 문서에 설명된 SQL/XML 기능을 함께 조합함으로써 한층 개선된 환경을 구현할 수 있을 것입니다. 또 오라클의 인덱싱, explain, 스토리지 기능을 이용하여 XML 데이터의 접근 성능을 개선할 수 있습니다. 설계담당자, 개발자, DBA 들은 빠르게 변화하는 테크놀로지 환경에 신속하게 대응할 수 있어야 합니다.
Tim Quinlan [tquinlan@tlqconsulting.com]은 Oracle 7 이후 Oracle10g Release 2에 이르기까지 풍부한 경험을 보유하고 있습니다. 팀은 1981년부터 데이터베이스 관련 업무를 수행해 왔으며, 엔터프라이즈 데이터 웨어하우스 및 OLTP 데이터베이스의 DBA, 아키텍트, 설계 및 구축 업무를 담당해 왔습니다. 팀은 다양한 컨퍼런스, 교육 세션에 강사로 참여하였으며, 주요 데이터베이스 전문지에 기고해 왔습니다. 팀은 대용량, 고성능, 고가용성 데이터베이스 시스템의 설계 및 구축을 전문 분야로 하고 있습니다.
|
