Get values from XML tags with dynamically specified data fields

Question

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!

radar · Accepted Answer · 2014-10-09 21:30:13Z

2

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

answered Oct 9, 2014 at 21:30

radar

13.4k2 gold badges27 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Get values from XML tags with dynamically specified data fields

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related