March 3, 2008 at 10:32 am
I'm trying to extract data in a custom XML format using the FOR XML EXPLICIT option in SQL Server 2000. When I run the query (attached) . Without using this option, bringing back just a regular result set, the data looks to be just fine (records that have parents have the right parents, and parents have null parents etc.). I have been stumped on this for a couple of days and it is driving me absolutely nuts. Also, when I use auto or raw it also brings back invalid XML. I have noticed, however, that when I try to bring back the XML, for every individual that has children, SQL Server brings back that many rows when I try to use FOR XML EXPLICIT.
SELECT
1 AS Tag,
NULL AS Parent,
Members.TeamID AS [Member!1! TeamID],
Members.DirectoryID AS [Member!1!DirectoryID],
Members.LName + ', '+ Members.FName AS [Member!1!Name],
Members.AlternateInfo AS [Member!1!AlternateInfo],
NULL AS [UnavailableDates!2!Date],
(
SELECT COUNT (DirectoryID)+1 FROM [IMTMemberUnavailable]WHERE [DirectoryID]= Members.DirectoryID
) AS [Member!1!DateCount]
FROM(
SELECT dbo.Directory.DirectoryID, dbo.Directory.FName, dbo.Directory.LName, dbo.IMTTeamMember.AlternateInfo, dbo.[IMTTeamMember].TeamID
FROM dbo.Directory
INNER JOIN
dbo.IMTTeamMember ON dbo.Directory.DirectoryID = dbo.IMTTeamMember.DirectoryID
WHERE [IMTTeamMember].[TeamID]=@TeamID
) Members
UNION ALL
SELECT
2,
1,
NULL,
[Unavailability].DirectoryID,
Unavailability.[Name],
Unavailability.AlternateInfo,
Unavailability.[Date],
NULL
FROM (
SELECT DISTINCT TOP 100 PERCENT
Directory.LName + ', ' + Directory.FName AS [Name], dbo.IMTTeamMember.AlternateInfo, dbo.IMTTeamMember.TeamID,
dbo.IMTMemberUnavailable.[Date],
dbo.Directory.DirectoryID
FROM dbo.IMTTeamMember
INNER JOIN
dbo.Directory ON dbo.IMTTeamMember.DirectoryID = dbo.Directory.DirectoryID INNER JOIN
dbo.IMTMemberUnavailable ON dbo.Directory.DirectoryID = dbo.IMTMemberUnavailable.DirectoryID
WHERE [IMTTeamMember].[TeamID]=@TeamID
ORDER BY [Date] ASC
) Unavailability
ORDER BY [Member!1!Name], [UnavailableDates!2!Date]
FOR XML EXPLICIT
March 3, 2008 at 11:12 am
What do you mean by "invalid"?
What specifically clues you in to it being "invalid"?
Are you getting an error? If so - where?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply