March 20, 2019 at 2:27 am
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.
March 20, 2019 at 6:33 am
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)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply