在ADF中用concat函数将varchar转Base64时遇语法错误求解决

问题场景 #

在增量加载场景下，使用ADF的concat函数生成SQL语句，将CLAUSE_TEXT_BLOB列转换为Base64编码，原ADF表达式如下：

@if(
    contains(activity('ADS_Lookup').output,'firstRow'),
    concat(
        'select ID,POLICY_ASSET_ID,ASSET_CLAUSE_ID,ADDITIONAL_TEXT,UPDATE_USER,UPDATE_DATE,UPDATE_VERSION,CANCEL_DATE,CANCEL_USER,ACTIVE_REASON_TYPE_ID,ADDITIONAL_AMOUNT,CLAUSE_TEXT,CAST(N'' AS XML).value(''xs:base64Binary(xs:hexBinary(sql:column("CLAUSE_TEXT_BLOB")))' , 'VARCHAR(MAX)'') AS CLAUSE_TEXT_BLOB, BESPOKE_SUBJECT,CLAUSE_VERSION_ID,EXT_DATA,EXTRA_DATA_BLOB_ID from [',
        variables('schema'),
        '].[',
        if(variables('custom_table_list_flag'),item(),item().name),
        '] where update_date >=',
        '''',
        activity('ADS_Lookup').output.firstRow.startTime,
        ''''
    ),
    concat(
        'select ID,POLICY_ASSET_ID,ASSET_CLAUSE_ID,ADDITIONAL_TEXT,UPDATE_USER,UPDATE_DATE,UPDATE_VERSION,CANCEL_DATE,CANCEL_USER,ACTIVE_REASON_TYPE_ID,ADDITIONAL_AMOUNT,CLAUSE_TEXT,CAST(N'' AS XML).value(''xs:base64Binary(xs:hexBinary(sql:column("CLAUSE_TEXT_BLOB")))' , 'VARCHAR(MAX)'') AS CLAUSE_TEXT_BLOB, BESPOKE_SUBJECT,CLAUSE_VERSION_ID,EXT_DATA,EXTRA_DATA_BLOB_ID from [',
        variables('schema'),
        '].[',
        if(variables('custom_table_list_flag'),item(),item().name),
        ']'
    )
)

错误信息 #

执行时出现Source端SQL操作失败：

Failure happened on 'Source' side.
ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A
database operation failed with the following error: 'Incorrect syntax
near 'xs:'. The label 'xs' has already been declared. Label names must
be unique within a query batch or stored procedure. Unclosed quotation
mark after the character string
''',Source=,''Type=System.Data.SqlClient.SqlException,Message=Incorrect
syntax near 'xs:'. The label 'xs' has already been declared. Label
names must be unique within a query batch or stored procedure.
Unclosed quotation mark after the character string ''.,Source=.Net
SqlClient Data
Provider,SqlErrorNumber=102,Class=15,ErrorCode=-2146232060,State=1,Errors=[{Class=15,Number=102,State=1,Message=Incorrect
syntax near 'xs:'.,},{Class=15,Number=132,State=1,Message=The label
'xs' has already been declared. Label names must be unique within a
query batch or stored
procedure.,},{Class=15,Number=105,State=1,Message=Unclosed quotation
mark after the character string ''.,},]

问题原因 #

核心问题是ADF表达式中字符串转义错误：

• 在CAST(N'' AS XML).value(...)部分，嵌套的单引号未正确转义，导致生成的SQL出现未闭合的引号。
• 错误的转义使得SQL解析时把xs:误判为重复的标签，引发语法错误。

修正后的ADF表达式 #

将Base64转换部分的字符串正确转义，ADF中需要用四个单引号表示SQL中的两个单引号，同时调整括号和引号的匹配：

@if(
    contains(activity('ADS_Lookup').output,'firstRow'),
    concat(
        'select ID,POLICY_ASSET_ID,ASSET_CLAUSE_ID,ADDITIONAL_TEXT,UPDATE_USER,UPDATE_DATE,UPDATE_VERSION,CANCEL_DATE,CANCEL_USER,ACTIVE_REASON_TYPE_ID,ADDITIONAL_AMOUNT,CLAUSE_TEXT,CAST(N'''' AS XML).value(''xs:base64Binary(xs:hexBinary(sql:column("CLAUSE_TEXT_BLOB")))'', ''VARCHAR(MAX)'') AS CLAUSE_TEXT_BLOB, BESPOKE_SUBJECT,CLAUSE_VERSION_ID,EXT_DATA,EXTRA_DATA_BLOB_ID from [',
        variables('schema'),
        '].[',
        if(variables('custom_table_list_flag'),item(),item().name),
        '] where update_date >= ''',
        activity('ADS_Lookup').output.firstRow.startTime,
        ''''
    ),
    concat(
        'select ID,POLICY_ASSET_ID,ASSET_CLAUSE_ID,ADDITIONAL_TEXT,UPDATE_USER,UPDATE_DATE,UPDATE_VERSION,CANCEL_DATE,CANCEL_USER,ACTIVE_REASON_TYPE_ID,ADDITIONAL_AMOUNT,CLAUSE_TEXT,CAST(N'''' AS XML).value(''xs:base64Binary(xs:hexBinary(sql:column("CLAUSE_TEXT_BLOB")))'', ''VARCHAR(MAX)'') AS CLAUSE_TEXT_BLOB, BESPOKE_SUBJECT,CLAUSE_VERSION_ID,EXT_DATA,EXTRA_DATA_BLOB_ID from [',
        variables('schema'),
        '].[',
        if(variables('custom_table_list_flag'),item(),item().name),
        ']'
    )
)

关键修正点 #

1. CAST(N'' AS XML)改为CAST(N'''' AS XML)：ADF中每两个单引号会被解析为一个单引号，四个单引号最终生成SQL中的两个单引号，确保N''正确表示空字符串。
2. 修正value方法的引号匹配：将value(''xs:...' , 'VARCHAR(MAX)'')改为value(''xs:...'', ''VARCHAR(MAX)'')，确保逗号前后的引号正确闭合，避免语法断裂。
3. 简化日期条件的拼接：将where update_date >='', '''', ...改为where update_date >= ''', ...，直接用>=替代转义的>=，ADF会自动处理HTML转义，提升表达式可读性。

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