Convert XML datatype in SQL 2005 into a relational resultset

Question

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?

Answer 1

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.

Answer 2

You can use the xml data type or OPENXML. Another poster gave an xml data type example, so here is a OPENXML example.

It really depends on the XML schema though. SQL Server may not like your schema very well. If you have any control over the schema then it helps. You may end up having to use a table variable and build the data with multiple calls to OPENXML to query everything.

For example, the XML input could be processed as follows:

DECLARE @idoc int
DECLARE @doc nvarchar(max)

SET @doc = '
<xml>
    <Entry Type="Error" Start="2008-11-19 02:16:00" End="2008-11-20 04:55:00" />
    <Entry Type="Success" Start="2008-11-25 12:45:00" End="2008-11-25 13:01:00" />
</xml>'


EXEC sp_xml_preparedocument @idoc OUTPUT, @xml

SELECT   [EventType]
        ,[EventStart]
        ,[EventEnd]
FROM    OPENXML (@idoc, '//Event',1)
        WITH    ([Type]         varchar(30)
                ,[Start]        varchar(30)
                ,[End]          varchar(30)
)

One of the challenges I faced was parsing XML dates (ISO8601 with the 'T' and possible time zone). I ended up creating a .NET assembly with a single line of code to convert it a datetime that SQL liked.