데이터베이스 질문과 답변
안녕하세요
아래와 같이 순환 참조 형태의 데이터를 XML 문서 형태로 만들고 싶습니다.
쿼리로만 가능할까요? (INPUT = 기본 데이터, OUTPUT = 출력물)
# 컬럼 TYPE 에 따라 XML 형태 결정
* 기본 데이터
WITH TAB AS (
SELECT '0001' AS ID , '' TYPE, 'BOOK' NAME , '' VALUE , '' MANAGE FROM DUAL UNION ALL
SELECT '0100' AS ID , '' TYPE, 'INFO' NAME , '' VALUE , '0001' MANAGE FROM DUAL UNION ALL
SELECT '0110' AS ID , 'ELEMENT' TYPE, 'NAME' NAME , 'HERYPOTTER' VALUE , '0100' MANAGE FROM DUAL UNION ALL
SELECT '0120' AS ID , 'ELEMENT' TYPE, 'AUTH' NAME , 'HENNY' VALUE , '0100' MANAGE FROM DUAL UNION ALL
SELECT '0200' AS ID , '' TYPE, 'DESC' NAME , '' VALUE , '0001' MANAGE FROM DUAL UNION ALL
SELECT '0210' AS ID , '' TYPE, 'TYPEA' NAME , '' VALUE , '0200' MANAGE FROM DUAL UNION ALL
SELECT '0211' AS ID , 'ATTRIBUTE' TYPE, 'PAGES' NAME , '420' VALUE , '0210' MANAGE FROM DUAL UNION ALL
SELECT '0212' AS ID , 'ATTRIBUTE' TYPE, 'LANGUAGE' NAME , 'KOREAN' VALUE , '0210' MANAGE FROM DUAL
)
SELECT LPAD(' ', 4 * (LEVEL - 1)) || NAME,
A.* FROM TAB A
START WITH MANAGE IS NULL
CONNECT BY PRIOR ID = MANAGE
* 출력물
<?xml version="1.0" encoding="UTF-8"?>
<BOOK>
<INFO NAME = "HERYPOTTER" AUTH = "HENNY">
</INFO>
<DESC>
<TYPEA>
<PAGES>420</PAGES>
<LANGUAGE>KOREAN</LANGUAGE>
</TYPEA>
</DESC>
</BOOK>
WITH tab AS
(
SELECT '0001' id, '' type, 'BOOK' name, '' value, '' manage FROM dual
UNION ALL SELECT '0100', '' , 'INFO' , '' , '0001' FROM dual
UNION ALL SELECT '0110', 'ELEMENT' , 'NAME' , 'HERYPOTTER', '0100' FROM dual
UNION ALL SELECT '0120', 'ELEMENT' , 'AUTH' , 'HENNY' , '0100' FROM dual
UNION ALL SELECT '0200', '' , 'DESC' , '' , '0001' FROM dual
UNION ALL SELECT '0210', '' , 'TYPEA' , '' , '0200' FROM dual
UNION ALL SELECT '0211', 'ATTRIBUTE', 'PAGES' , '420' , '0210' FROM dual
UNION ALL SELECT '0212', 'ATTRIBUTE', 'LANGUAGE', 'KOREAN' , '0210' FROM dual
)
, tab1 AS
(
SELECT id, type, name, value, manage
FROM tab
UNION ALL
SELECT 'z' || id AS id
, '' AS type
, '/'||name AS name
, '' AS value
, id AS manage
FROM tab
WHERE type IS NULL
)
SELECT '<?xml version="1.0" encoding="UTF-8"?>' x
FROM dual
UNION ALL
SELECT DECODE('ELEMENT'
, LEAD(type) OVER(ORDER BY rn), REPLACE(x, '>', '')
, LAG (type) OVER(ORDER BY rn), REPLACE(x, '<', '><')
, x) x
FROM (SELECT id, type, name, value, manage
, DECODE(type
, 'ELEMENT', ' '||name||'="'||value||'"'
, 'ATTRIBUTE', REPLACE(XMLELEMENT(x, value), 'X>', name||'>')
, '<'||name||'>'
) x
, LEVEL lv
, ROWNUM rn
FROM tab1
START WITH manage IS NULL
CONNECT BY PRIOR id = manage
ORDER SIBLINGS BY id
)
;
헐!~ 결과가 제대로 나오네요...
감사합니다. SQL로 불가능이 없군요
대단한 내공이 느껴집니다
) 버튼을 클릭하여 작성 하시면 됩니다.