数据的SQL到XML传输
数据的SQL到XML传输
提问于 2015-11-25 16:49:30
过去几天，Ok一直在尝试学习SQL到XML，到目前为止，这就是我能够教自己的。
`SELECT distinct StudentItem.foldername AS "foldername", StudentItem.status, StudentItem.vhrid, StudentItem.firstname, StudentItem.middleinitial, StudentItem.lastname,
dbo.getEnumDescript(StudentType, 'StudentType') AS title,
StudentItem.email,
dbo.getEnumDescript(OfficeLocation, 'OfficeLocation') AS Office,
practices.id as 'StudentItem/practices/practice/id',
practices.name as 'StudentItem/practices/practice/name',
schoolItem.Name as 'StudentItem/bio/schools/schoolItem/schoolname',
schoolItem.schoolYear as 'lawyerItem/bio/schools/schoolItem/schoolyear'
FROM [dbo].[Student] as lawyerItem
LEFT JOIN [dbo].[StudentGroups] as aprac on StudentItem.vhrid = aprac.vhrid
INNER JOIN [dbo].[PracticeGroups] as practices on aprac.PracticeGroupID = practices.ID
LEFT JOIN [dbo].[StudentEducation] as schoolItem on StudentItem.vhrid = schoolItem.vhrid
where StudentItem.vhrid='50330'
FOR XML path, ROOT ('StudentItem'), ELEMENTS;` 我得到的是这个
`<StudentItems>
<row>
<foldername>susan.wissink</foldername>
<status>1</status>
<vhrid>50330</vhrid>
<firstname>Susan</firstname>
<middleinitial>M.</middleinitial>
<lastname>Wissink</lastname>
<title>Student leader</title>
<email>swissink@blank.com</email>
<Office>Phoenix</Office>
<StudentItem>
<practices>
<practice>
<id>681</id>
<name>Real Estate Finance and Lending</name>
</practice>
</practices>
<bio>
<schools>
<schoolItem>
<schoolname><i>Best in America®</i>, ASU</schoolname>
<schoolyear>2016</schoolyear>
</schoolItem>
</schools>
</bio>
</StudentItem>
</row>
<row>
<foldername>susan.wissink</foldername>
<status>1</status>
<vhrid>50330</vhrid>
<firstname>Susan</firstname>
<middleinitial>M.</middleinitial>
<lastname>Wissink</lastname>
<title>Student leader</title>
<email>swissink@blank.com</email>
<Office>Phoenix</Office>
<StudentItem>
<practices>
<practice>
<id>681</id>
<name>Real Estate Finance and Lending</name>
</practice>
</practices>
<bio>
<schools>
<schoolItem>
<schoolname><i>Best in America®</i>, UOP</schoolname>
<schoolyear>2011-2015</schoolyear>
</schoolItem>
</schools>
</bio>
</StudentItem>
</row>`但我正在努力让所有的训练和学校都能成为那个家伙的参赛作品。或多或少我想让它看起来像下面的样子。
`<StudentItems>
<row>
<foldername>susan.wissink</foldername>
<status>1</status>
<vhrid>50330</vhrid>
<firstname>Susan</firstname>
<middleinitial>M.</middleinitial>
<lastname>Wissink</lastname>
<title>Student leader</title>
<email>swissink@blank.com</email>
<Office>Phoenix</Office>
<StudentItem>
<practices>
<practice>
<id>681</id>
<name>Real Estate Finance and Lending</name>
<id>683</id>
<name>Business and Finance</name>
</practice>
</practices>
<bio>
<schools>
<schoolItem>
<schoolname><i>Best in America®</i>, UOP</schoolname>
<schoolyear>2011-2015</schoolyear>
<schoolname><i>Best in America®</i>, ASU</schoolname>
<schoolyear>2016</schoolyear>
</schoolItem>
</schools>
</bio>
</StudentItem>
</row>`任何帮助都是欢迎的。谢谢。
回答 1
Stack Overflow用户
回答已采纳
发布于 2015-11-25 22:20:51
没有样本数据，就很难编写代码并对其进行测试。但是通常您需要做的是创建子查询来创建practice和schoolItem节点。就像这样：
SELECT distinct StudentItem.foldername AS "foldername",
StudentItem.status,
StudentItem.vhrid,
StudentItem.firstname,
StudentItem.middleinitial,
StudentItem.lastname,
dbo.getEnumDescript(StudentType, 'StudentType') AS title,
StudentItem.email,
dbo.getEnumDescript(OfficeLocation, 'OfficeLocation') AS Office,
(
select practices.id, practices.name
from [dbo].[StudentGroups] as aprac
INNER JOIN [dbo].[PracticeGroups] as practices
on aprac.PracticeGroupID = practices.ID
where StudentItem.vhrid = aprac.vhrid
FOR XML path(''), type
) 'StudentItem/practices/practice',
(
select Name schoolname, schoolYear
from [dbo].[StudentEducation] schoolItem
where StudentItem.vhrid = schoolItem.vhrid
FOR XML path(''), type
) 'StudentItem/bio/schools/schoolItem'
FROM [dbo].[Student] as StudentItem
where StudentItem.vhrid='50330'
FOR XML path, ROOT ('StudentItem');页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/33922084
复制相关文章
相似问题
腾讯云开发者
