如何从SQL Server中的XML元素获取特定的属性

I have something like the following XML in a column of a table:

我在表的一列中有如下XML:

<?xml version="1.0" encoding="utf-8"?>
<container>
  <param name="paramA" value="valueA" />
  <param name="paramB" value="valueB" />
  ...
</container>

I am trying to get the valueB part out of the XML via TSQL

我试图通过TSQL从XML中获取valueeb部分。

So far I am getting the right node, but now I can not figure out how to get the attribute.

到目前为止，我得到的是正确的节点，但是现在我不知道如何获得属性。

select xmlCol.query('/container/param[@name="paramB"]') from LogTable

I figure I could just add /@value to the end, but then SQL tells me attributes have to be part of a node. I can find a lot of examples for selecting the child nodes attributes, but nothing on the sibling atributes (if that is the right term).

我认为我可以在结尾添加/@value，但是SQL告诉我属性必须是节点的一部分。我可以找到许多选择子节点属性的示例，但是在兄弟姐妹节点上没有(如果这是正确的术语)。

Any help would be appreciated.

如有任何帮助，我们将不胜感激。

3 个解决方案

#1

Try using the .value function instead of .query:

尝试使用.value函数而不是.query:

SELECT 
  xmlCol.value('(/container/param[@name="paramB"]/@value)[1]', 'varchar(50)') 
FROM  
  LogTable

The XPath expression could potentially return a list of nodes, therefore you need to add a [1] to that potential list to tell SQL Server to use the first of those entries (and yes - that list is 1-based - not 0-based). As second parameter, you need to specify what type the value should be converted to - just guessing here.

XPath表达式可能会返回一个节点列表，因此需要向该潜在列表添加一个[1]，以告诉SQL Server使用其中的第一个条目(是的，该列表是基于1的，而不是基于0的)。作为第二个参数，您需要指定值应该转换为什么类型——请在这里猜测。

Marc

马克

#2

Depending on the the actual structure of your xml, it may be useful to put a view over it to make it easier to consume using 'regular' sql eg

根据您的xml的实际结构，在其上放置一个视图以使使用“常规”sql eg更容易使用它可能是有用的

CREATE VIEW vwLogTable
AS
SELECT
    c.p.value('@name', 'varchar(10)') name,
    c.p.value('@value', 'varchar(10)') value
FROM
    LogTable
    CROSS APPLY x.nodes('/container/param') c(p)
GO


-- now you can get all values for paramB as...
SELECT value FROM vwLogTable WHERE name = 'paramB'

#3

$doc/param[@value = "valueB"]/fn:data(@value)
Assuming $doc has the Xml.

$doc/param[@value = "valueB"]/fn:data(@value)假设$doc拥有Xml。

#1