title: Using a Count within an OpenXML query
body: <p>I have a very large XML document which I am loading into SQL and then trying to parse to add to the end of an existing table. </p>
  
  <p>My code so far is: 
  '</p>
  
  <pre><code>INSERT INTO XMLAttempt1(XMLData, LoadedDateTime)
  SELECT CONVERT(XML, Bulkcolumn) AS Bulkcolumn, getdate()
  from openrowset(bulk 'filepath.xml', Single_clob) as x;
  
  select * from XMLAttempt1
  
  DECLARE @XML as XML, @hdoc int, @doc nvarchar(MAX); 
  SELECT @xml = XMLDATA FROM XMLAttempt1;
  EXEC sp_xml_preparedocument @hdoc OUTPUT, @XML;
    Select * 
    FROM  OPENXML (@hdoc, 'CxXMLResults/Query/Result', 1) 
    WITH  (ID     int     '../@id',
      @NodeId     INT     @NodeId',
      ProjectID   int '../../@ProjectId',
      ScanId      int '../../@ScanId');
  </code></pre>
  
  <p>The XML is formatted as: </p>
  
  <pre><code>    &lt;Result NodeId="635920047"  lots of other info&gt;
        &lt;Path ResultId="63592" PathId="47" SimilarityId="-1924977021"&gt;
          &lt;PathNode&gt;
           &lt;FileName&gt;&lt;/FileName&gt;
            &lt;Line&gt;292&lt;/Line&gt;
            &lt;Column&gt;82&lt;/Column&gt;
            &lt;NodeId&gt;1&lt;/NodeId&gt;
            &lt;Name&gt;SelectedValue&lt;/Name&gt;
            &lt;Type&gt;&lt;/Type&gt;
            &lt;Length&gt;13&lt;/Length&gt;
            &lt;Snippet&gt;
              &lt;Line&gt;
                &lt;Number&gt;292&lt;/Number&gt;
                &lt;Code&gt;  stuff;&lt;/Code&gt;
              &lt;/Line&gt;
            &lt;/Snippet&gt;
          &lt;/PathNode&gt;
  [next x numbers of PathNode] 
  &lt;/Path&gt;
  &lt;/Result&gt; 
  &lt;Result NodeId="635920048"
  
  [etc until the next QueryId]
  
  </code></pre>
  
  <p>I've seen this post (<a href="https://stackoverflow.com/questions/30132560/count-the-number-of-sub-tags-from-xml-in-sql/30132881#30132881">Count the number of sub tags from XML in SQL</a>) but I can't get it to work and i'm thinking thats because of where I'm trying to use it - I'm not getting the syntax right. </p>
  
  <pre><code>DECLARE @XML as XML, @hdoc int, @doc nvarchar(MAX); 
  SELECT @xml = XMLDATA FROM XMLAttempt1;
  EXEC sp_xml_preparedocument @hdoc OUTPUT, @XML;
    Select * 
    FROM  OPENXML (@hdoc, 'CxXMLResults/Query/Result', 1) 
    WITH  (ID     int '../@id',
      @XML.query('count(@NodeId') INT '@NodeId',
      ProjectID   int '../../@ProjectId',
      ScanId      int '../../@ScanId');
  </code></pre>
  
  <p>But it says incorrect syntax at @XML</p>
  
  <p>Current results: </p>
  
  <pre><code>ID      NodeID       ProjectID  ScanId
  427 635920047   20336   63592
  427 635920048   20336   63592
  427 635920049   20336   63592
  427 635920050   20336   63592
  427 635920051   20336   63592
  427 635920052   20336   63592
  </code></pre>
  
  <p>What I want to end up with is: </p>
  
  <pre><code>ID      Count ofNodes       ProjectID  ScanId
  427   6                   20336 63592
  
  </code></pre>
  
  <p>any help would be greatly appreciated.</p>