Group by a result of a xml query

Question

I have table with a XML column (called MetaData) which looks like this:

<props>
  <prop name="bytes" value="194" />
  <prop name="error" value="File is a text file" />
  <prop name="mime-type" value="text/plain " />
</props>

Now I have some differnt errors which I can select with this here:

SELECT MetaData.value('(/props/prop[@name="error"]/@value)[1]', 'varchar(50)') Error,
       MetaData.value('(/props/prop[@name="mime-type"]/@value)[1]', 'varchar(50)') MimeType,
       *
FROM source
WHERE MetaData.exist('/props/prop[@name="error"]') = 1

Now I would like to count how often a error accours:

SELECT MetaData.value('(/props/prop[@name="error"]/@value)[1]', 'varchar(50)') Error,
       COUNT(*) Count
FROM source
WHERE MetaData.exist('/props/prop[@name="error"]') = 1
GROUP BY Error

But I get the error message:

Meldung 207, Ebene 16, Status 1, Zeile 5
Ungültiger Spaltenname 'Error'.

Which means something like: Invalid column name 'Error'

I also tried this here:

Select Error, COUNT(Error) FROM (
    SELECT MetaData.value('(/props/prop[@name="error"]/@value)[1]', 'varchar(50)') Error
    FROM videos
    WHERE MetaData.exist('/props/prop[@name="error"]') = 1
)
GROUP BY Error

But that crashes with:

Meldung 156, Ebene 15, Status 1, Zeile 6
Falsche Syntax in der Nähe des GROUP-Schlüsselworts.

Which means something like: Syntax error near the keyword GROUP

How can I fix that problem?

score 4 · Accepted Answer · answered Sep 26 '12 at 10:43

The only time an aliased column can be referenced in the same query is in the ORDER BY clause. Your error with the last attempt was that the subquery had to be given a table-alias, I used "X" below for lack of a better name.

SELECT Error, COUNT(Error) [Count]
FROM (
    SELECT MetaData.value('(/props/prop[@name="error"]/@value)[1]', 'varchar(50)') Error
    FROM source
    WHERE MetaData.exist('/props/prop[@name="error"]') = 1
) X
GROUP BY Error

And Count is a reserved word so you need it in square brackets, if you are going to use it.

Oh no just that `X` was missing on my last try. Thanks! – rekire Sep 26 '12 at 10:45 — rekire, Sep 26 '12 at 10:45

score 2 · Answer 2 · answered Sep 26 '12 at 10:44

In your second query, you need to name your subquery

Select Error, COUNT(Error) FROM
( 
    SELECT MetaData.value('(/props/prop[@name="error"]/@value)[1]', 'varchar(50)') Error 
    FROM videos 
    WHERE MetaData.exist('/props/prop[@name="error"]') = 1 
) subqueryname
GROUP BY Error

You can give it almost any name you like.

score 0 · Answer 3 · edited Sep 27 '12 at 01:46

Correct. You could also that like this:

SELECT error, COUNT(*) errorCount
FROM
    (
    SELECT
        e.c.value ('@value', 'varchar(50)') Error
    FROM source s
        CROSS APPLY s.metadata.nodes('props/prop[@name="error"]') e(c) 
    ) x
GROUP BY error

You can also do this with XQuery, although it's not clear how you want your expected results to look. Try this:

DECLARE @source TABLE ( metadata XML )

INSERT INTO @source
SELECT '<props> 
  <prop name="bytes" value="194" /> 
  <prop name="error" value="File is a text file" /> 
  <prop name="mime-type" value="text/plain " /> 
</props>'

INSERT INTO @source
SELECT '<props> 
  <prop name="bytes" value="0" /> 
  <prop name="error" value="error 1" /> 
  <prop name="error" value="error 2" /> 
  <prop name="mime-type" value="text/plain " /> 
</props>'

SELECT
    MetaData.value('(/props/prop[@name="error"]/@value)[1]', 'varchar(50)') Error, 
    MetaData.value('(/props/prop[@name="mime-type"]/@value)[1]', 'varchar(50)') MimeType, 
    MetaData.value('count(/props/prop[@name="error"])', 'int') errorCount, 
    * 
FROM @source 
WHERE MetaData.exist('/props/prop[@name="error"]') = 1

I want a list of error message and count. E.g. File not found: 3 times. Read Error: 10 times and so on. If I guess right your sql statment would return the count of `prop`s with the name error right? — rekire, Sep 26 '12 at 12:39

Group by a result of a xml query

3 Answers3