How do I return the value of a specific attribute in an XML column for all rows in a SQL Server table?


So I've spent all day looking for good examples of using xpath / xquery as part of a SQL Server query to return values from an XML data column to no avail.

Here's what I'm trying to do. I have the following XML in a XML data column in a table:

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

I'm trying to write a SQL Server query that will return the value of the ID attribute of the Document node. I want to do this for all of the rows in the table, and all the XML data starts in the exact same format.

Any help would be greatly appreciated! I really just need a clean example and I can take it from there!

So that's the whole XML in your table's XML column??

In that case, use something like this:

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

The .value() call basically takes an XPath as its first parameter, and a T-SQL type as its second parameter, and returns that XML value as the given T-SQL datatype to your query.