1

Sorry if the topic isn't very clear, but here is what I need to accomplish. I have a table like so:

 user_id   | friends
==============================
75          | <friend name="bob" /><friend name="joe" />
76          | <friend name="bill" /><friend name="bob" />
77          | <friend name="sam" /><friend name="gary" /><friend name="john" />

I need to get the number of friends (ie, number of XML nodes) for each user. The resulting table should look like this:

 user_id   | number_of_friends
==============================
75         | 2
76         | 2
77         | 3

The following SQL can do it for one column at a time, but I'm not sure how to do it for all columns in one go.

DECLARE @x XML;

SELECT @x = pval 
FROM [mytable]
WHERE uid=75 AND pkey='roster';

SELECT COUNT(t.c.value('@name', 'NVARCHAR(MAX)')) AS number_of_friends
FROM @x.nodes('//friend') t(c);

This results in the table:

number_of_friends
=================
2
sme
  • 4,023
  • 3
  • 28
  • 42

2 Answers2

2

If it is SQL Server you can use XQuery count function:

SELECT  [user_id],
        friends.value('count(/friend)','integer') as number_of_friends
FROM YourTable

In MySQL - ExtractValue():

SELECT  `user_id`,
        ExtractValue(`friends`, 'count(/friend)')
FROM `YourTable`
gofr1
  • 15,741
  • 11
  • 42
  • 52
  • I get this error: `Cannot find either column "friends" or the user-defined function or aggregate "friends.value", or the name is ambiguous. – sme Dec 04 '17 at 10:22
  • `friends` is XML datatype or varchar? If varchar - you need to use `CAST(friends as xml).value('count(/friend)','integer') as number_of_friends` – gofr1 Dec 04 '17 at 10:25
  • Thanks, casting the friends column to XML did the trick. it works now – sme Dec 04 '17 at 10:26
1

You can try this trick: Remove (replace) what you are searching for and compare the difference in string length:

SELECT 
    user_id,    
    ROUND (
        (
            LENGTH(friends)
            - LENGTH( REPLACE ( friends, "<friend", "") ) 
        ) / LENGTH("<friend")        
    ) AS count  
FROM friendtable

Running fiddle

Count the number of occurrences of a string in a VARCHAR field?

Chrille
  • 1,435
  • 12
  • 26
  • The `friends` column is an `ntext`, so the query was giving an error. Even after casting to `nvarchar(max)`, each column returns a value of 0. (Also, its Microsoft SQL Server, so the `length` function should be `len`, and the `round` function needs more than one parameter) – sme Dec 04 '17 at 10:25
  • Ok, you tagged your question with mysql – Chrille Dec 04 '17 at 10:26
  • 1
    Sorry, I removed the tag. I gave you a +1 anyways though, thanks – sme Dec 04 '17 at 10:29
  • 1
    Well, you *can* cut bread with a chain saw - but it's the wrong tool... This ought to be solved with XML's `XQuery`. Your approach would break in any case with another node starting with ` – Shnugo Dec 04 '17 at 19:39