Last night, I have got request from my customer. He wanted to know how many XML nodes are there in XML log table placed in Oracle. I tried to find sufficient oracle function but couldn`t. So I make slightly dirty SQL query but it was very helpful to reduce manual job. Let`s see SQL query first.
SELECT xml_seq,
to_char((length(xml_recv)-length(replace(xml_recv,'<ITEM>',''))) / length('<ITEM>')) as item_count
FROM tbl_xml_log
Look above SQL carefully. It looks dirty calculation about length of column but very simple. You can change <ITEM> to string that you want to count on specific column. It would be helpful for guys who want to count repeated character in specific column.
- NoPD -
