Sql – Convert XML datatype in SQL 2005 into a relational resultset


I have a table in a SQL 2005 database that contains a column defined as a xml datatype. I'm trying to write stored proc that queries the xml and returns a resultset from a select statement. I've seen examples of returning scalar values or xml but not how to return a resultset.

Am I going to have to use openxml or is there another solution?

Best Solution

The xml data type has its own set of methods that you can use to deal with the data in the column. In this case you'd probably use something like this:

select xmlColumnName.value('XpathExpression', 'outputSqlType') from dataTable

For example,

select xmlColumnName.value('/root/node[@name="myname"]', 'varchar(60)')

The methods are query(), value(), exist(), modify() and nodes(), and you can read more about them in Books Online.