November 4, 2011 at 1:29 pm
Hi Everyone
I'm trying to convert:
TABLE: ArrayOfStudentMoveSummary
rowidiStudentidOldClassIDNewClassID
1 6280147 50106 48036
2 6515202 50106 48036
3 6521630 50106 48036
4 6527724 50106 48036
5 6528117 50106 48036
into this XML:
<ArrayOfStudentMoveSummary>
<Format>String content</Format>
<Students>
<StudentMoveSummary>
<ProcessId>186</ProcessId>
<Pending>true</Pending>
<Active>true</Active>
<StudentId>6280147</StudentId>
<Name>String content</Name>
<OldClassId>50106</OldClassId>
<NewClassId>48036</NewClassId>
</StudentMoveSummary>
<StudentMoveSummary>
<ProcessId>186</ProcessId>
<Pending>true</Pending>
<Active>true</Active>
<StudentId>6515202</StudentId>
<Name>String content</Name>
<OldClassId>50106</OldClassId>
<NewClassId>48036</NewClassId>
</StudentMoveSummary>
<StudentMoveSummary>
<ProcessId>186</ProcessId>
<Pending>true</Pending>
<Active>true</Active>
<StudentId>6521630</StudentId>
<Name>String content</Name>
<OldClassId>50106</OldClassId>
<NewClassId>48036</NewClassId>
</StudentMoveSummary>
<StudentMoveSummary>
<ProcessId>186</ProcessId>
<Pending>true</Pending>
<Active>true</Active>
<StudentId>6527724</StudentId>
<Name>String content</Name>
<OldClassId>50106</OldClassId>
<NewClassId>48036</NewClassId>
</StudentMoveSummary>
<StudentMoveSummary>
<ProcessId>186</ProcessId>
<Pending>true</Pending>
<Active>true</Active>
<StudentId>6528117</StudentId>
<Name>String content</Name>
<OldClassId>50106</OldClassId>
<NewClassId>48036</NewClassId>
</StudentMoveSummary>
</Students>
</ArrayOfStudentMoveSummary>
So far i've got this query
SELECT iStudentId, OldClassID, NewClassID FROM @ArrayOfStudentMoveSummary FOR XML AUTO,ELEMENTS
which generates:
<_x0040_ArrayOfStudentMoveSummary>
<iStudentId>6280147</iStudentId>
<OldClassID>50106</OldClassID>
<NewClassID>48036</NewClassID>
</_x0040_ArrayOfStudentMoveSummary>
<_x0040_ArrayOfStudentMoveSummary>
<iStudentId>6515202</iStudentId>
<OldClassID>50106</OldClassID>
<NewClassID>48036</NewClassID>
</_x0040_ArrayOfStudentMoveSummary>
<_x0040_ArrayOfStudentMoveSummary>
<iStudentId>6521630</iStudentId>
<OldClassID>50106</OldClassID>
<NewClassID>48036</NewClassID>
</_x0040_ArrayOfStudentMoveSummary>
<_x0040_ArrayOfStudentMoveSummary>
<iStudentId>6527724</iStudentId>
<OldClassID>50106</OldClassID>
<NewClassID>48036</NewClassID>
</_x0040_ArrayOfStudentMoveSummary>
<_x0040_ArrayOfStudentMoveSummary>
<iStudentId>6528117</iStudentId>
<OldClassID>50106</OldClassID>
<NewClassID>48036</NewClassID>
</_x0040_ArrayOfStudentMoveSummary>
Can anyone suggest a better way?
Thank You
November 4, 2011 at 1:36 pm
The values that aren't in the table, like ProcessID, where do they come from? Or are those static?
Other than that, it looks like:
select *
from dbo.ArrayOfStudentMoveSummary
for XML path('StudentMoveSummary'), type
That would need to be nested in a query that gets the outer structure of all of this.
What have you got so far as the query? Where are you getting stuck?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 4, 2011 at 2:42 pm
Thanks GSquared for the SQL
Those static values not needed.
This is what I added
SELECT StudentMoveSummary.* FROM
(SELECT iStudentid as StudentId, OldClassID, NewClassID FROM @tblMoveStudentList) as StudentMoveSummary
FOR XML auto,ROOT('ArrayOfStudentMoveSummary'),ELEMENTS
OUTPUT:
<ArrayOfStudentMoveSummary>
<StudentMoveSummary>
<StudentId>6280147</StudentId>
<OldClassID>50106</OldClassID>
<NewClassID>48036</NewClassID>
</StudentMoveSummary>
<StudentMoveSummary>
<StudentId>6515202</StudentId>
<OldClassID>50106</OldClassID>
<NewClassID>48036</NewClassID>
</StudentMoveSummary>
<StudentMoveSummary>
<StudentId>6521630</StudentId>
<OldClassID>50106</OldClassID>
<NewClassID>48036</NewClassID>
</StudentMoveSummary>
<StudentMoveSummary>
<StudentId>6527724</StudentId>
<OldClassID>50106</OldClassID>
<NewClassID>48036</NewClassID>
</StudentMoveSummary>
<StudentMoveSummary>
<StudentId>6528117</StudentId>
<OldClassID>50106</OldClassID>
<NewClassID>48036</NewClassID>
</StudentMoveSummary>
</ArrayOfStudentMoveSummary>
November 4, 2011 at 3:05 pm
The subquery is unnecessary. You can write this as follows:
SELECT iStudentid as StudentId, OldClassID, NewClassID
FROM @tblMoveStudentList as StudentMoveSummary
FOR XML auto,ROOT('ArrayOfStudentMoveSummary'),ELEMENTS
If you alias the table, it will use that alias in the XML. If you don't it will use the actual table name, but since your table is a variable and "@" is not valid for XML element names, it needs to translate that into something that is valid. That's why your original query produced elements that started with "_x0040_"
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 7, 2011 at 7:18 am
One more time, with PATH syntax:
SELECT
aosms.iStudentid AS [StudentId],
aosms.oldClassID AS [OldClassID],
aosms.newClassID AS [NewClassID]
FROM dbo.ArrayOfStudentMoveSummary AS aosms
FOR XML
PATH('StudentMoveSummary'),
ROOT('ArrayOfStudentMoveSummary'),
TYPE
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 11, 2011 at 9:12 am
Thank you Drew.
The subquery is unnecessary. You can write this as follows:
SELECT iStudentid as StudentId, OldClassID, NewClassID
FROM @tblMoveStudentList as StudentMoveSummary
FOR XML auto,ROOT('ArrayOfStudentMoveSummary'),ELEMENTS
If you alias the table, it will use that alias in the XML. If you don't it will use the actual table name, but since your table is a variable and "@" is not valid for XML element names, it needs to translate that into something that is valid. That's why your original query produced elements that started with "_x0040_"
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 11, 2011 at 9:13 am
Thank you SQLKiwi
SQL Kiwi (11/7/2011)
One more time, with PATH syntax:
SELECT
aosms.iStudentid AS [StudentId],
aosms.oldClassID AS [OldClassID],
aosms.newClassID AS [NewClassID]
FROM dbo.ArrayOfStudentMoveSummary AS aosms
FOR XML
PATH('StudentMoveSummary'),
ROOT('ArrayOfStudentMoveSummary'),
TYPE
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply