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!
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)', 'int') AS 'DocumentID' FROM dbo.YourTable
.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.