Count number of rows where Extract Value found a match

Question

100,000 rows in a table which contains a column that stores a large XML block, I need to check if there is a certain XML tag is filled with data in this column, lets say the column is called test_request and the XML tag is named 'd'. Also I want to make sure that the value inside 'd' doesn't contain a newline /n within the XML tag. So for every row that has a match I want to add 1 to a overall count. Here is my query so far.

SELECT EXTRACTVALUE( UNCOMPRESS(`test__request` ) ,  count('/a/b/c/d') ) 
FROM testTable16
WHERE  `test_created` >  '2014-08-16 10:00:00'
AND  `test_created` <=  '2014-08-16 10:10:00'
AND  `test_client` =  'test2'
AND  `test_user` =  'testuser2'
AND UNCOMPRESS(  `test__request` ) LIKE  '%<testID>test</testID>%'
LIMIT 0 , 30

it doesn't work though as it returns 100,000 rows which I cant obviously sift through. And I am not sure how to do the isnt newline check.

mysql, sorry stackoverflow told me it was a good idea to add SQL as a tag. — Matthew Underwood, Aug 18 '14 at 14:23
just as a good rule of thumb keep your tags as specific as possible.. mysql doesn't support everything sql does so it needs to be clarified :) — John Ruddell, Aug 18 '14 at 14:25
what comes out from your extractvalue() select? can we see some data? :) — John Ruddell, Aug 18 '14 at 14:26
The returned is the first 30 rows contains 0 (the rest could contain either 0 or 1, the point is I cant search all the rows there are too many which is why I need a total of matches) — Matthew Underwood, Aug 18 '14 at 14:39
If I put in a Xpath of a tag I know thats always there the rows return a 1. — Matthew Underwood, Aug 18 '14 at 14:41

Arth · Accepted Answer · 2014-08-18T14:49:32.057

1

If you only to return rows with a count, you should move your count to the WHERE clause.

My XPATH is a little rusty, but I believe you can use a predicate with the contains function:

SELECT *
FROM testTable16
WHERE  `test_created` >  '2014-08-16 10:00:00'
AND  `test_created` <=  '2014-08-16 10:10:00'
AND  `test_client` =  'test2'
AND  `test_user` =  'testuser2'
AND UNCOMPRESS(`test__request`) LIKE  '%<testID>test</testID>%'
AND EXTRACTVALUE( 
      UNCOMPRESS(`test__request`),  
      'count(/a/b/c/d[contains(text(),"\n")])'
    ) > 0
LIMIT 0 , 30

If you want to return a count of all rows that have at least one match use SELECT COUNT(*) ...

If you want a total of all the node counts use:

SELECT SUM(EXTRACTVALUE( 
         UNCOMPRESS(`test__request`),  
         'count(/a/b/c/d[contains(text(),"\n")])'
       ))
FROM testTable16
WHERE  `test_created` >  '2014-08-16 10:00:00'
AND  `test_created` <=  '2014-08-16 10:10:00'
AND  `test_client` =  'test2'
AND  `test_user` =  'testuser2'
AND UNCOMPRESS(`test__request`) LIKE '%<testID>test</testID>%'

edited Aug 18 '14 at 14:49

answered Aug 18 '14 at 14:41

Arth

12,789
5
37
69

I am getting invalid use of group function. – Matthew Underwood Aug 18 '14 at 14:47
@MatthewUnderwood Oh sorry, the count needs to be within the XPATH.. i'll update. – Arth Aug 18 '14 at 14:49
I think that has worked, the count goes down when I add more tags, which is what I was expecting, what I will do is put a limit 30 and check the XML data coming back. Then I will up vote you and mark you as correct. Wont be long. – Matthew Underwood Aug 18 '14 at 14:53
Cool, happy to help.. count goes down when you add more tags? do you mean to the `WHERE` conditions/XPATH conditions? If you mean to the XML the count should only go up! Bear in mind the `LIMIT` is applied last, so it won't affect a MySQL aggregate `COUNT(*)` or `SUM(...)`. – Arth Aug 18 '14 at 15:02
1

Yeah I was refering to the WHERE, all the rows should contain tag a and b but c and d can be optional (its specific to my work). Hence I expect there to be a lower count number when a/d/c rather then a/b. – Matthew Underwood Aug 18 '14 at 15:13
Thanks man, note that that solution may not be optimal performance-wise! – Arth Aug 18 '14 at 15:20

Count number of rows where Extract Value found a match

1 Answers1