Oracle XML

De curand am avut de a face cu o tabela dintr-o schema de baze de date Oracle care continea toata informatiile utile in doar cateva coloane de tip CLOB, coloane care contineau toate XML-uri pline ciorchine de date interesante.

Metoda de a face o selectie a unui camp dintr-un XML stocat intr-un camp este:

select extractvalue(xmltype(report_failed),'//ProductAndServiceID/text()')
from table_...
where conditions...

unde campul este report_failed, iar ceea ce caut eu in XML este o expresie XPath care spune: gaseste-mi elementul ProductAndServiceID pe undeva prin XML (// la asta foloseste). Daca as vrea sa gasesc exact elementul pe care il caut, XPath-ul ar trebui sa fie: extractvalue(XMLTYPE(request),'/ServiceConfigurationRequest/ProductAndServiceID/text()' )

Daca vreau sa selectionez un atribut, nu un element, XPath-ul ar fi de genul:

extractvalue(XMLTYPE(request),'//TargetParameters/Parameter[@ParameterName = "MSISDN"]' )

atributul meu se numeste ParameterName si in plus are si valoarea MSISDN.

Interesant, dar si lent, este ca se pot scrie expressi de tip GROUP BY bazate pe campurile din XML, ca sa poti genera rapoarte:

select count(*), 
extractvalue(xmltype(report_failed),'//ProductAndServiceID/text()') product_id,
extractvalue(xmltype(report_failed),'//Verb/text()') verb,
extractvalue(xmltype(report_failed),'//ErrorMessage/text()') error_message
from table_...
where (current_status = 'FAIL')
and sys_creation_date > trunc(sysdate ) - 10
group by
extractvalue(xmltype(report_failed),'//ProductAndServiceID/text()'),
extractvalue(xmltype(report_failed),'//Verb/text()'),
extractvalue(xmltype(report_failed),'//ErrorMessage/text()')
order by 1, 2, 3, 4

Bafta !