在TSQL中解析带命名空间的XML：获取所有子节点记录

我明白你的需求了——现在你需要从带命名空间的XML里把所有PhysicalAddress节点都提取出来，每个地址对应一条数据库记录，而不是只能取单个字段，还要避免重复的地址行。咱们一步步来解决这个问题：

首先看你提供的XML示例 #

<PostData xmlns="mypath"> 
  <data> 
    <Studentebo:CurrentEBO xmlns:Studentebo="MMMMMMM"> 
      <Studentebo:Person> 
        <ns6:PersonName xmlns:ns6="ZZZZZZZZZZZZZZ" xmlns="ZZZZZZZZZZZZZZ"> 
          <ns6:FirstName>Example3</ns6:FirstName> 
          <ns6:MiddleName>Example3</ns6:MiddleName> 
          <ns6:LastName>Example4</ns6:LastName> 
          <ns6:PreferredName>testTest4</ns6:PreferredName> 
          <ns6:Title>MRS.</ns6:Title> 
        </ns6:PersonName> 
        <ns6:PhysicalAddresses xmlns:ns6="ZZZZZZZZZZZZZZ" xmlns="ZZZZZZZZZZZZZZ"> 
          <ns6:PhysicalAddress> 
            <ns15:Identification xmlns:ns15="OOOOO" xmlns="OOOOO"> 
              <ns15:AddressNumber>125a6407-1b91-4d2c-a783-280127f38249</ns15:AddressNumber> 
            </ns15:Identification> 
            <ns15:AddressUsages xmlns:ns15="XXXXXXX" xmlns="XXXXXXX"> 
              <ns15:AddressUsage> 
                <ns9:UsageCode xmlns:ns9="QQQQQ" xmlns="QQQQQ">HOME</ns9:UsageCode> 
              </ns15:AddressUsage> 
            </ns15:AddressUsages> 
            <ns15:EffectiveDates xmlns:ns15="XXXXXXX" xmlns="XXXXXXX"> 
              <ns12:StartDateTime xmlns:ns12="LLLLLL" xmlns="LLLLLL">2018-01-07T00:00:00+00:00</ns12:StartDateTime> 
              <ns12:EndDateTime xmlns:ns12="LLLLLL" xmlns="LLLLLL">2064-01-03T00:00:00+00:00</ns12:EndDateTime> 
            </ns15:EffectiveDates> 
            <Address1 xmlns:ns12="XXXXXXX" xmlns="XXXXXXX">Testing Address line2</Address1> 
            <Address2 xmlns:ns12="XXXXXXX" xmlns="XXXXXXX">test4 line2</Address2> 
            <Address3 xmlns:ns12="XXXXXXX" xmlns="XXXXXXX">line33</Address3> 
            <City xmlns:ns12="XXXXXXX" xmlns="XXXXXXX">Testing</City> 
            <PostalCode xmlns:ns12="XXXXXXX" xmlns="XXXXXXX">Testing</PostalCode> 
            <Country xmlns:ns12="XXXXXXX" xmlns="XXXXXXX">Testing</Country> 
          </ns6:PhysicalAddress> 
          <ns6:PhysicalAddress> 
            <ns15:Identification xmlns:ns15="OOOOO" xmlns="OOOOO"> 
              <ns15:AddressNumber>125a6407-1b91-4d2c-a783-280127f38249</ns15:AddressNumber> 
            </ns15:Identification> 
            <ns15:AddressUsages xmlns:ns15="XXXXXXX" xmlns="XXXXXXX"> 
              <ns15:AddressUsage> 
                <ns9:UsageCode xmlns:ns9="QQQQQ" xmlns="QQQQQ">HOME</ns9:UsageCode> 
              </ns15:AddressUsage> 
            </ns15:AddressUsages> 
            <ns15:EffectiveDates xmlns:ns15="XXXXXXX" xmlns="XXXXXXX"> 
              <ns12:StartDateTime xmlns:ns12="LLLLLL" xmlns="LLLLLL">2018-01-07T00:00:00+00:00</ns12:StartDateTime> 
              <ns12:EndDateTime xmlns:ns12="LLLLLL" xmlns="LLLLLL">2064-01-03T00:00:00+00:00</ns12:EndDateTime> 
            </ns15:EffectiveDates> 
            <Address1 xmlns:ns12="XXXXXXX" xmlns="XXXXXXX">Testing Address line2</Address1> 
            <Address2 xmlns:ns12="XXXXXXX" xmlns="XXXXXXX">test4 line2</Address2> 
            <Address3 xmlns:ns12="XXXXXXX" xmlns="XXXXXXX">line33</Address3> 
            <City xmlns:ns12="XXXXXXX" xmlns="XXXXXXX">Testing</City> 
            <PostalCode xmlns:ns12="XXXXXXX" xmlns="XXXXXXX">Testing</PostalCode> 
            <Country xmlns:ns12="XXXXXXX" xmlns="XXXXXXX">Testing</Country> 
          </ns6:PhysicalAddress> 
        </ns6:PhysicalAddresses> 
      </Studentebo:Person> 
    </Studentebo:CurrentEBO> 
  </data> 
</PostData>

分析你当前语句的问题 #

你现在的语句只提取了Address1字段，而且路径里的CAM2:Address1存在命名空间错误（XML里Address1的命名空间是XXXXXXX，不是OOOOO），另外没有完整遍历每个PhysicalAddress节点并提取所有字段。

解决方案：统一声明命名空间+提取全量地址字段 #

我们可以用WITH XMLNAMESPACES统一声明所有需要的命名空间，避免重复书写，然后定位到每个PhysicalAddress节点，再从每个节点里提取所有相关字段：

WITH XMLNAMESPACES (
    'mypath' AS C1,
    'MMMMMMM' AS Studentebo,
    'ZZZZZZZZZZZZZZ' AS ns6,
    'OOOOO' AS ns15_id,
    'XXXXXXX' AS ns15_addr,
    'QQQQQ' AS ns9,
    'LLLLLL' AS ns12
)
SELECT 
    id,
    -- 提取地址唯一标识
    t.r.value('(ns15_id:Identification/ns15_id:AddressNumber)[1]', 'uniqueidentifier') AS AddressNumber,
    -- 提取地址用途
    t.r.value('(ns15_addr:AddressUsages/ns15_addr:AddressUsage/ns9:UsageCode)[1]', 'varchar(50)') AS UsageCode,
    -- 提取生效日期
    t.r.value('(ns15_addr:EffectiveDates/ns12:StartDateTime)[1]', 'datetimeoffset') AS StartDateTime,
    t.r.value('(ns15_addr:EffectiveDates/ns12:EndDateTime)[1]', 'datetimeoffset') AS EndDateTime,
    -- 提取地址详情
    t.r.value('(Address1)[1]', 'varchar(100)') AS Address1,
    t.r.value('(Address2)[1]', 'varchar(100)') AS Address2,
    t.r.value('(Address3)[1]', 'varchar(100)') AS Address3,
    t.r.value('(City)[1]', 'varchar(50)') AS City,
    t.r.value('(PostalCode)[1]', 'varchar(20)') AS PostalCode,
    t.r.value('(Country)[1]', 'varchar(50)') AS Country
FROM [XXX_SH_SOAService_formattedXML]
OUTER APPLY [XML].nodes('/C1:PostData/C1:data/Studentebo:CurrentEBO/Studentebo:Person/ns6:PhysicalAddresses/ns6:PhysicalAddress') t(r)
-- 可选：如果要去重，添加DISTINCT关键字
-- DISTINCT
WHERE t.r.value('(ns15_id:Identification/ns15_id:AddressNumber)[1]', 'uniqueidentifier') IS NOT NULL -- 过滤空地址

关键说明： #

1. 统一命名空间声明：用WITH XMLNAMESPACES把所有用到的命名空间定义好，后续XPath会更简洁，也避免了重复声明的麻烦。
2. 定位每个PhysicalAddress：通过nodes()方法定位到每个ns6:PhysicalAddress节点，每个节点会自动生成一行记录。
3. 提取字段：从每个节点t.r下提取对应的子节点值，用[1]确保只取第一个匹配的节点（避免返回多个值报错）。
4. 去重处理：如果XML里有重复的地址（比如你示例里的两个完全一样的地址），可以加上DISTINCT关键字，或者根据AddressNumber字段过滤重复行。

这样执行后，每个PhysicalAddress节点都会生成一条对应的记录，包含该地址的所有字段，完美满足你的需求！

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