给定表T列:
@H_502_2@ID UNIQUEIDENTIFIER
CreatedDate DATETIME
XmlData XML
XmlData的结构如下:
@H_502_2@<application> <details firstname="first" lastname="last"> <statement>statement</statement> </details> <educationHistory> <education subject="subject1" /> <education subject="subject2" /> </educationHistory> <experienceHistory> <examiningExperienceHistory> <examiningExperience module="module1" /> <examiningExperience module="module2" /> </examiningExperienceHistory> <teachingExperienceHistory> <teachingExperience module="module1" /> <teachingExperience module="module2" /> </teachingExperienceHistory> </experienceHistory> </application>我需要像这样返回一个提取物:
@H_502_2@ID Date FirstName LastName Education ExaminingExp TeachingExp ----------------------------------------------------------------------- 1 02-10-2012 First Last <xmlextract> <xmlextract> <xmlextract>到目前为止我有:
@H_502_2@SELECT ID,CreatedDate [Date],XmlData.value('(application/details/@firstname)[1]','varchar(max)') [FirstName],XmlData.value('(application/details/@lastname)[1]','varchar(max)') [LastName] FROM T我在最后三列中苦苦挣扎.对于每个记录,我需要列出教学/考试经验和教育.有人可以帮忙吗?