CODEWITHSUNDEEP
sqlsql-serverxmlsql-server-2008
Kapil
Kapil

Reputation: 1931

how to get the count of the xml nodes

I have the following xml nodes 

<PPAProposal xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Answers>
<AnswersList>
<Entry key="ac5_a_InsuredDetailsUpload" type="System.Collections.ArrayList">
        <value>
          <anyType xsi:type="xsd:string">C___documents__PPA_813FN0011684_190219_1003.pdf</anyType>
          <anyType xsi:type="xsd:string">New Doc 2019-02-18 13.37.43 (1).pdf</anyType>
        </value>
</Entry>
</Answers>
</AnswersList>
</PPAProposal>

I need to fetch the count of the "AnyType" nodes. I have written query something like below.

SELECT 
(CASE WHEN  Data.value('(/*/Answers/AnswersList/Entry[@key="ac5_a_InsuredDetailsUpload"]/value)[1]', 'nvarchar(max)')!=''
            THEN 

             (SELECT Count((ISNULL(Col.value('(value)[1]', 'nvarchar(max)'), '0'))) AS upload
          FROM Data.nodes('(/*/Answers/AnswersList/Entry[@key="ac5_a_InsuredDetailsUpload"])') AS Tbl(Col))

      ELSE  0
      END
    )
FROM TEMPTABLE

I'm able to return count 1 instead of count 2 as there is 2 nodes in the "Value" node.

Upvotes: 0

Views: 670

Answers (2)

har07
har07

Reputation: 89285

You can count anyType elements directly in the XPath :

SELECT 
  t.Data.value(
    'count(/*/Answers/AnswersList/Entry[@key="ac5_a_InsuredDetailsUpload"]/value/anyType)', 
    'int'
  ) as anytype_count
FROM TEMPTABLE t;

dbfiddle demo

Upvotes: 1

UsmanMirza
UsmanMirza

Reputation: 276

declare @XMLDATA xml;
set @XMLDATA = '
<PPAProposal xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<AnswersList>
<Answers>
<Entry key="ac5_a_InsuredDetailsUpload" type="System.Collections.ArrayList">
    <value>
      <anyTypexsi:type="xsd:string">C___documents__PPA_813FN0011684_190219_1003.pdf</anyType>
      <anyType xsi:type="xsd:string">New Doc 2019-02-18 13.37.43 (1).pdf</anyType>
    </value></Entry></Answers></AnswersList></PPAProposal>'

DECLARE @XMLDocPointer INT; 
EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT,@XMLDATA; 

    SELECT  value INTO #TEMPDATA

    FROM    OPENXML( @XMLDocPointer, '/PPAProposal/AnswersList/Answers/Entry/value/anyType', 2 )
            WITH ( value NVARCHAR(1000), anyType NVARCHAR(1000) )


EXEC sp_xml_removedocument @XMLDocPointer;

select count(*)total from #TEMPDATA
drop table #TEMPDATA

Upvotes: 0

Related Questions