基于SupporterID和CampaignNumber的XQuery分组与字段拼接需求

需求说明 #

• 基于SupporterID和CampaignNumber两个字段对XML中的<row>记录分组
• 对CampaignID按规则映射：Phone→789、Email→456、Post→123，忽略HELLO
• 用*分隔符拼接映射后的CampaignID值
• 保留CampaignData35字段的非空值000123
• 将输入的4条记录合并为1条完整XML结构

输入数据 #

let $payload:= <results
    xmlns:aetgt="http://"
    xmlns:bconn="http://">
    <row>
        <AccountID>"2404"</AccountID>
        <SupporterID>"254356368"</SupporterID>
        <DateCreated>"2023-02-11"</DateCreated>
        <DateModified/>
        <CampaignNumber>"275077"</CampaignNumber>
        <CampaignType>"QCB"</CampaignType>
        <CampaignID>"Phone"</CampaignID>
        <CampaignData35>""</CampaignData35>
    </row>
    <row>
        <AccountID>"2404"</AccountID>
        <SupporterID>"254356368"</SupporterID>
        <DateCreated>"2023-02-11"</DateCreated>
        <DateModified/>
        <CampaignNumber>"275077"</CampaignNumber>
        <CampaignType>"QCB"</CampaignType>
        <CampaignID>"Email"</CampaignID>
        <CampaignData35>""</CampaignData35>
    </row>
    <row>
        <AccountID>"2404"</AccountID>
        <SupporterID>"254356368"</SupporterID>
        <DateCreated>"2023-02-11"</DateCreated>
        <DateModified/>
        <CampaignNumber>"275077"</CampaignNumber>
        <CampaignType>"QCB"</CampaignType>
        <CampaignID>"Post"</CampaignID>
        <CampaignData35>""</CampaignData35>
    </row>
    <row>
        <AccountID>"2404"</AccountID>
        <SupporterID>"254356368"</SupporterID>
        <DateCreated>"2023-02-11"</DateCreated>
        <DateModified/>
        <CampaignNumber>"275077"</CampaignNumber>
        <CampaignType>"QCB"</CampaignType>
        <CampaignID>"HELLO"</CampaignID>
        <CampaignData35>"000123"</CampaignData35>
    </row>
</results>

期望输出 #

<results
    xmlns:aetgt="http://"
    xmlns:bconn="http://">
    <row>
        <AccountID>"2404"</AccountID>
        <SupporterID>"254356368"</SupporterID>
        <DateCreated>"2023-02-11"</DateCreated>
        <DateModified/>
        <CampaignNumber>"275077"</CampaignNumber>
        <CampaignType>"QCB"</CampaignType>
        <CampaignID>"123*456*789"</CampaignID>
        <CampaignData35>"000123"</CampaignData35>
    </row>
</results>

此前尝试的问题分析 #

第一次错误代码 #

let $post :=
  for $i in $payload//row
        return
          if ($i/CampaignID/text() eq '"Post"') then '123'
          else ''

let $email :=
  for $i in $payload//row
        return
           if ($i/CampaignID/text() eq '"Email"') then '456'
            else ''

let $phone :=
  for $i in $payload//row
        return
           if ($i/CampaignID/text() eq '"Phone"') then '789'
            else ''

return 
  $post || '*' || $email || '*' || $phone

这段代码会返回包含多个空字符串的序列，直接拼接会生成混乱的结果（比如*456*789123*），无法得到预期的拼接格式。

后续进展代码 #

let $x:= $payload
  for $z in distinct-values($x//row/SupporterID)
let $c := $x//row[SupporterID=$z]/CampaignID

    return
      concat('', string-join($c, "*"))

这段代码实现了原始CampaignID值的拼接，但未完成映射处理，也没有重建完整的XML结构。

正确实现代码 #

let $payload := <results
    xmlns:aetgt="http://"
    xmlns:bconn="http://">
    <row>
        <AccountID>"2404"</AccountID>
        <SupporterID>"254356368"</SupporterID>
        <DateCreated>"2023-02-11"</DateCreated>
        <DateModified/>
        <CampaignNumber>"275077"</CampaignNumber>
        <CampaignType>"QCB"</CampaignType>
        <CampaignID>"Phone"</CampaignID>
        <CampaignData35>""</CampaignData35>
    </row>
    <row>
        <AccountID>"2404"</AccountID>
        <SupporterID>"254356368"</SupporterID>
        <DateCreated>"2023-02-11"</DateCreated>
        <DateModified/>
        <CampaignNumber>"275077"</CampaignNumber>
        <CampaignType>"QCB"</CampaignType>
        <CampaignID>"Email"</CampaignID>
        <CampaignData35>""</CampaignData35>
    </row>
    <row>
        <AccountID>"2404"</AccountID>
        <SupporterID>"254356368"</SupporterID>
        <DateCreated>"2023-02-11"</DateCreated>
        <DateModified/>
        <CampaignNumber>"275077"</CampaignNumber>
        <CampaignType>"QCB"</CampaignType>
        <CampaignID>"Post"</CampaignID>
        <CampaignData35>""</CampaignData35>
    </row>
    <row>
        <AccountID>"2404"</AccountID>
        <SupporterID>"254356368"</SupporterID>
        <DateCreated>"2023-02-11"</DateCreated>
        <DateModified/>
        <CampaignNumber>"275077"</CampaignNumber>
        <CampaignType>"QCB"</CampaignType>
        <CampaignID>"HELLO"</CampaignID>
        <CampaignData35>"000123"</CampaignData35>
    </row>
</results>
return
<results xmlns:aetgt="http://" xmlns:bconn="http://">
    {
        for $row in $payload/row
        group by $sid := $row/SupporterID, $cn := $row/CampaignNumber
        let $first-row := $row[1]
        let $mapped-ids := 
            for $id in $row/CampaignID
            return
                switch ($id/text())
                    case '"Phone"' return '"789"'
                    case '"Email"' return '"456"'
                    case '"Post"' return '"123"'
                    case '"HELLO"' return ()
                    default return ()
        let $joined-ids := string-join($mapped-ids, "*")
        let $campaign-data35 := $row/CampaignData35[text() != '""'][1]
        return
        <row>
            {$first-row/AccountID}
            {$first-row/SupporterID}
            {$first-row/DateCreated}
            {$first-row/DateModified}
            {$first-row/CampaignNumber}
            {$first-row/CampaignType}
            <CampaignID>{$joined-ids}</CampaignID>
            <CampaignData35>{if ($campaign-data35) then $campaign-data35/text() else '""'}</CampaignData35>
        </row>
    }
</results>

代码关键点 #

1. 使用group by同时按SupporterID和CampaignNumber分组，确保符合双键分组要求
2. 通过switch语句实现CampaignID的规则映射，自动忽略不需要的HELLO值
3. 用string-join将映射后的ID用*分隔拼接
4. 提取组内CampaignData35的非空值，优先保留有效数据
5. 保留其他字段的原始值，取组内第一条记录的字段（分组后同组字段值一致）

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