XML 텍스트 한줄로
1. 함수 요약 정리
| 함수 | 역할 | 비유 |
| XMLELEMENT | 데이터를 XML 태그로 감싸기 | 선물을 상자에 담기 (<태그>값</태그>) |
| XMLAGG | 여러 행의 XML 조각을 하나로 합치기 | 여러 상자를 테이프로 붙여 큰 묶음 만들기 |
| EXTRACT | XML 문서 내 특정 경로 데이터 추출 | 큰 묶음 상자에서 특정 내용물만 꺼내기 |
| RTRIM | 문자열 오른쪽의 공백이나 문자 제거 | 상자 오른쪽 끝에 붙은 불필요한 테이프 떼기 |
2. 네 가지 함수를 한 번에 사용한 예제
상황: 사원 테이블(EMP)에서 부서별로 사원 이름을 수집하되, 이름 뒤에 붙은 불필요한 점(.)을 제거(RTRIM)한 뒤, 이를 XML 형식으로 합쳐서(XMLAGG, XMLELEMENT) 관리하고, 최종적으로 그중 특정 노드만 확인(EXTRACT)하는 예제입니다.
SQL
SELECT
DEPTNO,
-- 4. EXTRACT: 생성된 전체 XML에서 <EmployeeList> 태그 내용만 추출
EXTRACT(
-- 1. XMLELEMENT: 'DeptGroup'이라는 루트 태그 생성
XMLELEMENT("DeptGroup",
-- 2. XMLAGG: 여러 행의 <Name> 태그를 하나로 뭉침
XMLAGG(
XMLELEMENT("Name",
-- 3. RTRIM: 이름 끝에 혹시 있을지 모를 마침표(.)나 공백 제거
RTRIM(ENAME, '.')
)
)
),
'/DeptGroup/Name'
) AS XML_EMPLOYEES
FROM (
-- 테스트를 위해 이름 끝에 점(.)이 붙은 가상 데이터를 포함
SELECT 10 AS DEPTNO, 'SMITH.' AS ENAME FROM DUAL UNION ALL
SELECT 10, 'ALLEN ' FROM DUAL UNION ALL
SELECT 20, 'SCOTT.' FROM DUAL
)
GROUP BY DEPTNO;
3. 코드 해설 (작동 순서)
- RTRIM(ENAME, '.'): 'SMITH.' 처럼 이름 끝에 점이 있으면 'SMITH'로 깔끔하게 정리합니다.
- XMLELEMENT("Name", ...): 정리된 이름을 <Name>SMITH</Name> 형태로 만듭니다.
- XMLAGG(...): 같은 부서원들의 태그를 합쳐 <Name>SMITH</Name><Name>ALLEN</Name>으로 만듭니다.
- XMLELEMENT("DeptGroup", ...): 전체를 <DeptGroup> 태그로 한 번 더 감쌉니다.
- EXTRACT(..., '/DeptGroup/Name'): 최종 XML 뭉치에서 /DeptGroup/Name 경로에 해당하는 데이터만 결과로 보여줍니다.
1. 단계별 데이터 변화 (Conceptual)
먼저 쿼리 내부에서 데이터가 어떻게 변하는지 과정을 따라가 보겠습니다.
| 단계 | 처리 내용 | SMITH 행의 변화 |
| 원래 데이터 | ENAME 컬럼 값 | 'SMITH.' (점 포함) |
| RTRIM 적용 | 오른쪽 점(.) 제거 | 'SMITH' |
| XMLELEMENT | 태그로 감싸기 | <Name>SMITH</Name> |
| XMLAGG | 부서원끼리 합치기 | <Name>SMITH</Name><Name>ALLEN</Name> |
| EXTRACT | 특정 경로 추출 | (위와 동일한 XML 조각) |
2. 최종 쿼리 결과 화면
실제 SQL Tool(SQL Developer, DBeaver 등)에서 조회하면 아래와 같은 형태로 출력됩니다.
| DEPTNO | XML_EMPLOYEES (추출된 데이터) |
| 10 | <Name>SMITH</Name><Name>ALLEN</Name> |
| 20 | <Name>SCOTT</Name> |
3. XML 구조 상세 보기
결과 컬럼(XML_EMPLOYEES)에 들어있는 내용은 텍스트로 풀어서 보면 이렇습니다.
부서 10번의 결과:
XML
<Name>SMITH</Name>
<Name>ALLEN</Name>
- RTRIM 덕분에 SMITH.의 점이 사라졌습니다.
- XMLAGG 덕분에 두 명의 이름이 나란히 붙어 있습니다.
- EXTRACT를 통해 부모 태그인 <DeptGroup>은 제외하고 알맹이인 <Name> 태그들만 골라낸 상태입니다.
부서 20번의 결과:
XML
<Name>SCOTT</Name>
📂 자주 쓰는 XPath 문법 정리
오라클에서 XML 데이터를 다룰 때 EXTRACT나 XMLQUERY 함수와 함께 자주 사용하는 XPath 핵심 문법
| 패턴 | 의미 | 설명 |
| / | 루트(최상위) | 절대 경로의 시작 (예: /Root/Name) |
| // | 전체 검색 | 위치에 상관없이 문서 내 모든 해당 요소 검색 (예: //Name) |
| . | 현재 노드 | 현재 내가 머물고 있는 위치 |
| .. | 부모 노드 | 현재 위치의 바로 위 단계 노드 |
| @ | 속성(Attribute) | 태그 안의 속성값을 지칭 (예: <Emp id="1">에서 @id) |
| text() | 텍스트 노드 | 태그를 제외한 순수 문자열 값만 선택 |
| * | 와일드카드 | 모든 요소(태그)를 선택 |
🛠️ 예시
아래와 같은 XML 데이터가 있다고 가정해 보겠습니다.
XML
<Dept id="10">
<Staff>
<Name>SCOTT</Name>
<Job>ANALYST</Job>
</Staff>
<Staff>
<Name>TIGER</Name>
<Job>CLERK</Job>
</Staff>
</Dept>
1. 모든 텍스트만 뽑고 싶을 때 (가장 많이 씀)
- 경로: //text()
- 결과: SCOTTANALYSTTIGERCLERK
- 용도: 태그 싹 다 무시하고 데이터 내용만 합쳐서 보고 싶을 때 사용합니다.
2. 특정 태그 아래의 값만 뽑고 싶을 때
- 경로: /Dept/Staff/Name
- 결과: <Name>SCOTT</Name>, <Name>TIGER</Name>
- 용도: 정확한 구조를 알고 있을 때 데이터를 안전하게 추출합니다.
3. 태그 안의 속성값(ID 등)을 뽑고 싶을 때
- 경로: /Dept/@id
- 결과: 10
- 용도: <Dept id="10">처럼 태그 이름이 아닌 속성으로 저장된 값을 가져올 때 씁니다.
4. 조건에 맞는 노드만 뽑고 싶을 때 (필터링)
- 경로: //Staff[Name='SCOTT']
- 결과: 이름이 SCOTT인 사원의 전체 <Staff> 노드
- 용도: XML 데이터 덩어리 안에서 특정 조건만 검색할 때 유용합니다.
💡 요약하자면
- //는 "어디에 있든 다 찾아라"
- text()는 "태그 빼고 글자만 가져와라"
- @는 "속성(ID 등)을 가져와라"
CASE문
1. 기본 문법 구조
CASE로 시작해서 END로 끝나야 한다는 점이 가장 중요
SQL
CASE
WHEN 조건1 THEN 결과1
WHEN 조건2 THEN 결과2
ELSE 모든 조건에 해당하지 않을 때의 결과
END
2. 두 가지 사용 방식
① 특정 컬럼의 값을 비교할 때 (단순 CASE)
특정 컬럼 하나를 두고 여러 값을 비교할 때 씁니다.
SQL
SELECT ENAME,
CASE JOB
WHEN 'PRESIDENT' THEN '대표님'
WHEN 'MANAGER' THEN '관리자'
ELSE '직원'
END AS 권한
FROM EMP;
② 복잡한 조건을 걸 때 (검색 CASE)
비교 연산자(<, >, <>)나 여러 컬럼을 조합한 조건을 걸 때 씁니다. (실무에서 더 많이 사용됩니다.)
SQL
SELECT ENAME, SAL,
CASE
WHEN SAL >= 3000 THEN '고액 연봉'
WHEN SAL >= 2000 THEN '평균 연봉'
ELSE '미달'
END AS 급여등급
FROM EMP;
3. 우리가 배운 함수들과 섞어 쓴다면? (심화 예제)
앞서 배운 XMLAGG, XMLELEMENT, RTRIM과 CASE를 모두 섞어서 "부서별로 특정 직급인 사람들의 명단만 XML로 만드는" 아주 실무적인 예제를 만들어 보겠습니다.
SQL
SELECT
DEPTNO,
-- XML 생성
XMLELEMENT("StaffList",
XMLAGG(
XMLELEMENT("Name",
-- CASE 사용: 이름 뒤에 직급에 따른 별칭을 붙여서 정제
RTRIM(ENAME) ||
CASE
WHEN JOB = 'PRESIDENT' THEN '(CEO)'
WHEN JOB = 'MANAGER' THEN '(MG)'
ELSE ''
END
)
)
).extract('//text()') AS STAFF_INFO
FROM EMP
GROUP BY DEPTNO;
🔍 예제 해석
- CASE WHEN: 직업(JOB)이 사장이면 이름 뒤에 (CEO)를, 관리자면 (MG)를 붙여줍니다.
- RTRIM: 이름에 혹시 모를 공백을 지워줍니다.
- XMLELEMENT / XMLAGG: 가공된 이름들을 <Name> 태그로 감싸고 하나로 합칩니다.
- extract('//text()'): 최종적으로 태그를 떼고 글자만 보여줍니다.
💡 요약 및 주의사항
- ELSE는 생략 가능하지만, 생략했을 때 조건에 맞는 게 하나도 없으면 NULL을 반환합니다. 가급적 ELSE를 써주는 것이 안전합니다.
- CASE 문은 위에서부터 순차적으로 조건을 검사합니다. 첫 번째 WHEN 조건이 맞으면 바로 그 값을 반환하고 종료되므로, 범위가 좁은 조건을 위쪽에 배치하는 것이 좋습니다.