从具有动态指定的数据字段的XML标记中获取值 [英] Get values from XML tags with dynamically specified data fields

问题描述

我有2张桌子:

表1列出了我想从XML字段中提取的XML标记名称.我通过运行此查询来模拟这一点

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'

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

这将导致单列中包含3行，其名称将用于从XML标记中提取相应的数据.

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.

第二个表具有名为ClientData的列，它为XML格式，并且具有数千行数据.我的任务是从Table1中指定的XML标记中提取值，在这种情况下，我希望从3个xml标记中获取值:客户端，FEE和ADDRESS.

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.

因此，如果XML是

<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

现在我有一个查询:

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)

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)

这给了我必要的结果，但是它不是动态的.意思是，如果我想包含第4个xml值，例如PHONE，则需要在SELECT中添加coalesce(Cast(ClientData as xml).value('(/XML/PHONE)[1]', 'varchar(max)'), '')

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

我的问题是， 如何使查询动态化，以代替将要从Table2中的XML中提取的标记名进行硬编码，而是使用Tabl1作为要提取的标记名的来源?

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 :)

谢谢！

推荐答案

您可以使用DYNAMIC SQL

TagsTable应该包含所有可能的标签

The TagsTable should have all the possible Tags

然后我们可以使用标签名称构造SQL并执行

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

这篇关于从具有动态指定的数据字段的XML标记中获取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！