오라클 SQL 레코드를 XML로 변환해주는 FUNCTION (오라클 xml 사용법 등)

MS-SQL의 SELECT ..... FOR XML AUTO 에 상응하는 오라클의 기능을 알아보도록하죠.

이게 무슨기능이냐구요? 바로 SQL 쿼리에 의해 반환된 레코드 집합을 XML로 표현하는겁니다.

 

요즘 프레임워크 설정부터 , 어플리케이션 인프라 Configuration , 웹서비스는 물론, Ajax,flex 등의 web rich client 등등..

XML의 사용빈도는 점점 증가 하고 있습니다.

이 시점에서 오라클에서 제공하는 XML 지원 패키지에 대해 알아보는 시간을 갖도록하죠.

 

 

시작)

SELECT 절에 의해 생성된 레코드를 Java 나 pro* c , .net 등의 미들 tier 에서 별도의 XML파싱 작업을 하지않고,

 

오라클에서 제공하는 패키지를 이용하여 손쉽게 XML으로 표현할수 있다.

 

9i와 10g에서 XML을 다루는 패키지가 다르니 주의하기바란다.

 

SELECT * FROM PRT 라는 SQL에 의해 반환되는 레코드는 아래와 같다.

 

 

이제 필자가 작성한 10g XML제네릭 함수를 보도록하자.

(9i, 8i 사용자들은 아래의 코드에서 DBMS_XMLQUERY 부분을 XMLGEN으로 바꾸면 된다.

독자중 XMLGEN 패키지가 없는 분들은 oracle.com에서 xsu12.tar.gz 를 다운받아 압축을 푼후, xsu12.jar를

오라클 스키마에 올리도록한다.

이때 사용법은    loadjava -verbose -resolve -user scott/tiger xsu12.jar 이다. (기타 자세한 사용법은 loadjava 을 검색하라.)

(혹은 http://blog.naver.com/jadin1/70018342609 이 아티클을 참고하길 바란다.)

 

 

 

 

 

함수코드)

-----------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION GET_XML_RECORD
          (XMLSQL IN VARCHAR2,
           ENCODINGTYPE IN VARCHAR2 := 'UTF-8') RETURN CLOB IS
    CTX DBMS_XMLQUERY.CTXHANDLE;
    XMLRECORD CLOB;
BEGIN
    
 --컨텍스트 얻어오기
    CTX := DBMS_XMLQUERY.NEWCONTEXT(XMLSQL);

    -- encoding 설정
    DBMS_XMLQUERY.SETENCODINGTAG(CTX, ENCODINGTYPE);

    -- xml 생성
    XMLRECORD := DBMS_XMLQUERY.GETXML(CTX);

    RETURN xmlRecord;
END GET_XML_RECORD;
/

 

create or replace function GET_XML_RECORD
          (xmlSQL IN VARCHAR2,
           encodingType IN VARCHAR2 := 'UTF-8') return CLOB is
 
   Ctx            dbms_xmlquery.ctxHandle; -- SQL에서 XML로 컨버팅된 객체 핸들
   xmlRecord      CLOB; --리턴될 값
   
begin
 
  --컨텍스트 얻어오기
  Ctx := dbms_xmlquery.newContext(xmlSQL);
 
  -- encoding 설정
  dbms_xmlquery.setEncodingTag(Ctx,encodingType);

  -- xml 생성
  xmlRecord := dbms_xmlquery.getXML(Ctx);

  RETURN xmlRecord;
  
end GET_XML_RECORD;
-----------------------------------------------------------------------------------------

 

 

사용예)

SELECT GET_XML_RECORD('SELECT * FROM PRT','EUC-KR') FROM DUAL;

 

 

 

결과값)

          

마지막으로 오라클 도큐먼트에서 도움이 될만한 부분 남깁니다.

URL : http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10802/d_xmlque.htm#1032794

 

 

 

 

 

DBMS_XMLQUERY패키지..

GETXML

Creates the new context, executes the query, gets the XML back and closes the context. This is a convenience function. The context doesn't have to be explicitly opened or closed. The options are described in the following table.

Syntax	Description
FUNCTION getXML( sqlQuery IN VARCHAR2, metaType IN NUMBER := NONE) RETURN CLOB;	This function uses a SQL query in string form.
FUNCTION getXML( sqlQuery IN CLOB, metaType IN NUMBER := NONE) RETURN CLOB;	This function uses a SQL query in CLOBform.
FUNCTION getXML( ctxHdl IN ctxType, metaType IN NUMBER := NONE) RETURN CLOB;	This function generates the XML document based on a SQL query used to initialize the context.
PROCEDURE getXML( ctxHdl IN ctxType, xDoc IN CLOB, metaType IN NUMBER := NONE);	This procedure generates the XML document based on the SQL query used to initialize the context.

Parameter	IN / OUT	Description
ctxHdl	(IN)	Context handle.
metaType	(IN)	XML metadata type (NONE, DTD, or SCHEMA).
sqlQuery	(IN)	SQL query.
xDoc	(IN)	CLOB into which to write the generated XML document.

 

위에서 특히나, GETXML 함수에 metaType을 지정하면 meta 정보 (DTD, Schema)가 포함되어 반환됩니다.

아주 유용하죠..

 

 

 

다시금 오라클의 강력함과 동시에 오라클에서 점점 B2B, EAI 에 적합한 XML Service에 비중을 많이 두는것 같은 기분이 듭니다.

[출처] [DEV.WON] ORACLE - SQL 레코드를 XML로 쉽게 변환하기|작성자 데브원

 

========================================================================================================================
========================================================================================================================

ORACLE 9I 에서 XMLTYPE COLUMN 사용 예제



Oracle 9i 에서 새롭게 나온 datatype 인 XMLType 에 대해 column 을 생성하고 drop 하고, 
data 를 insert, delete, update 하는 방법을 예제를 통해 알아 봅니다. 

1a. XMLType Column 을 생성하는 방법.
1b. XMLType column 을 추가하는 방법.
1c. XMLType column 을 drop 하는 방법.
2. XMLType column 에 data 를 insert 하는 방법.
3. XMLType Column 의 data 를 update 하는 방법.
4. XMLType Column 의 data 를 delete 하는 방법.
5a. XML Data select 하는 방법.
5b. XML Data select 하는 또 다른 예제.


Example
-------



1a. XMLType Column 을 생성하는 방법.

CREATE TABLE po_xml_tab
( 
poid number, 
poDoc SYS.XMLTYPE
); 


1b. XMLType column 을 추가하는 방법.


ALTER TABLE po_xml_tab ADD (custDoc sys.XMLType); 



1c. XMLType column 을 drop 하는 방법.

ALTER TABLE po_xml_tab DROP (custDoc ); 



2. XMLType column 에 data 를 insert 하는 방법.

createXML function 은 XML 이 well-formed 인지 검사하지만
validity 는 검사하지 않습니다.

INSERT INTO po_xml_tab VALUES
(100, sys.XMLType.createXML(' 
Po_1
John
1033, Main Street 
Sunnyvalue 
CA 

'));



3. XMLType Column 의 data 를 update 하는 방법.

update 는 XML document 전체만을 update 할 수 있습니다.


UPDATE po_xml_tab e
SET e.poDoc = sys.XMLType.createXML(
'
Po_2
Nance
2 Avocet Drive
Redwood Shores

')
WHERE e.poDoc.extract('/PO/@pono').getNumberVal() = 1;

UPDATE po_xml_tab e
SET e.poDoc = sys.XMLType.createXML(
'
Po_2
Nance
2 Avocet Drive
Redwood Shores

')
WHERE e.poDoc.extract('/PO/CUSTNAME/text()').getStringVal()='John'; 



4. XMLType Column 의 data 를 delete 하는 방법.

Extract 와 ExistsNode function 을 사용해서 delete 할 rows 를 
찾을 수 있습니다. 
Extract function 은 명시된 XPath 에 의해 document 의 node 나 
nodes 의 한 set 을 추출할 수 있습니다. 
추출된 nodes 는 elements 이거나 attributes, text nodes 일 수 있습니다.
ExistsNode function 은 주어진 XPath 에 의해 반화되는 XML element 나 
text node 가 최소 한개 이상인지 검사합니다. 
만일 한개 이상이라면 1 을 반환하고 그렇지 않을 경우에는 0을 반환합니다.


DELETE FROM po_xml_tab e 
WHERE e.poDoc.extract('/PO/PNAME/text()').getStringVal()='Po_2'; 


5a. XML Data select 하는 방법.

Xpath 를 ExistsNode 또는 Extract Function 와 같이 사용해서 select 할 수 
있습니다.

SELECT e.poDoc.extract('/PO/@pono').getNumberVal() pono 
FROM po_xml_tab e 
WHERE e.poDoc.existsnode('/PO/@pono') = 1 AND poid > 1; 

위의 query 에서 extract() 는 "PONO" tag 의 내용을 추출합니다. 
그리고 "PO" 의 child 로 "PONO" 가 존재하는지 existsnode() 로 확인하고 
있습니다. 

text() function 은 text nodes 만을 반환합니다. 
getNumberVal() function 은 text 값만을 숫자값으로 바꿀 수 있습니다.



5b. XML Data select 하는 또 다른 예제.

SELECT e.poDoc.extract('/PO/PNAME/text()').getStringVal() PNAME 
FROM po_xml_tab e 
WHERE e.poDoc.existsNode('/PO/SHIPADDR') = 1 AND 
e.poDoc.extract('/PO/@pono').getNumberVal() = 1 AND 
e.poDoc.extract('/PO/CUSTNAME/text()').getStringVal() LIKE '%John%'; 

위의 query 에서는 shipping address 가 존재하고 
purchase order number 가 2001 이고 고객명에 John 이 포함되어 있는 
PO element 로 부터 "PNAME" 을 추출하고 있습니다.

====================================================================================================

여러개의 로우에 특정값을 세로에서 가로로 표현하기 (XML)

데이터를 예를 들자면
순번 지역
------ ---------
1 서울
2 경기
3 인천
4 춘천

이러한 데이터가 있을때
이를 
서울,경기,인천,춘천 으로 표현을 하고 싶을때
사용을 하던 방법이
DECODE(순번, 1, 지역),
DECODE(순번, 2, 지역),
이렇게 표현을 했다.
이것을 XML 로 한번 바꿔 보자
SELECT XMLElement("post", 지역||',') FROM tpost 를 수행을 하면
결과 값은
서울,
경기,
인천,
춘천,

4 rows
로 표현이 된다. 이것은 오라클에 XML 데이터 형식이다.
어떻게 가로로 표현을 할것인가
오라클 SQL 레퍼런스를 참고 하시길...
XMLAgg 를 그룹함수가 있습니다.

SELCET XMLAgg(XMLElement("post", 지역||',')) FROM tpost 그러면
서울,경기,인천,춘천,
1 row
위와 같이 나옵니다.

자그럼 문자로 바꿔 보자 ...
이에 대한 설명을 오라클 sql 레퍼런스에 없다, 그럼 어디에 있나.
Oracle XML DB 개발자 가이드에 내용을 참고 하십시요
최종적으로
SELCET RTRIM(REGEXP_REPLACE(XMLAgg(XMLElement("post", 지역||',')).getStringVal(), '(|)', ''), ',') FROM tpost


결과는
서울,경기,인천,춘천
REGEXP_REPLACE는 10g에서만 지원을 하니깐 이하버전에서는 REPLACE를 두번을 사용을 하시면 됩니다.

출처 : 

http://blog.naver.com/PostList.nhn?blogId=tyboss&skinType=&skinId=&from=menu&userSelectMenu=true