如何从XML表列提取多层级关联数据？SQL查询优化求助

我有一张包含XML列的数据库表，XML结构如下：

<option> 
  <OptionName>Option 1</OptionName> 
  <grant> 
    <GrantName>Grant 1</GrantName> 
    <schedules> 
      <schedule> 
        <scheduleID></scheduleID> 
        <scheduleName></scheduleName> 
        <scheduleDate>1/1/2018</scheduleDate> 
        <scheduleAmount></scheduleAmount> 
      </schedule> 
      <schedule> 
        <scheduleID></scheduleID> 
        <scheduleName></scheduleName> 
        <scheduleDate>2/1/2018</scheduleDate> 
        <scheduleAmount></scheduleAmount> 
      </schedule> 
      <schedule> 
        <scheduleID></scheduleID> 
        <scheduleName></scheduleName> 
        <scheduleDate>3/1/2018</scheduleDate> 
        <scheduleAmount></scheduleAmount> 
      </schedule> 
    </schedules> 
  </grant> 
  <grant> 
    <GrantName>Grant 2</GrantName> 
    <schedules> 
      <schedule> 
        <scheduleID></scheduleID> 
        <scheduleName></scheduleName> 
        <scheduleDate>1/1/2019</scheduleDate> 
        <scheduleAmount></scheduleAmount> 
      </schedule> 
      <schedule> 
        <scheduleID></scheduleID> 
        <scheduleName></scheduleName> 
        <scheduleDate>2/1/2019</scheduleDate> 
        <scheduleAmount></scheduleAmount> 
      </schedule> 
      <schedule> 
        <scheduleID></scheduleID> 
        <scheduleName></scheduleName> 
        <scheduleDate>3/1/2019</scheduleDate> 
        <scheduleAmount></scheduleAmount> 
      </schedule> 
    </schedules> 
  </grant>
</option>

注：修正了原XML中闭合标签不匹配的问题（比如<GrantName>对应</Name>的错误，统一改成</GrantName>，还有<scheduleID>的闭合标签）

我希望通过SQL查询得到如下格式的结果：

OptionName | GrantName | ScheduleDate
Option 1   | Grant 1   | 1/1/2018
Option 1   | Grant 1   | 2/1/2018
Option 1   | Grant 1   | 3/1/2018
Option 1   | Grant 2   | 1/1/2019
Option 1   | Grant 2   | 2/1/2019
Option 1   | Grant 2   | 3/1/2019

目前我尝试的查询语句如下：

select 
  FactChange.Fact.value('(Option/OptionName)[1]','varchar(max)') OptionName,
  FactChange.Fact.value('(Option/Grant/GrantName)[1]', 'varchar(max)') grantName 
from FactChange(nolock)

但该语句仅能提取子节点的第一个值，无法获取全部关联数据，请求优化该SQL查询以得到预期结果。

要提取XML中多层嵌套的关联数据，你需要用.nodes()方法来逐个展开<grant>和<schedule>节点，通过CROSS APPLY来关联这些层级，这样就能把每个Option下的每个Grant，再到每个Schedule都一一对应起来。

优化后的SQL语句如下：

select
  -- 提取OptionName，从根节点开始定位
  fc.Fact.value('(option/OptionName)[1]', 'varchar(max)') as OptionName,
  -- 从每个grant节点提取GrantName
  g.grantNode.value('(GrantName)[1]', 'varchar(max)') as GrantName,
  -- 从每个schedule节点提取ScheduleDate
  s.scheduleNode.value('(scheduleDate)[1]', 'varchar(max)') as ScheduleDate
from FactChange fc(nolock)
-- 展开所有grant节点
cross apply fc.Fact.nodes('option/grant') as g(grantNode)
-- 针对每个grant节点，展开其下的所有schedule节点
cross apply g.grantNode.nodes('schedules/schedule') as s(scheduleNode)

说明： #

1. fc.Fact.nodes('option/grant')：把XML列中的所有<grant>节点拆分成行，每个行对应一个grant节点，别名g(grantNode)。
2. g.grantNode.nodes('schedules/schedule')：针对每个grant节点，再拆分其下的所有<schedule>节点，每个行对应一个schedule节点，别名s(scheduleNode)。
3. 最后分别从根节点提取OptionName，从grant节点提取GrantName，从schedule节点提取ScheduleDate，这样就能得到所有层级的关联数据，完全匹配你想要的结果格式。

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