使用变量获取SQL xml属性值

I have a SQL function that takes a variable called attribute, which is the xml attribute I want to get the value from. xmlPath is the full XML string.

我有一个SQL函数，它接受一个名为attribute的变量，这是我想从中获取值的xml属性。 xmlPath是完整的XML字符串。

My xml looks like this:

我的xml看起来像这样：

<EventSpecificData>
  <Keyword>
    <Word>myWord</Word>
    <Occurences>1</Occurences>
    <Context>context</Context>
  </Keyword>
</EventSpecificData>

I want to get the value for <Word>, so I pass in /Keyword/Word and set a variable to:

我想得到的值，所以我传入/ Keyword / Word并将变量设置为：

set @value = @xmlPath.value('(/EventSpecificData/@attribute)[1]', 'varchar(max)')

However, I don't think @attribute is actually inserting the variables string. Is there another way to do this?

但是，我不认为@attribute实际上是插入变量字符串。还有另一种方法吗？

2 个解决方案

#1

Here are a couple of solutions for you.

以下是一些适合您的解决方案。

Sample data:

样本数据：

declare @xml xml
set @xml = 
'<EventSpecificData>
  <Keyword>
    <Word>myWord</Word>
    <Occurences>1</Occurences>
    <Context>context</Context>
  </Keyword>
</EventSpecificData>'

Get the first value from node named Word regardless of parents. Use // to do a deep search and use local-name() to match node name.

无论父母如何，从名为Word的节点获取第一个值。使用//进行深度搜索并使用local-name（）匹配节点名称。

declare @Attribute varchar(max)

set @Attribute = 'Word'
select @xml.value('(//*[local-name() = sql:variable("@Attribute")])[1]',  'varchar(max)')

Provide parent node name and attribute in separate variables using local-name() in two levels.

使用两个级别的local-name（）在单独的变量中提供父节点名称和属性。

declare @Node varchar(max)
declare @Attribute varchar(max)

set @Attribute = 'Word'
set @Node = 'Keyword'
select @xml.value('(/EventSpecificData
                    /*[local-name() = sql:variable("@Node")]
                    /*[local-name() = sql:variable("@Attribute")])[1]',  'varchar(max)')

Since the parameter to nodes have to be a string literal it invites to use dynamic sql to solve this. It could look something like this to make it work with your original variable content.

由于节点的参数必须是字符串文字，因此它邀请使用动态sql来解决此问题。它可能看起来像这样使它与原始变量内容一起使用。

set @Attribute = 'Keyword/Word'
declare @SQL nvarchar(max)
set @SQL = 'select @xml.value(''(/EventSpecificData/'+@Attribute+')[1]'', ''varchar(max)'')'
exec sp_executesql @SQL, N'@xml xml', @xml

But you should be aware of that if you use this you are wide open to SQL Injection attacks. Some devious end-user might come up with a attribute string that looks like this:

但是你应该知道，如果使用它，你会对SQL注入攻击持开放态度。一些狡猾的最终用户可能会想出一个如下所示的属性字符串：

set @Attribute = 'Keyword/Word)[1]'', ''varchar(max)'') select @@version --'

Executing the dynamic SQL with that will give you two result sets. The select @@version is just there to show some harmless code but it might be much worse stuff in there.

使用它执行动态SQL将为您提供两个结果集。选择@@版本只是为了显示一些无害的代码，但它可能是更糟糕的东西。

You can use quotename() to prevent the SQL injection attack. It will at least prevent the attempt made by me.

您可以使用quotename（）来防止SQL注入攻击。它至少会阻止我的尝试。

set @Attribute = 'Keyword/Word'
set @SQL = 'select @xml.value('+quotename('(/EventSpecificData/'+@Attribute+')[1]', '''')+', ''varchar(max)'')'
exec sp_executesql @SQL, N'@xml xml', @xml

Is the last version using quotename()safe? Have a look at this article by Erland Sommarskog The Curse and Blessings of Dynamic SQL.

使用quotename（）的最后一个版本是否安全？看看Erland Sommarskog的这篇文章动态SQL的诅咒和祝福。

Quote:

引用：

So with quotename() and quotestring(), do we have as good protection against SQL injection as we have with parameterised commands? Maybe. I don't know of any way to inject SQL that slips through quotename() or quotestring(). Nevertheless, you are interpolating user input into the SQL string, whereas with parameterised commands, you don't.

因此，使用quotename（）和quotestring（），我们是否可以像使用参数化命令一样保护SQL注入？也许。我不知道有任何方法可以通过quotename（）或quotestring（）来填充SQL。然而，您正在将用户输入插入到SQL字符串中，而使用参数化命令则不会。

#2

Try concatenating the string.

尝试连接字符串。

set @value = @xmlPath.value('(/EventSpecificData/' + @attribute + ')[1]', 'varchar(max)')

Updated answer:

更新的答案：

Let's try CASE'ing the operation.

让我们试试CASE的操作。

SELECT @value = CASE @attribute
        WHEN 'word' THEN [word]
        WHEN 'occurrence' THEN [occurrence]
        WHEN 'context' THEN [context]
        END AS [value]
FROM
(
    SELECT x.u.value('(/EventSpecificData/Keyword/Word)[1]', 'varchar(max)') AS [word]
        , x.u.value('(/EventSpecificData/Keyword/Occurrence)[1]', 'varchar(max)') AS [word]
        , x.u.value('(/EventSpecificData/Keyword/Context)[1]', 'varchar(max)') AS [word]
    FROM @xmlPath.nodes('/EventSpecificData') x(u)
) a

#1