如何在SQL Server表的所有行中返回XML列中特定属性的值？

Question

我已经花了一整天的时间寻找使用xpath / xquery作为SQL Server查询的好例子，以从XML数据列中返回值，但都没有成功。

这是我的目标。我在表格的XML数据列中有以下XML：

<Document ID="156">
  <Version>1.0</Version>
  <LastModifiedInVersion>1.0</LastModifiedInVersion>
  <Signatures />
  <Controls>
    <Control ID="TB1">
      <Value>Hello world!</Value>
    </Control>
  </Controls>
  <AutoKeys />
</Document>

我正在尝试编写一个SQL Server查询，以返回节点的属性值。我想对表中所有行执行此操作，而且所有XML数据都以相同的格式开始。

任何帮助都将不胜感激！我只需要一个清晰的示例，然后就可以自己完成了！

- Xedicon

Answer 1

那么你的表格的XML列中就包含了整个XML吗？如果是这样，可以使用以下内容：

SELECT
   (list of other columns),
   XmlColumn.value('(/Document/@ID)[1]', 'int') AS 'DocumentID'
FROM
   dbo.YourTable

.value()方法基本上以XPath作为第一个参数，以T-SQL类型作为其第二个参数，并将该XML值作为给定的T-SQL数据类型返回到您的查询中。

- marc_s

---

我记得你不能从根元素中获取属性。 - Jeremy Holovacs

@JeremyHolovacs：你确定吗？？？在我的机器上，它在SQL Server 2008 R2（Mgmt Studio）中运行得非常好....试试吧！ - marc_s

其他的可能更大，但目前我只关心获取ID属性的值。非常感谢！我已经接近成功了，只是我的闭括号放错了位置。虽然我对XPath / XQuery很新，但在SQL方面有一些经验，所以这将帮助我入门！ - Xedicon

好的，知道了。我只是记得以前有一些挫折，最近还没有尝试过。 - Jeremy Holovacs

@Xedicon：在这种情况下，SQL Server XQuery 的 .value() 调用有点特殊，你必须确保只选择单个节点（这就是 XPath 表达式末尾的 [1] 的原因）。 - marc_s

Answer 2

如果您的XML以普通字符串的形式保存在数据库中，可以立即转换和查询它。

SELECT CONVERT(XML, [XMLStringColumn]).value('(/Document/@ID)[1]', 'int') 
FROM yourTable

- CommonToast

Answer 3

SELECT xmlcol.value('(//Document/@ID)[1]','int') AS DocID  FROM xmltable

- Mithrandir