? on Count specific value on XML data

Question

? on Count specific value on XML data

jbalbo

SSCertifiable

Points: 7583
More actions
March 2, 2021 at 5:37 pm

#3852379

Hi
How do I find a count of a specific value in XML?
data.value('count(/SampleXML/Colors/*)', 'int') finds all
so say Black in [1] and Red in [2] and Black in [3]
How do I get Black 2. Red 1

Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246974 More actions · Answer 1

jbalbo wrote:

Hi
How do I find a count of a specific value in XML?
data.value('count(/SampleXML/Colors/*)', 'int') finds all
so say Black in [1] and Red in [2] and Black in [3]
How do I get Black 2. Red 1
Thanks

If you want some code, please provide some sample XML to code against.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

jbalbo SSCertifiable Points: 7583 More actions · Answer 2

Thnaks and sorry for not adding this

So in this example I would want Black 2 and red 1

<Data>

</members>

<Color>black</Minor>

</members>

<Color>black</Minor>

</members>

</Others>

</Data>

Phil Parkin SSC Guru Points: 246974 More actions · Answer 3

jbalbo wrote:

Thnaks and sorry for not adding this
So in this example I would want Black 2 and red 1
<Data> <Others> <members> <Name>adf</Name> <Color>red</Minor> </members> <members> <Name>adf</Name> <Color>black</Minor> </members> <members> <Name>adf</Name> <Color>black</Minor> </members> </Others> </Data>

This is not valid XML. There is no opening Minor tag.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

jbalbo SSCertifiable Points: 7583 More actions · Answer 4

Sorry, I was trying not to show some data

here is a samle I found and changed, so lets say in this example I want counts of name

</Person>

</Person>

</Person>

</Person>

</Employees>

So I would like the output to counts of minor = 'yes', disregarding ID

my output would be Minor = 3

Phil Parkin SSC Guru Points: 246974 More actions · Answer 5

In case it helps others, here is a formatted version.

<Employees>
<Person>
<ID>1000</ID>
<minor>yes</minor>
</Person>
<Person>
<ID>1001</ID>
<minor>yes</minor>
</Person>
<Person>
<ID>1002</ID>
<minor>yes</minor>
</Person>
<Person>
<ID>1003</ID>
<minor>no</minor>
</Person>
</Employees>

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

jbalbo SSCertifiable Points: 7583 More actions · Answer 6

jbalbo

SSCertifiable

Points: 7583

March 2, 2021 at 6:41 pm

#3852411

Thanks.... 🙂

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 7

Not totally clear what you want, but this gives you counts from your XML

declare @x xml = '
<Employees>
<Person>
<ID>1000</ID>
<minor>yes</minor>
</Person>
<Person>
<ID>1001</ID>
<minor>yes</minor>
</Person>
<Person>
<ID>1002</ID>
<minor>yes</minor>
</Person>
<Person>
<ID>1003</ID>
<minor>no</minor>
</Person>
</Employees>
';

select @x.query('
 for $value in distinct-values(/Employees/Person/minor)
  let $count := count(/Employees/Person/minor[. eq $value])
  return concat($value," ",xs:string($count))
');


select @x.query('count(/Employees/Person/minor[. eq "yes"])');

This reply was modified 3 years, 9 months ago by Mark Cowne. Reason: Typo

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537