Examing Values from xml-field in SQL Server 2008

Question

I need to get some data out of an xml-field. The special problem is the xml-field itself.

The xml-field is build that way:

<UseAnsw>
  <User User="3" Answ="N" />
  <User User="2" Answ="N" />
  <User User="7" Answ="N" />
  <User User="5" Answ="N" />
  <User User="1" Answ="N" />
</UseAnsw>

The Sql table has also a column with the User who posted a question. What I need to do is, count how often a User answered questions.

As an Example:

use_ID    use_PostingUser     use_UseAnsw
00001     2                   <UseAnsw>
                                  <User User="3" Answ="N" />
                                  <User User="1" Answ="N" />
                                  <User User="7" Answ="N" />
                              </UseAnsw>
00002     4                   <UseAnsw>
                                  <User User="2" Answ="N" />
                                  <User User="1" Answ="N" />
                                  <User User="3" Answ="N" />
                              </UseAnsw>
00003     1                   <UseAnsw>
                                  <User User="3" Answ="N" />
                                  <User User="5" Answ="N" />
                                  <User User="8" Answ="N" />
                              </UseAnsw>

The Result out of that example that i look for would be:

User   AnswersGiven
1      2
2      1
4      0

I hope someone understands what I am trying and is able to help me (an sql-beginner, especially when it comes to xml)

Answer 1

I do not understand, how your count works, but this might push you to the right path:

DECLARE @tbl TABLE(use_ID VARCHAR(100),use_PostingUser INT,use_UseAnsw XML);
INSERT INTO @tbl VALUES
 ('00001',2,'<UseAnsw>
                                  <User User="3" Answ="N" />
                                  <User User="1" Answ="N" />
                                  <User User="7" Answ="N" />
                              </UseAnsw>')
,('00002',4,'<UseAnsw>
                                  <User User="2" Answ="N" />
                                  <User User="1" Answ="N" />
                                  <User User="3" Answ="N" />
                              </UseAnsw>')
,('00003',1,'<UseAnsw>
                                  <User User="3" Answ="N" />
                                  <User User="5" Answ="N" />
                                  <User User="8" Answ="N" />
                              </UseAnsw>');

This query will return all data

SELECT use_ID,use_postingUser
      ,Usr.value('@User','int') AS AttribUser
      ,Usr.value('@Answ','varchar(max)') AS AttribAnsw
FROM @tbl AS tbl
CROSS APPLY use_UseAnsw.nodes('/UseAnsw/User') AS One(Usr)

Here I use the same approach to return the column use_postingUser and its count of User elements (actually of the equally named attribute)

SELECT use_postingUser
      ,COUNT(Usr.value('@User','int')) AS CountAttribUser
FROM @tbl AS tbl
CROSS APPLY use_UseAnsw.nodes('/UseAnsw/User') AS One(Usr)
GROUP BY use_PostingUser

Hope this helps...

UPDATE

Still I don't understand your logic, but this might come closer:

The CTE "AllData" provides the list of all data (as before)

In the final SELECT the count of answers for each user according to the User's number within the XML is counted. This is OK for 1,2 and 4 (zero==non existent), but there are more IDs you did not mention in your expected output...

If you need all IDs dsiplayed (the 4 with a zero) you'll need a list of all users and a LEFT JOIN to the XML's data...

WITH AllData AS
(
    SELECT use_ID,use_postingUser
          ,Usr.value('@User','int') AS AttribUser
          ,Usr.value('@Answ','varchar(max)') AS AttribAnsw
    FROM @tbl AS tbl
    CROSS APPLY use_UseAnsw.nodes('/UseAnsw/User') AS One(Usr)
)
SELECT AttribUser
      ,COUNT(*) AS CountAttribUser
FROM AllData
GROUP BY AttribUser

UPDATE 2

According to Y.B's comment it might be this: (use the declared table variable from above)

WITH AllData AS
(
    SELECT use_ID,use_postingUser
          ,Usr.value('@User','int') AS AttribUser
          ,Usr.value('@Answ','varchar(max)') AS AttribAnsw
    FROM @tbl AS tbl
    CROSS APPLY use_UseAnsw.nodes('/UseAnsw/User') AS One(Usr)
)
SELECT DistinctUsers.use_PostingUser
      ,COUNT(AllData.AttribUser) AS CountAttribUser
FROM @tbl AS DistinctUsers
LEFT JOIN AllData ON DistinctUsers.use_PostingUser=AllData.AttribUser
GROUP BY DistinctUsers.use_PostingUser