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

Question

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">
  <Version>1.0</Version>
  <LastModifiedInVersion>1.0</LastModifiedInVersion>
  <Signatures />
  <Controls>
    <Control ID="TB1">
      <Value>Hello world!</Value>
    </Control>
  </Controls>
  <AutoKeys />
</Document>

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!

Answer 1

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

In that case, use something like this:

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

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.