CODEWITHSUNDEEP
sqlxmlt-sqlgroup-bysqlxml
rekire
rekire

Reputation: 47945

Group by a result of a xml query

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?

Upvotes: 1

Views: 6980

Answers (3)

wBob
wBob

Reputation: 14389

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

Upvotes: 0

podiluska
podiluska

Reputation: 51494

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.

Upvotes: 3

RichardTheKiwi
RichardTheKiwi

Reputation: 107736

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.

Upvotes: 5

Related Questions