如何在同一存储过程中合并SQL WITH子句XML解析结果与另一SELECT查询

解决方案 #

你不需要使用UNION/UNION ALL，你的需求是给第二个查询的结果新增CountryMarket字段，直接将XML解析逻辑合并到第二个查询中即可：两个查询的核心数据源都包含Archive_ProgUsers表，每行可以直接解析自身对应的ContactInformationXML字段值拿到国家市场信息。

合并后的完整查询代码如下：

WITH CastToXML AS (
    SELECT 
        id AS user_table_id,
        user_id,
        email,
        ContactInformationXML,
        -- 直接解析XML拿到CountryMarket
        TRY_CAST(ContactInformationXML AS xml).value('(/user/contact/@CountryMarket)[1]','nvarchar(max)') AS CountryMarket
    FROM Archive_ProgUsers
    WHERE 
        ContactInformationXML IS NOT NULL 
        AND ContactInformationXML <> '' 
        AND ContactInformationXML <> 'Admin - Old Program in Use' 
        AND ContactInformationXML <> 'Admin - Admin Edits'
)
SELECT DISTINCT
    c.user_id,
    c.email,
    i.app_id,
    c.ContactInformationXML,
    i.install_code,
    hitsCount.programStarts,
    c.CountryMarket
FROM CastToXML c
INNER JOIN Archive_Installations i ON c.user_table_id = i.user_id
INNER JOIN (
    SELECT DISTINCT inst_id 
        , COUNT(CASE WHEN Archive_Hits.type <> 'New' AND Archive_Hits.accessed_on BETWEEN CAST(@startdate AS DATE) AND CAST(@enddate AS DATE) THEN 1 END) AS programStarts
    FROM Archive_Hits
    GROUP BY Archive_Hits.inst_id 
) AS hitsCount ON hitsCount.inst_id = i.id
WHERE hitsCount.programStarts > 0
ORDER BY hitsCount.programStarts DESC

说明 #

如果你的Archive_ProgUsers表主键就是id，上面的逻辑可以直接运行，最终输出会在你原有第二个查询的列基础上新增CountryMarket列，完全匹配目标结果表结构。如果部分XML格式异常无法解析，TRY_CAST会返回NULL，不会打断整个查询。

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