Teradata解析Swift ISO支付XML：忽略可变命名空间问题求助

需求与问题 #

需要在Teradata中编写SQL查询，提取Swift ISO支付类XML（如PACS04、PACS08等）的属性与元素数据。当前核心问题是XML的命名空间前缀不固定——示例中用的是pacs，但实际场景中可能是任意字母数字组合，直接用固定前缀或通配符尝试都无效，最终要将查询封装为视图供用户直接使用。

示例XML #

<pacs:Document xmlns:pacs="urn:iso:std:iso:20022:tech:xsd:pacs.009.001.08" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <pacs:FICdtTrf>
        <pacs:GrpHdr>
            <pacs:MsgId>XXXXXXXXX</pacs:MsgId>
            <pacs:CreDtTm>2022-09-12T10:18:09+08:00</pacs:CreDtTm>
            <pacs:NbOfTxs>1</pacs:NbOfTxs>
            <pacs:SttlmInf>
                <pacs:SttlmMtd>CLRG</pacs:SttlmMtd>
                <pacs:ClrSys>
                    <pacs:Cd>MEP</pacs:Cd>
                </pacs:ClrSys>
            </pacs:SttlmInf>
        </pacs:GrpHdr>
        <pacs:CdtTrfTxInf>
            <pacs:PmtId>
                <pacs:InstrId>YYYYYYYYY</pacs:InstrId>
                <pacs:EndToEndId>ZZZZZZZZ</pacs:EndToEndId>
                <pacs:UETR>UETRRRRR</pacs:UETR>
            </pacs:PmtId>
            <pacs:IntrBkSttlmAmt Ccy="INR">10000</pacs:IntrBkSttlmAmt>
            <pacs:IntrBkSttlmDt>2022-09-12</pacs:IntrBkSttlmDt>
            <pacs:InstgAgt>
                <pacs:FinInstnId>
                    <pacs:BICFI>BICXXXX</pacs:BICFI>
                </pacs:FinInstnId>
            </pacs:InstgAgt>
            <pacs:InstdAgt>
                <pacs:FinInstnId>
                    <pacs:BICFI>BICZZZZZ</pacs:BICFI>
                </pacs:FinInstnId>
            </pacs:InstdAgt>
            <pacs:Dbtr>
                <pacs:FinInstnId>
                    <pacs:BICFI>BIXYYYYY</pacs:BICFI>
                </pacs:FinInstnId>
            </pacs:Dbtr>
            <pacs:CdtrAgt>
                <pacs:FinInstnId>
                    <pacs:BICFI>BICOOOOOO</pacs:BICFI>
                </pacs:FinInstnId>
            </pacs:CdtrAgt>
            <pacs:Cdtr>
                <pacs:FinInstnId>
                    <pacs:BICFI>BICPPPPPPPP</pacs:BICFI>
                </pacs:FinInstnId>
            </pacs:Cdtr>
        </pacs:CdtTrfTxInf>
    </pacs:FICdtTrf>
</pacs:Document>

尝试的代码 #

SELECT X.*
FROM (SELECT * FROM DB_NAME.TABLE_NAME) AS C,
   XMLTable (
      '/Document/FICdtTrf/GrpHdr'
      PASSING C.PACS_XML
      COLUMNS

           "Seqno" FOR ORDINALITY,
           "MsgId" VARCHAR(35) PATH '//MsgId',
           "CreDtTm" VARCHAR(35) PATH '//CreDtTm',
           "NbOfTxs" VARCHAR(35) PATH '//NbOfTxs',
           "SttlmAmt" VARCHAR(12) PATH '../CdtTrfTxInf/IntrBkSttlmAmt'

   ) AS X ("Sequence #", "MSG_ID", "CREATION_TMP", "TXN_COUNT", "SttlmAmt");

解决方案 #

Teradata的XML处理支持使用*:前缀匹配任意命名空间前缀的元素，正好解决前缀不固定的问题。同时要避免//全文档搜索带来的性能损耗，改用精准路径导航。

修正后的查询代码 #

SELECT 
    X."Sequence #",
    X."MSG_ID",
    X."CREATION_TMP",
    X."TXN_COUNT",
    X."STTLM_AMT",
    X."STTLM_CCY",
    X."END_TO_END_ID"
FROM DB_NAME.TABLE_NAME AS C,
XMLTable (
    '/*:Document/*:FICdtTrf'
    PASSING C.PACS_XML
    COLUMNS
        "Sequence #" INTEGER FOR ORDINALITY,
        "MSG_ID" VARCHAR(35) PATH '*:GrpHdr/*:MsgId',
        "CREATION_TMP" TIMESTAMP(0) PATH '*:GrpHdr/*:CreDtTm',
        "TXN_COUNT" INTEGER PATH '*:GrpHdr/*:NbOfTxs',
        "STTLM_AMT" DECIMAL(18,2) PATH '*:CdtTrfTxInf/*:IntrBkSttlmAmt',
        "STTLM_CCY" CHAR(3) PATH '*:CdtTrfTxInf/*:IntrBkSttlmAmt/@Ccy',
        "END_TO_END_ID" VARCHAR(35) PATH '*:CdtTrfTxInf/*:PmtId/*:EndToEndId'
) AS X;

封装为视图 #

将上述查询封装为视图，方便用户直接查询：

CREATE VIEW DB_NAME.PAYMENT_XML_VIEW AS
SELECT 
    X."Sequence #",
    X."MSG_ID",
    X."CREATION_TMP",
    X."TXN_COUNT",
    X."STTLM_AMT",
    X."STTLM_CCY",
    X."END_TO_END_ID"
FROM DB_NAME.TABLE_NAME AS C,
XMLTable (
    '/*:Document/*:FICdtTrf'
    PASSING C.PACS_XML
    COLUMNS
        "Sequence #" INTEGER FOR ORDINALITY,
        "MSG_ID" VARCHAR(35) PATH '*:GrpHdr/*:MsgId',
        "CREATION_TMP" TIMESTAMP(0) PATH '*:GrpHdr/*:CreDtTm',
        "TXN_COUNT" INTEGER PATH '*:GrpHdr/*:NbOfTxs',
        "STTLM_AMT" DECIMAL(18,2) PATH '*:CdtTrfTxInf/*:IntrBkSttlmAmt',
        "STTLM_CCY" CHAR(3) PATH '*:CdtTrfTxInf/*:IntrBkSttlmAmt/@Ccy',
        "END_TO_END_ID" VARCHAR(35) PATH '*:CdtTrfTxInf/*:PmtId/*:EndToEndId'
) AS X;

关键说明 #

1. *:前缀匹配：比如*:Document匹配任意前缀下的Document元素，彻底解决命名空间前缀不固定的问题。
2. 精准路径导航：替换原来的//全文档搜索，改用从根节点开始的层级路径，提升查询性能。
3. 数据类型优化：将字符串类型改为贴合业务的类型（如TIMESTAMP、INTEGER、DECIMAL），避免后续转换。
4. 属性提取：通过@Ccy语法提取IntrBkSttlmAmt元素的货币属性，满足支付类XML的属性提取需求。

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