Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to get specific element Count in XML variable

Consider this XML:

<Employees>
    <Person>
        <ID>1000</ID>
        <Name>Nima</Name>
        <LName>Agha</LName>
    </Person>
    <Person>
        <ID>1001</ID>
        <Name>Ligha</Name>
        <LName>Ligha</LName>
    </Person>
    <Person>
        <ID>1002</ID>
        <Name>Jigha</Name>
        <LName>Jigha</LName>
    </Person>
    <Person>
        <ID>1003</ID>
        <Name>Aba</Name>
        <LName>Aba</LName>
    </Person>
</Employees>

I declare a XML variable and assign this XML to that. How I can get count of ID elements in this XML variable using Sql Server 2008 (TSQL)?

like image 317
Arian Avatar asked Jan 06 '12 19:01

Arian


People also ask

How do I count elements in XML?

Count the XML elements (XPath)newXPath(); NodeList nodes = (NodeList) xpath. evaluate("//staff", doc, XPathConstants. NODESET); int count = nodes. getLength();

How do I find specific nodes in XML?

To find nodes in an XML file you can use XPath expressions. Method XmlNode. SelectNodes returns a list of nodes selected by the XPath string.


2 Answers

SELECT @XMLVariable.value('count(/Employees/Person/ID)', 'int') AS IDCount
like image 94
Joe Stefanelli Avatar answered Nov 15 '22 08:11

Joe Stefanelli


try this:

declare @xmlvar XML;
set @xmlvar ='<YOUR XML>';
select @xmlvar.value('count(/Employees/Person/ID)', 'INT') AS 'Count'
like image 45
Arian Avatar answered Nov 15 '22 08:11

Arian