Row Count on a table not equal row count in XML file

• mediacommentry

  Old Hand

  Points: 348

  More actions

  March 20, 2019 at 2:27 am

  #377779

  Hi,

  I have this SQL code to load data from a SQL table to XML;
  SELECT (
SELECT BatchRunID, GETDATE() as extractDate,
        (SELECT T1.RealityMemberNo as realityMemberNumber, T1.IDNumber as realityIdNumber,
             (SELECT t2.TierActivityCode AS activityCode,t2.activityDate as activityDate,
                        (SELECT t4.Category AS 'category/@type',
                                t4.Name AS 'category/@name',     
                                t4.Value as 'category'
                            FROM    MyTable AS T4
                            WHERE    T4.IDNumber = T2.IDNumber
                            AND        T4.TierActivityCode = T2.TierActivityCode
                            AND  T4.ActivityDate = T2.ActivityDate
                            AND        T4.BatchControlID = BatchRunID
                            and        T4.BusinessKey    = T2.BusinessKey
                            ORDER BY T4.Category, t4.Name
                                    FOR XML PATH (''),ROOT('categories'), type)
                FROM MyTable AS T2
                WHERE T1.IDNumber = T2.IDNumber
                AND T2.BatchControlID = BatchRunID
                GROUP BY IDNumber,RealityMemberNo, TierActivityCode, ActivityDate, BusinessKey
                FOR XML PATH ('activity'), ROOT ('activities'),type)
        FROM MyTable T1
        WHERE T1.BatchControlID = BatchRunID
        GROUP BY BatchControlID,IDNumber,RealityMemberNo
        FOR XML PATH ('realityMember'), type),
        count(distinct IDNumber+TierActivityCode+convert(varchar(20),ActivityDate)) as ActivityCodeTotal
    FROM MyTable T
    JOIN
    (SELECT    BatchRunID = RealityDataStaging.dbo.udf_getBatchControlID ((SELECT DomainClientID
        FROM    RealityDataStaging.dbo.DomainClients
        WHERE    ClientshortName = 'AAG'),'Tiering')) BR
    ON BatchControlID = BR.BatchRunID
    GROUP BY BatchRunID
    FOR XML PATH ('xml')
) as XMLCol

  I have a total of 663 rows in MyTable, but when I look at my XML file, and Control - Find the activityCode field, and select Count, it only records 221, please see the image below.

  

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  March 20, 2019 at 6:33 am

  #2025002

  Without having your table data to work with, we're entirely guessing.   I saw the word DISTINCT in your query, which could easily cause some rows to not appear in the results, depending on how it's used.  Most folks aren't going to want to help on something like this without sample data to play with, and mainly because it's asking a bit much of them.   Kinda like saying "fix my problem" but you can't see the data or the data types or run test queries to validate assumptions.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)