SQL Server XML列查询求助：将元素拆分为独立行而非合并

我明白你的困扰——刚接触XML查询时很容易碰到这种节点合并的问题，核心是要嵌套遍历那些包含的子节点，让每个单独生成一行。咱们一步步来解决：

首先先确认你的XML结构（方便对照）：

<Attributes>
  <Map>
    <entry key="name" value="John Doe" />
    <entry key="department" value="Finance" />
    <entry key="employeeNumber" value="123456" />
    <entry key="phone">
      <value>
        <List>
          <String>TBA</String>
        </List>
      </value>
    </entry>
    <entry key="OrgStructure">
      <value>
        <List>
          <String>top</String>
          <String>person</String>
          <String>organizationalPerson</String>
          <String>user</String>
        </List>
      </value>
    </entry>
    <entry key="Membership">
      <value>
        <List>
          <String>Group1</String>
          <String>Group2</String>
          <String>Group3</String>
        </List>
      </value>
    </entry>
  </Map>
</Attributes>

你的原查询问题在于：只遍历了entry节点，但没有深入遍历entry内部的List/String，所以多个会被合并成一行文本。

修改后的查询语句 #

SELECT 
    m.c.value('@key', 'varchar(max)') AS xmlkey,
    m.c.value('@value', 'varchar(max)') AS xmlvalue,
    ISNULL(s2.str.value('.', 'varchar(max)'), m.c.value('.', 'varchar(max)')) AS xmlString
FROM #TEMPDB AS s
CROSS APPLY s.attributes.nodes('Attributes/Map/entry') AS m(c)
-- 嵌套遍历每个entry下的List/String节点，没有则返回NULL
OUTER APPLY m.c.nodes('value/List/String') AS s2(str)

逻辑解释 #

1. CROSS APPLY s.attributes.nodes('Attributes/Map/entry')：这部分和你原查询一致，先把每个entry节点拆分成单独的行。
2. OUTER APPLY m.c.nodes('value/List/String')：这是关键——对每个entry节点，再遍历它内部的value/List/String子节点。如果某个entry没有这个结构（比如name、department这类带@value的），OUTER APPLY会返回NULL，不会丢失这些行。
3. ISNULL(...)：优先取节点的文本值；如果没有（即OUTER APPLY返回NULL），则取原entry节点的文本（这部分对带@value的节点来说是空，和你的原结果一致）。

执行后的结果 #

完全符合你的期望：

如果后续遇到更复杂的XML结构（比如同一个entry既有@value又有List），可以再加CASE判断来区分处理，但当前你的XML场景下这个查询完全够用～

内容的提问来源于stack exchange，提问作者Recycle_Bin28