How to extract and concat portions of an XML in TSQL

Question

I'm newbie in T-SQL and XML handling within the language, I've spent several days studying and trying to understand all these concepts, new to me. The question I'm about to ask may be a bit silly, but I would appreciate any help. I have the following XML document stored in a variable named @tblCpSttgs_fldsPrmsMap

<row>
  <targetField>ano</targetField>
  <copyProcParamAssoc>@ipYear</copyProcParamAssoc>
  <fieldContInParam>0</fieldContInParam>
</row>
<row>
  <targetField>mes</targetField>
  <copyProcParamAssoc>@ipMonth</copyProcParamAssoc>
  <fieldContInParam>0</fieldContInParam>
</row>
<row>
  <targetField>id_expediente</targetField>
  <copyProcParamAssoc>@ipExps</copyProcParamAssoc>
  <fieldContInParam>1</fieldContInParam>
</row>

Which is the result of the following query

SET @tblCpSttgs = CURSOR FOR
SELECT tblcpsttgs.id, 
        tblcpsttgs.sourceTableDB, 
        tblcpsttgs.sourceTableSch, 
        tblcpsttgs.sourceTableName, 
        tblcpsttgs.targetLinkedServer, 
        tblcpsttgs.targetTableDB, 
        tblcpsttgs.targetTableSch, 
        tblcpsttgs.targetTableName, 
        tblcpsttgs.targetGroup, 
        tblcpsttgs.copyBehavior, 
        fieldsParamsMap = CONVERT(XML, (
            SELECT fldcpsttgs.targetField, fldcpsttgs.copyProcParamAssoc, fldcpsttgs.fieldContInParam
            FROM dbo.FieldCopySettings fldcpsttgs 
            WHERE fldcpsttgs.tableCopySettingsId = tblcpsttgs.id AND
                    fldcpsttgs.copyProcParamAssoc IS NOT NULL
            FOR XML PATH
        ))
FROM dbo.TableCopySettings tblcpsttgs
ORDER BY tblcpsttgs.copyOrder DESC;

OPEN @tblCpSttgs;

FETCH NEXT FROM @tblCpSttgs INTO @tblCpSttgs_id, @tblCpSttgs_srcTblDB, @tblCpSttgs_srcTblSch, @tblCpSttgs_srcTblNm, @tblCpSttgs_tgtLnkSrv, @tblCpSttgs_tgtTblDB, @tblCpSttgs_tgtTblSch, @tblCpSttgs_tgtTblNm, @tblCpSttgs_tgtGrp, @tblCpSttgs_cpBhr, @tblCpSttgs_fldsPrmsMap;

So in this variable I have stored de XML on which I want to iterate. What I need is to concatenate the values of the nodes, if certain conditions are met, and return everything concatenated in the same text. From what I think I've understood in the documentation I studied, I made this query

WITH fldsMap AS 
(
    SELECT 
        fldsMap.rows.query('for $i in targetField return $i').value('.', 'nvarchar(max)') AS targetField,
        fldsMap.rows.query('for $i in copyProcParamAssoc return $i').value('.', 'nvarchar(max)') AS copyProcParamAssoc,
        fldsMap.rows.query('for $i in fieldContInParam return $i').value('.', 'bit') AS fieldContInParam
    FROM 
        @tblCpSttgs_fldsPrmsMap.nodes('row') fldsMap (rows)
)
SELECT 
    (CASE 
        WHEN fldsMap.fieldContInParam = 1 
           THEN CONCAT('CONCAT('','', ISNULL(', fldsMap.copyProcParamAssoc, ', RTRIM(', fldsMap.targetField, ')), '','') LIKE CONCAT(''%,'', RTRIM(', fldsMap.targetField, '), '',%'')') 
           ELSE CONCAT(fldsMap.targetField, ' = ', fldsMap.copyProcParamAssoc) 
     END)
FROM fldsMap

And I get this output

But what I really need is all concatenated in the same output, something like this

ano = @ipYear AND mes = @ipMonth 
    AND CONCAT(',', ISNULL(@ipExps, RTRIM(id_expediente)), ',') LIKE CONCAT('%,', RTRIM(id_expediente), ',%')

How can I achieve that?

Answer 1

declare @x xml = N'<row>
  <targetField>ano</targetField>
  <copyProcParamAssoc>@ipYear</copyProcParamAssoc>
  <fieldContInParam>0</fieldContInParam>
</row>
<row>
  <targetField>mes</targetField>
  <copyProcParamAssoc>@ipMonth</copyProcParamAssoc>
  <fieldContInParam>0</fieldContInParam>
</row>
<row>
  <targetField>id_expediente</targetField>
  <copyProcParamAssoc>@ipExps</copyProcParamAssoc>
  <fieldContInParam>1</fieldContInParam>
</row>';


select 
stuff(
(
SELECT N' AND ' + 
    (CASE 
        WHEN fldsMap.fieldContInParam = 1 
           THEN CONCAT('CONCAT('','', ISNULL(', fldsMap.copyProcParamAssoc, ', RTRIM(', fldsMap.targetField, ')), '','') LIKE CONCAT(''%,'', RTRIM(', fldsMap.targetField, '), '',%'')') 
           ELSE CONCAT(fldsMap.targetField, ' = ', fldsMap.copyProcParamAssoc) 
     END) 
from     
(
select 
    r.row.value('targetField[1]', 'nvarchar(100)') as targetField,
    r.row.value('copyProcParamAssoc[1]', 'nvarchar(100)') as copyProcParamAssoc,
    r.row.value('fieldContInParam[1]', 'nvarchar(100)') as fieldContInParam     
from @x.nodes('/row') as r(row)
) as fldsMap
for xml path(''), type).value('.', 'nvarchar(max)'), 1, 5, N'');


SELECT 
string_agg( 
    (CASE 
        WHEN fldsMap.fieldContInParam = 1 
           THEN CONCAT('CONCAT('','', ISNULL(', fldsMap.copyProcParamAssoc, ', RTRIM(', fldsMap.targetField, ')), '','') LIKE CONCAT(''%,'', RTRIM(', fldsMap.targetField, '), '',%'')') 
           ELSE CONCAT(fldsMap.targetField, ' = ', fldsMap.copyProcParamAssoc) 
     END) , ' and ')
from     
(
select 
    r.row.value('targetField[1]', 'nvarchar(100)') as targetField,
    r.row.value('copyProcParamAssoc[1]', 'nvarchar(100)') as copyProcParamAssoc,
    r.row.value('fieldContInParam[1]', 'nvarchar(100)') as fieldContInParam     
from @x.nodes('/row') as r(row)
) as fldsMap;


select @x.query('
let $f := /row[1]
for $r in /row
    let $fieldContInParam := ($r/fieldContInParam/text())[1]
    let $copyProcParamAssoc := ($r/copyProcParamAssoc/text())[1]
    let $targetField := ($r/targetField/text())[1]
return 
    concat( if ($r is $f) then "" else "AND ", 
    if ($fieldContInParam = 1) then 
        concat("CONCAT('','', ISNULL(", $copyProcParamAssoc, ", RTRIM(", $targetField, ")), '','') LIKE CONCAT(''%,'', RTRIM(", $targetField, "), '',%'')") 
    else
        concat($targetField, "=", $copyProcParamAssoc)
    )
'
).value('.', 'nvarchar(max)');