Oracle XML 쿼리

출처 : http://www.oracleclub.com/article/55741

설명은 안 하겠습니다.

 

WITH t AS
( 
SELECT XMLTYPE('
<ROOT>
<META use="report">
<META-NAME>Type1</META-NAME>
<META-VALUE>REQ1</META-VALUE>
</META>
<META use="report">
<META-NAME>Type2</META-NAME> 
<META-VALUE>REQ3</META-VALUE> 
</META>
<META use="report">
<META-NAME>Type3</META-NAME>
<META-VALUE>REQ4</META-VALUE>
</META>
</ROOT>
') v 
FROM dual 
) 
SELECT ExtractValue(Value(p), '/META/META-VALUE') "META-VALUE"
  FROM t 
     , TABLE(XMLSequence(Extract(v, '/ROOT/META[@use="report"]'))) p 
 WHERE ExtractValue(Value(p), '/META/META-NAME') = 'Type1'
; 

Tistory 태그: oracle,sql,query,xml,xmltype,오라클,쿼리