如何获取多个内部节点值

Question

DECLARE @Test TABLE (ID INT NOT NULL, XmlContent XML)

INSERT INTO @Test VALUES (1,
   '<location>
      <locationName>Loc1</locationName>
      <locationInchargeName>User1</locationInchargeName>
    </location>
    <location>
      <locationName>Loc2</locationName>
      <locationInchargeName>User2</locationInchargeName>
      <locationInchargeName>User3</locationInchargeName>
    </location>')

SELECT ID, E.P.value('locationName[1]','varchar(200)') as locationName, 
    E.P.value('locationInchargeName[1]','varchar(200)') AS locationInchargeName 
FROM @Test
CROSS APPLY XmlContent.nodes('location') as E(P)

这给出了每个位置名的输出和第一个位置输入名。

locationName   locationInchargeName
-------------  ---------------------    
Loc1            User1
Loc2            User2

如何获得所有locationInchargeNames的名称及其位置名称？就像这样：

locationName   locationInchargeName
-------------  ---------------------    
Loc1            User1
Loc2            User2
Loc2            User3     

任何帮助都将不胜感激。

Answer 1

试试这个：

SELECT 
    ID, 
    XTbl1.Loc.value('locationName[1]','varchar(200)') as locationName, 
    XTbl2.InCharge.value('.','varchar(200)') AS locationInchargeName 
FROM @Test
CROSS APPLY XmlContent.nodes('location') as XTbl1(Loc)
CROSS APPLY XTbl1.Loc.nodes('locationInchargeName') as XTbl2(InCharge)