XML PATH query...is this possible? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

XML PATH query...is this possible?

Author

Topic

hshot_rooke
Starting Member

8 Posts

Posted - 2007-12-05 : 15:37:15

Hello all

I'm having trouble creating a FOR XML PATH query. I'm wondering if it is even possible to get the resulting XML, or if I should just use FOR XML EXPLICIT.

Here's some sample data from my table:


ClsID	CliID	Attended_On	        Attended	Reason
9	2	2007-11-11 00:00:00	0	
9	2	2007-11-17 00:00:00	0	
9	2	2007-11-18 00:00:00	0	
9	2	2007-11-24 00:00:00	0	
9	2	2007-11-25 00:00:00	0	
9	2	2007-12-01 00:00:00	0	
9	2	2007-12-02 00:00:00	0	
9	2	2007-12-08 00:00:00	0	
9	361	2007-11-11 00:00:00	0	
9	361	2007-11-17 00:00:00	0	
9	361	2007-11-18 00:00:00	0	
9	361	2007-11-24 00:00:00	0	
9	361	2007-11-25 00:00:00	0	
9	361	2007-12-01 00:00:00	0	
9	361	2007-12-02 00:00:00	0	
9	361	2007-12-08 00:00:00	0

I would like the XML to look as such:


<ROOT>
	<part cliid="2" >
		<att attendedon="2007-11-11" attended="0" reason="">
		<att attendedon="2007-11-17" attended="0" reason="">
                ...
	</part>
	<part cliid="361">
		<att attendedon="2007-11-11" attended="0" reason="">
		<att attendedon="2007-11-17" attended="0" reason="">
                ...
	</part>
</ROOT>

And what I've begun with doesn't really get me the correct XML:


	SELECT
		C.CliID				AS "@cliid",
		(
			SELECT
				A.Attended_On	AS "@attendedon",
				A.Attended	AS "@attended",
				A.Reason	AS "@reason"

			FROM Class_Attendance A
			WHERE	(A.ClsID = C.ClsID)
			FOR XML PATH('att'),TYPE
		)
	FROM Class_Attendance C
	WHERE (C.ClsID = 9)
	FOR XML PATH('part'),ROOT('ROOT')

The resulting XML gives me multiple part nodes with the same "CliID".
Can't use a DISTINCT on the outer query and even if I attach a DISTINCT to the inner query, I'm still getting duplicate nodes of the same CliID.

Can anyone tell me if this is possible or if I should just use EXPLICIT? Trying to keep all my Stored procs consistent in the way I query the XML...any help appreciated.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 15:58:34

[code]DECLARE @Sample TABLE (ClsID INT, CliID INT, Attended_On DATETIME, Attended TINYINT, Reason VARCHAR(20))

INSERT @Sample
SELECT 9, 2, '2007-11-11 00:00:00', 0, NULL UNION ALL
SELECT 9, 2, '2007-11-17 00:00:00', 0, NULL UNION ALL
SELECT 9, 2, '2007-11-18 00:00:00', 0, NULL UNION ALL
SELECT 9, 2, '2007-11-24 00:00:00', 0, NULL UNION ALL
SELECT 9, 2, '2007-11-25 00:00:00', 0, NULL UNION ALL
SELECT 9, 2, '2007-12-01 00:00:00', 0, NULL UNION ALL
SELECT 9, 2, '2007-12-02 00:00:00', 0, NULL UNION ALL
SELECT 9, 2, '2007-12-08 00:00:00', 0, NULL UNION ALL
SELECT 9, 361, '2007-11-11 00:00:00', 0, NULL UNION ALL
SELECT 9, 361, '2007-11-17 00:00:00', 0, NULL UNION ALL
SELECT 9, 361, '2007-11-18 00:00:00', 0, NULL UNION ALL
SELECT 9, 361, '2007-11-24 00:00:00', 0, NULL UNION ALL
SELECT 9, 361, '2007-11-25 00:00:00', 0, NULL UNION ALL
SELECT 9, 361, '2007-12-01 00:00:00', 0, NULL UNION ALL
SELECT 9, 361, '2007-12-02 00:00:00', 0, NULL UNION ALL
SELECT 9, 361, '2007-12-08 00:00:00', 0, NULL

SELECT C.CliID AS [@cliid],
(
SELECT a.Attended_On AS [@attendedon],
a.Attended AS [@attended],
COALESCE(a.Reason, '') AS [@reason]
FROM @Sample AS a
WHERE a.ClsID = c.ClsID
AND a.CliID = c.CliID
FOR XML PATH('att'),
TYPE
)
FROM (
SELECT DISTINCT CliID,
ClsID
FROM @Sample
) AS c
WHERE c.ClsID = 9
FOR XML PATH('part'),
ROOT('ROOT')[/code]

E 12°55'05.25"
N 56°04'39.16"

hshot_rooke
Starting Member

8 Posts

Posted - 2007-12-05 : 16:11:59

Thanks Peso...overlooked the (A.CliID = C.CliID) in the inner query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources