January 13, 2016 at 5:09 pm
Hello,
I have a query that outputs as XML and am having trouble with it.
I have a table called StudentPrograms:
CREATE TABLE [dbo].[StudentPrograms](
[StudentID] [char](10) NULL,
[Catalog] [varchar](10) NULL,
[Degree] [char](40) NULL,
[Major] [char](40) NULL,
[Minor] [char](40) NULL,
[Concentration] [char](40) NULL
) ON [PRIMARY]
With data such as:
Insert into StudentPrograms
values
('111111111', '2013-14', 'BA', 'AC', 'HN', 'EL'),
('222222222', '2013-14', 'BS', 'AC', 'HN', NULL),
('333333333', '2015-16', 'AS', 'AC', NULL, 'EL'),
('444444444', '2015-16', 'MBA', 'MK', 'MG', NULL),
('555555555', '2013-14', 'NS', 'HC', NULL, NULL)
I need this in XML formatted like this:
<student id="111111111">
<educational_goal type="final">
<student_path>
<path type = "catalog">2013-14</path>
<path type = "degree">BA</path>
<path type = "major">AC</path>
</student_path>
<student_path>
<path type = "catalog">2013-14</path>
<path type = "degree">BA</path>
<path type = "minor">HN</path>
</student_path>
<student_path>
<path type = "catalog">2013-14</path>
<path type = "degree">BA</path>
<path type = "concentration">EL</path>
</student_path>
</educational_goal>
</student>
<student id="222222222">
<educational_goal type="final">
<student_path>
<path type = "catalog">2013-14</path>
<path type = "degree">BS</path>
<path type = "major">AC</path>
</student_path>
<student_path>
<path type = "catalog">2013-14</path>
<path type = "degree">BA</path>
<path type = "minor">HN</path>
</student_path>
</educational_goal>
</student>
<student id="333333333">
<educational_goal type="final">
<student_path>
<path type = "catalog">2015-16</path>
<path type = "degree">AS</path>
<path type = "major">AC</path>
</student_path>
<student_path>
<path type = "catalog">2013-14</path>
<path type = "degree">BA</path>
<path type = "concentration">EL</path>
</student_path>
</educational_goal>
</student>
<student id="444444444">
<educational_goal type="final">
<student_path>
<path type = "catalog">2015-16</path>
<path type = "degree">MBA</path>
<path type = "major">MK</path>
</student_path>
<student_path>
<path type = "catalog">2015-16</path>
<path type = "degree">MBA</path>
<path type = "minor">MG</path>
</student_path>
</educational_goal>
</student>
<student id="555555555">
<educational_goal type="final">
<student_path>
<path type = "catalog">2013-14</path>
<path type = "degree">NS</path>
<path type = "major">HC</path>
</student_path>
</educational_goal>
</student>
My script is:
select StudentID,
(
select
ltrim(rtrim(t2.catalog)) as [program/@catalog],
ltrim(rtrim(t2.degree)) as [program/@degree],
ltrim(rtrim(t2.major)) as [program/@major],
ltrim(rtrim(t2.minor)) as [program/@minor],
ltrim(rtrim(t2.concentration)) as [program/@concentration]
from StudentPrograms as t2
inner join StudentPrograms as t1 on t1.StudentId = t2.StudentId
for xml path('program'), type).query('
<educational_goal type="final">
{
for $program in /program
return
<student_path>
{$program/Name}
<path type="catalog">{data($program/program/@catalog)}</path>
<path type="degree">{data($program/program/@degree)}</path>
<path type="major">{data($program/program/@major)}</path>
<path type="minor">{data($program/program/@minor)}</path>
<path type="concentration">{data($program/program/@concentration)}</path>
</student_path>
}
</educational_goal>')
from StudentPrograms as t1
I need to have a student_path section for every major, minor, and concentration a student has. The student should have a major but not necessarily a minor or concentration. If one of these don't exist, there shouldn't be a student_path section for it. I'm new to this so I'm not sure how to write the script to get it to format like the way I need. Any help that can be provided is greatly appreciated!! Please let me know if any other info is needed.
Thank you in advance!
January 13, 2016 at 7:17 pm
.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 14, 2016 at 10:00 am
I would appreciate any help that can be provided. Thank you so much in advance!
January 14, 2016 at 9:13 pm
See if this gets you close:
SELECT sp.StudentID AS [@id],
(
SELECT 'final' AS type,
(
SELECT (
SELECT 'catalog' AS type,
CAST(CAST(path.Catalog AS NVARCHAR(40)) AS XML)
FROM dbo.StudentPrograms path
WHERE sp.StudentID = path.StudentID
FOR
XML AUTO,
TYPE
),
(
SELECT 'degree' AS type,
CAST(RTRIM(path.Degree) AS XML)
FROM dbo.StudentPrograms path
WHERE sp.StudentID = path.StudentID
AND path.Degree != ''
FOR
XML AUTO,
TYPE
),
(
SELECT 'major' AS type,
CAST(RTRIM(path.Major) AS XML)
FROM dbo.StudentPrograms path
WHERE sp.StudentID = path.StudentID
AND path.Major != ''
FOR
XML AUTO,
TYPE
),
(
SELECT 'minor' AS type,
CAST(RTRIM(path.Minor) AS XML)
FROM dbo.StudentPrograms path
WHERE sp.StudentID = path.StudentID
AND path.Minor != ''
FOR
XML AUTO,
TYPE
),
(
SELECT 'concentration' AS type,
CAST(RTRIM(path.Concentration) AS XML)
FROM dbo.StudentPrograms path
WHERE sp.StudentID = path.StudentID
AND path.Concentration != ''
FOR
XML AUTO,
TYPE
)
FROM dbo.StudentPrograms sp
FOR
XML PATH('student_path'),
TYPE
)
FROM dbo.StudentPrograms educational_goal
WHERE sp.StudentID = educational_goal.StudentID
FOR
XML AUTO,
TYPE
)
FROM dbo.StudentPrograms sp
FOR XML PATH('student');
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply