How to retrieve xml data from multiple columns from SQL table with comma seperated

A simplified example (using only one XML column) will look like this:

drop table if exists #Temp;

create table #Temp (
    Name varchar(50) not null,
    XMLData xml null
);

insert into #Temp (Name, XMLData)
values ('Test1', N'<collection>
    <object> 
        <fields> 
            <field>
                 <value>Savings account</value>
            </field>
        </fields>
    </object>
    <object>
        <fields>
             <field>
                 <value>Mortgage</value>
             </field>
        </fields>
    </object>
</collection>'),
('Test3', null);

select t.Name, x1.ValueList
from #Temp t
    cross apply (
        select string_agg(v.c.value('.', 'varchar(max)'), ', ') as [ValueList]
        from t.XMLData.nodes('/collection/object/fields/field/value') v(c)
    ) x1;

You'll need a separate subquery like x1 for each XML column in the source.

Note that string_agg() function is only available in SQL Server 2017 or later versions. Prior to that, you'd have to implement concatenation using FOR XML PATH, like this.

Please try the following solution based on SQL Server's pure XQuery.

SQL

-- DDL and sample data population, start
DECLARE @tbl table (Name varchar(50) not null, XMLData xml null);
INSERT INTO @tbl (Name, XMLData) values 
('Test1', N'<collection>
    <object> 
        <fields> 
            <field>
                 <value>Savings account</value>
            </field>
        </fields>
    </object>
    <object>
        <fields>
             <field>
                 <value>Mortgage</value>
             </field>
        </fields>
    </object>
</collection>'),
('Test3', null);
-- DDL and sample data population, end

SELECT Name
    , XMLData.query('
        let $last := (/collection/object/fields/field/value)[last()] 
        for $i in /collection/object/fields/field/value
        return if ($i is $last[1]) then data($i)
            else concat(data($i), ",")
    ').value('text()[1]', 'NVARCHAR(MAX)') AS Result
FROM @tbl;

Output

If you don't mind a trailing comma then you can actually concatenate within XQuery.

select
  t.Name,
  t.XMLData.query('for $i in /collection/object/fields/field/value/text() return concat($i, ", ")').value('text()[1]', 'nvarchar(max)') 
from #Temp t;

.query will just lump all the text nodes together. The .value is necessary to prevent XML escaping/entitization.