从SQL Server表XML列提取数据添加reference_number后无返回行

1. 命名空间未处理：你的XML里<message>、<m_content>、<payee_firm>节点都带有不同的命名空间，原查询没有声明这些命名空间，SQL Server无法匹配到对应的节点，所以添加MY_XML3的CROSS APPLY后返回0行。
2. 提取语法错误：reference_number是XML元素而非属性，原脚本里用@reference_number（属性语法）是错误的，应该用元素文本提取的方式。

WITH XMLNAMESPACES (
    DEFAULT 'http://www.origostandards.com/schema/soap/v1',
    'http://www.origostandards.com/schema/ReceiveRemunerationStatement/v1/LoadStatementRequest' AS lr,
    'http://www.origostandards.com/schema/RemunerationStatement/v1' AS rs
)
SELECT 
    [Id],
    MY_XML.Pub.value('@CompanyName','varchar(250)') AS CompanyName,
    MY_XML2.Pub.value('@SystemDate','date') AS SystemDate,
    MY_XML3.Pub.value('(rs:reference_number/text())[1]','varchar(10)') AS ReferenceNumber,
    xmldata
FROM dbo.XMLFilesTable as tbl
CROSS APPLY xmldata.nodes('Publication/Addressee') AS MY_XML (Pub)
CROSS APPLY xmldata.nodes('Publication/Content/CommonExtract') AS MY_XML2 (Pub)
CROSS APPLY xmldata.nodes('Publication/Content/CommonExtract/message/lr:m_content/rs:payee_firm/rs:reference') AS MY_XML3 (Pub)

• 用WITH XMLNAMESPACES声明所有涉及的命名空间，给非默认命名空间起别名（lr、rs），方便在节点路径中引用
• 节点路径中添加对应命名空间别名，比如lr:m_content对应m_content节点的命名空间，rs:payee_firm对应payee_firm的命名空间
• 提取reference_number时，改用(rs:reference_number/text())[1]语法，获取元素的文本内容，避免属性语法的错误

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