Get values from XML tags with dynamically specifie

I have 2 tables:

Table1 has a list of XML tag names that I want to extract from an XML field. I simulate this by running this query

SELECT 'CLIENT' UNION SELECT 'FEE' UNION SELECT 'ADDRESS'

This results in a single column with 3 rows in it, the names of which will be used to extract corresponding data from XML tags.

The second table has a column called ClientData, it is in XML format and it has thousands of rows of data. My task is to extract values from XML tags specified in Table1, in this case I want values from 3 xml tags: Client, FEE and ADDRESS.

So, if the XML is this

<XML>
    <CLIENT>some client</CLIENT>
    <FEE>some fee</FEE>
    <ADDRESS>some address</ADDRESS>
 </XML>

After running a query I should get this:

Client, FEE, ADDRESS

some client, some fee, some address

Right now i have a query:

SELECT coalesce(Cast(ClientData as xml).value('(/XML/CLIENT)[1]', 'varchar(max)'), ''), coalesce(Cast(ClientData as xml).value('(/XML/FEE)[1]', 'varchar(max)'), ''), coalesce(Cast(ClientData as xml).value('(/XML/ADDRESS)[1]', 'varchar(max)'), '') FROM dbo.Table2 WITH(NOLOCK)

This gives me the necessary result, however it is not dynamic. Meaning, if I want to include a 4th xml value, lets say, PHONE, I would need to add coalesce(Cast(ClientData as xml).value('(/XML/PHONE)[1]', 'varchar(max)'), '') to the SELECT

My question is, How do I make my query dynamic so that instead of hardcoding tag names that I want to extract from XML in Table2 I would use Tabl1 as a source of tag names to extract?

I hope my explanation was good enough :)

Thank you!

You can achieve this using DYNAMIC SQL

The TagsTable should have all the possible Tags

we can then construct SQL using the tag names and execute it

create table TagsTable
(  tagName varchar(256)
)

insert into TagsTable values ('CLIENT')
insert into TagsTable values ('FEE')
insert into TagsTable values ('ADDRESS')

declare @query nvarchar(max)

SELECT @query = STUFF((select  ',' + 'coalesce(Cast(ClientData as xml).value(''(/XML/' 
                                   + tagName + ')[1]'', ''varchar(max)''), '''') as ' + tagName +' '
FROM TagsTable
FOR XML PATH ('') ), 1,1,'')


SET @query = 'SELECT ' +  @query + 'FROM dbo.Table2 WITH(NOLOCK)'
select @query

exec sp_executesql @query