sql-server - 只能从 xml 变量中获取标签下的第一条记录

Question

如果有人能帮我解决这个问题，我将不胜感激。有时我在所有结果中得到相同的 id，我永远无法获得第二条记录(毕业)，只有第一条。

xml格式如下:record标签可以包含一个或多个DOCTORAL标签:

    <Record username=<"erttt">
      <DOCTORAL>
        <PROGRAM>Program Name 1</PROGRAM>
        <MILESTONE>Entered Program</MILESTONE>
        <DTM_DATE>August</DTM_DATE>
        <DTD_DATE>24</DTD_DATE>
        <DTY_DATE>2015</DTY_DATE>
      </DOCTORAL>
   </Record>
   <Record> username=<"xxxgh">
      <DOCTORAL>
         <PROGRAM>Program Name 2</PROGRAM>
         <MILESTONE>Entered Program</MILESTONE>
         <DTM_DATE>Jan</DTM_DATE>
         <DTD_DATE>2</DTD_DATE>
         <DTY_DATE>2014</DTY_DATE>
     </DOCTORAL>
     <DOCTORAL>
         <PROGRAM>Program Name 2</PROGRAM>
         <MILESTONE>Graduated</MILESTONE>
         <DTM_DATE>August</DTM_DATE>
         <DTD_DATE>26</DTD_DATE>
         <DTY_DATE>2016</DTY_DATE>
     </DOCTORAL>
  </Record>

我想得到这样的结果:

    username  Program           Milestone         
    erttt     Program Name 1    Entered Program    
    xxxgh     Program Name 2    Entered Program     
    xxxgh     Program Name 2    Graduated           

所以，上面会有 3 条记录。

以下是行不通的，我尝试了许多不同的组合并查看了本网站上的示例。就是想不通...

SELECT
     x.value('(//Record/@username)[1]','varchar(50)') AS username, --'@username','varchar(50)' DOESN'T WORK HERE
     c.value('(//Record/DOCTORAL/PROGRAM)[1]','varchar(200)') as PROGRAM, 
    c.value('(//Record/DOCTORAL/MILESTONE)[1]','varchar(50)') as MILESTONE,
    c.value('(//Record/DOCTORAL/DTM_DATE)[1]','varchar(8)') as DTM_DATE, --DTM_DATE
    c.value('(//Record/DOCTORAL/DTD_DATE)[1]','varchar(2)') as DTD_DATE,  --DTD_DATE
    c.value('(//Record/DOCTORAL/DTY_DATE)[1]','varchar(4)') as DTY_DATE  --DTY_DATE 
from @xmlDocPrelim.nodes('//Record') t(x)  -- if ends with 'Record', then same id in all recs; if ends in DOCTORAL, then not.  In either case, no grad recs
  cross apply  x.nodes('./DOCTORAL')r(c)

Answer 1

我相信这是您需要的语法:

declare @xmlDocPrelim as xml
set @xmlDocPrelim =
'<Record username="erttt">
      <DOCTORAL>
        <PROGRAM>Program Name 1</PROGRAM>
        <MILESTONE>Entered Program</MILESTONE>
        <DTM_DATE>August</DTM_DATE>
        <DTD_DATE>24</DTD_DATE>
        <DTY_DATE>2015</DTY_DATE>
      </DOCTORAL>
   </Record>
   <Record username="xxxgh">
      <DOCTORAL>
         <PROGRAM>Program Name 2</PROGRAM>
         <MILESTONE>Entered Program</MILESTONE>
         <DTM_DATE>Jan</DTM_DATE>
         <DTD_DATE>2</DTD_DATE>
         <DTY_DATE>2014</DTY_DATE>
     </DOCTORAL>
     <DOCTORAL>
         <PROGRAM>Program Name 2</PROGRAM>
         <MILESTONE>Graduated</MILESTONE>
         <DTM_DATE>August</DTM_DATE>
         <DTD_DATE>26</DTD_DATE>
         <DTY_DATE>2016</DTY_DATE>
     </DOCTORAL>
  </Record>
 '
 select @xmlDocPrelim

 SELECT
     x.value('@username','varchar(50)') AS username,
    c.value('./PROGRAM[1]','varchar(200)') as PROGRAM, 
    c.value('./MILESTONE[1]','varchar(50)') as MILESTONE,
    c.value('./DTM_DATE[1]','varchar(8)') as DTM_DATE,
    c.value('(./DTD_DATE)[1]','varchar(2)') as DTD_DATE,
    c.value('(./DTY_DATE)[1]','varchar(4)') as DTY_DATE
from @xmlDocPrelim.nodes('//Record') t(x)
cross apply x.nodes('DOCTORAL') r(c)

结果:

username   PROGRAM           MILESTONE           DTM_DATE DTD_DATE DTY_DATE
---------- ----------------- ------------------- -------- -------- --------
erttt      Program Name 1    Entered Program     August   24       2015
xxxgh      Program Name 2    Entered Program     Jan      2        2014
xxxgh      Program Name 2    Graduated           August   26       2016