For XML - SQL Server Forums

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)

For XML

Author

Topic

morphviper
Yak Posting Veteran

60 Posts

Posted - 2009-09-08 : 15:21:09

I have the following data and I need to have an xml return like this:
I realize this might not be the best format, but this is what is being requested.

<Team>
<MemberName>James</MemberName>
<MemberName>Mike</MemberName>
<MemberName>Pete</MemberName>
<Build>Tall</Build>
<MinPushups>100</MinPuships>
<Attitude>Mean</Attitude>
<Color>Red</Color>
</Team>

Code A_03 = MemberName
Code A_01 = Attitude
Code A_02 = MinPushups
Code A_05 = Color
Code A_09 = Build

here is the data :

Create table #MyTestTable (ID int Identity,Team varchar(10),Code Varchar(10),Code_Description Varchar(100))

Insert into #MyTestTable (Team,Code,Code_Description)Values('MM','A_03','James')
Insert into #MyTestTable (Team,Code,Code_Description)Values('MM','A_03','Mike')
Insert into #MyTestTable (Team,Code,Code_Description)Values('MM','A_03','Pete')
Insert into #MyTestTable (Team,Code,Code_Description)Values('MM','A_05','Red')
Insert into #MyTestTable (Team,Code,Code_Description)Values('MM','A_09','Tall')
Insert into #MyTestTable (Team,Code,Code_Description)Values('MM','A_02','100')
Insert into #MyTestTable (Team,Code,Code_Description)Values('MM','A_01','Mean')
Insert into #MyTestTable (Team,Code,Code_Description)Values('MM','A_04','Ohio')
Insert into #MyTestTable (Team,Code,Code_Description)Values('MM','A_06','Florida')
Insert into #MyTestTable (Team,Code,Code_Description)Values('MM','A_07','Paintball')

Select * from #MyTestTable

Drop Table #MyTestTable

Thank you in advance for any help on this.

morphviper
Yak Posting Veteran

60 Posts

Posted - 2009-09-08 : 15:33:36

Sorry forgot the mention I am using SQL 2005.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-08 : 15:39:15

[code]SELECT (
SELECT '' + Code_Description
FROM #MyTestTable
WHERE Code = 'A_03'
FOR XML PATH('MemberName'),
TYPE
),
(
SELECT '' + Code_Description
FROM #MyTestTable
WHERE Code = 'A_09'
FOR XML PATH('Build'),
TYPE
),
(
SELECT '' + Code_Description
FROM #MyTestTable
WHERE Code = 'A_02'
FOR XML PATH('MinPushups'),
TYPE
),
(
SELECT '' + Code_Description
FROM #MyTestTable
WHERE Code = 'A_01'
FOR XML PATH('Attitude'),
TYPE
),
(
SELECT '' + Code_Description
FROM #MyTestTable
WHERE Code = 'A_05'
FOR XML PATH('Color'),
TYPE
)
FOR XML PATH('Team')[/code]

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-08 : 15:44:11

[code]SELECT (SELECT Code_Description AS MemberName FROM #MyTestTable WHERE Code = 'A_03' FOR XML PATH(''), TYPE),
(SELECT Code_Description AS Build FROM #MyTestTable WHERE Code = 'A_09' FOR XML PATH(''), TYPE),
(SELECT Code_Description AS MinPushups FROM #MyTestTable WHERE Code = 'A_02' FOR XML PATH(''), TYPE),
(SELECT Code_Description AS Attitude FROM #MyTestTable WHERE Code = 'A_01'FOR XML PATH(''), TYPE),
(SELECT Code_Description AS Color FROM #MyTestTable WHERE Code = 'A_05' FOR XML PATH(''), TYPE)
FOR XML PATH('Team')[/code]

N 56°04'39.26"
E 12°55'05.63"

morphviper
Yak Posting Veteran

60 Posts

Posted - 2009-09-22 : 19:09:06

what if you added the this data
Insert into #MyTestTable (Team,Code,Code_Description)Values('ZZ','A_03','Chris')
Insert into #MyTestTable (Team,Code,Code_Description)Values('ZZ','A_03','Chad')
Insert into #MyTestTable (Team,Code,Code_Description)Values('ZZ','A_03','Tim')
Insert into #MyTestTable (Team,Code,Code_Description)Values('ZZ','A_05','Red')
Insert into #MyTestTable (Team,Code,Code_Description)Values('ZZ','A_09','Tall')
Insert into #MyTestTable (Team,Code,Code_Description)Values('ZZ','A_02','100')
Insert into #MyTestTable (Team,Code,Code_Description)Values('ZZ','A_01','Mean')
Insert into #MyTestTable (Team,Code,Code_Description)Values('ZZ','A_04','Ohio')
Insert into #MyTestTable (Team,Code,Code_Description)Values('ZZ','A_06','Florida')
Insert into #MyTestTable (Team,Code,Code_Description)Values('ZZ','A_07','Paintball')

and wanted the xml to look something like this?

<AllTeams>
<Team ID="MM">
<MemberName>James</MemberName>
<MemberName>Mike</MemberName>
<MemberName>Pete</MemberName>
<Build>Tall</Build>
<MinPushups>100</MinPuships>
<Attitude>Mean</Attitude>
<Color>Red</Color>
</Team>
<Team ID="ZZ">
<MemberName>Chris</MemberName>
<MemberName>Chad</MemberName>
<MemberName>Tim</MemberName>
<Build>Tall</Build>
<MinPushups>100</MinPuships>
<Attitude>Mean</Attitude>
<Color>Red</Color>
</Team>
</AllTeams>

The request has changed, and I thought I could do it simple enough, but I can't seem to figure it out.

Thank you in advance!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-23 : 03:12:24

[code]DECLARE @Sample TABLE
(
ID INT IDENTITY(1, 1),
Team VARCHAR(10),
Code VARCHAR(10),
Code_Description VARCHAR(100)
)

Insert @Sample
SELECT 'MM', 'A_03', 'James' UNION ALL
SELECT 'MM', 'A_03', 'Mike' UNION ALL
SELECT 'MM', 'A_03', 'Pete' UNION ALL
SELECT 'MM', 'A_05', 'Red' UNION ALL
SELECT 'MM', 'A_09', 'Tall' UNION ALL
SELECT 'MM', 'A_02', '100' UNION ALL
SELECT 'MM', 'A_01', 'Mean' UNION ALL
SELECT 'MM', 'A_04', 'Ohio' UNION ALL
SELECT 'MM', 'A_06', 'Florida' UNION ALL
SELECT 'MM', 'A_07', 'Paintball' UNION ALL
SELECT 'ZZ','A_03','Chris' UNION ALL
SELECT 'ZZ','A_03','Chad' UNION ALL
SELECT 'ZZ','A_03','Tim' UNION ALL
SELECT 'ZZ','A_05','Green' UNION ALL
SELECT 'ZZ','A_09','Tall' UNION ALL
SELECT 'ZZ','A_02','110' UNION ALL
SELECT 'ZZ','A_01','Laidback' UNION ALL
SELECT 'ZZ','A_04','Ohio' UNION ALL
SELECT 'ZZ','A_06','Florida' UNION ALL
SELECT 'ZZ','A_07','Paintball'

-- According to Peso
SELECT s.Team AS [@ID],
(SELECT Code_Description AS MemberName FROM @Sample AS t WHERE t.Code = 'A_03' AND t.Team = s.Team FOR XML PATH(''), TYPE),
(SELECT Code_Description AS Build FROM @Sample AS t WHERE Code = 'A_09 AND t.Team = s.Team' FOR XML PATH(''), TYPE),
(SELECT Code_Description AS MinPushups FROM @Sample AS t WHERE Code = 'A_02' AND t.Team = s.Team FOR XML PATH(''), TYPE),
(SELECT Code_Description AS Attitude FROM @Sample AS t WHERE Code = 'A_01' AND t.Team = s.Team FOR XML PATH(''), TYPE),
(SELECT Code_Description AS Color FROM @Sample AS t WHERE Code = 'A_05' AND t.Team = s.Team FOR XML PATH(''), TYPE)
FROM @Sample AS s
GROUP BY s.Team
FOR XML PATH('Team'),
ROOT('AllTeams')[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources