从表中提取XML数据返回空结果，寻求技术帮助

1. 先确认XML数据结构与路径正确性 #

首先执行以下语句，查看目标记录的XML实际内容，确认根节点是否为OpportunityID，是否存在命名空间：

SELECT pol_interface_data FROM policies WHERE pol_id = 1952292

• 如果XML包含命名空间（如<ns:OpportunityID xmlns:ns="http://xxx">），需在查询中声明命名空间，示例：

WITH XMLNAMESPACES (DEFAULT 'http://你的命名空间URL')
SELECT C.value('local-name(.)', 'varchar(50)'), C.value('text()[1]', 'varchar(50)')
FROM policies pol
CROSS APPLY pol.pol_interface_data.nodes('/OpportunityID/*') AS T(C)
WHERE pol.pol_id = 1952292

• 如果根节点不是OpportunityID，需调整nodes()方法中的XPath路径，比如实际根是Root/OpportunityID，则改为nodes('/Root/OpportunityID/*')。

2. 检查表连接是否过滤了数据 #

原查询使用INNER JOIN general_insurance，如果pol_parent_id对应的genins_id不存在，会导致记录被排除。改为LEFT JOIN测试：

INSERT INTO #CustomRiskFormTable
SELECT dense_rank() OVER (ORDER BY C.value('local-name(.)', 'varchar(50)')) 
     , C.value('local-name(.)', 'varchar(50)') 
     , C.value('text()[1]', 'varchar(50)')
     , pol.pol_id 
FROM policies pol WITH (NOLOCK)
LEFT JOIN general_insurance gen ON pol.pol_parent_id = gen.genins_id
CROSS APPLY pol.pol_interface_data.nodes('/OpportunityID/*') AS T(C)
WHERE pol.pol_id = 1952292

执行后查询临时表，确认是否有数据：

SELECT * FROM #CustomRiskFormTable

3. 修复Pivot子查询的逻辑错误 #

原Pivot子查询仅选取了ent.ent_name，完全未关联临时表或包含ColumnName、Value字段，导致Pivot无数据可处理。重构后的Pivot语句示例（需根据实际表结构调整关联字段）：

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(ColumnName) 
                      FROM #CustomRiskFormTable 
                      GROUP BY ColumnName, id 
                      ORDER BY id 
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SET @query = N'
SELECT [Client Name], [Class of Risk], [Policy Description], [Policy Number], 
       [Start Date], [Renewal Date], [Base Premium], [Invoice Total], PolicyId, ' + @cols + N' 
FROM ( 
    SELECT 
        ent.ent_name AS ''Client Name'',
        gen.gen_class_of_risk AS ''Class of Risk'', -- 假设从general_insurance表取
        pol.pol_description AS ''Policy Description'',
        pol.pol_number AS ''Policy Number'',
        pol.pol_start_date AS ''Start Date'',
        pol.pol_renewal_date AS ''Renewal Date'',
        pol.pol_base_premium AS ''Base Premium'',
        pol.pol_invoice_total AS ''Invoice Total'',
        crft.ColumnName,
        crft.Value,
        crft.PolicyId
    FROM #CustomRiskFormTable crft
    JOIN policies pol ON crft.PolicyId = pol.pol_id
    JOIN general_insurance gen ON pol.pol_parent_id = gen.genins_id
    JOIN entities ent ON pol.pol_entity_id = ent.ent_id -- 假设客户名称来自entities表
) x 
PIVOT ( 
    MAX(Value) FOR ColumnName IN (' + @cols + N') 
) p '

EXEC sp_executesql @query;

4. 分步测试建议 #

• 先单独测试XML节点提取语句，确认能获取到字段和值；
• 确认临时表#CustomRiskFormTable有数据后，再执行Pivot部分的逻辑；
• 若某字段（如Class of Risk）实际来自其他表，需根据数据库实际结构调整关联和字段名。

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