Wrong Record count with uniqueidentifier and casting to nvarchar in TSQL for xml path

Question

Here are some sample records

ID(UniqueIdentifier)                 UserId title   description version     parentid    
8362AB94-946F-4092-97C2-ABD957CC2E20    3   title   desc    0   0   
C4E3FCC1-49B5-4310-931F-B8EE48B3F38E    3   title2  desc2   0   1           8362AB94-946F-4092-97C2-ABD957CC2E20
C622113B-C6E6-4ED1-B727-E4F864643CD2    3   title3  desc3   0   2           8362AB94-946F-4092-97C2-ABD957CC2E20

Records represent a hierarchical edits of textual data. Such as , on saving a edit of ParentBoard a new entry is made and with incremental version number i.e 1,2(this all happens on application). The columns of interest here are

ID - Unique Identifier

ParentId - NVARCHAR(50)

I am trying to build a xml as follows

<Edits>
  <Id>314BA459-3749-4BC1-8AA4-E72B882539F5</Id>
  <Title>Parent Title</Title>
  <Description>Parent Description</Description>
  <Version>0</Version>
  <ParentId></ParentId>
  <SubBoardCount>2</SubBoardCount>
</Edits>

Query

SELECT
            Id  'Id',
            Title   'Title',
            [Description]   'Description',
            Version     'Version',
            ParentId    'ParentId',
            (select COUNT(*) WHERE ParentId =  (CAST(Id AS NVARCHAR(50)))) 'SubBoardCount',
    FROM    Edits
    WHERE ParentId IS NULL OR ParentId = ''
    FOR XML PATH('Edits')

Problem

Why do i always end up getting SubBoardCount as 0 rather than 2?

Answer 1

Because your "subquery" isn't searching the table. Perhaps you meant:

SELECT
    Id 'Id',
    Title 'Title',
    [Description] 'Description',
    Version 'Version',
    ParentId 'ParentId',
    (SELECT COUNT(*) 
     FROM Edits e2 
     WHERE e2.ParentId =  (CAST(e1.Id AS NVARCHAR(50)))) 'SubBoardCount',
FROM    
    Edits e1
WHERE 
    ParentId IS NULL OR ParentId = ''
FOR XML PATH('Edits')

Without the extra FROM Edits e2 clause, the references to ParentId and Id within the subquery were referencing those column values from e1 (as I've now aliased it) and these aren't ever equal.