Flatten out an XML Document (Currently using OPENXML)

I am attempting to take a column with an XML datatype and make it available for reporting with as little code as possible. Specifically, we are storing credit report info in a column that has an XML datatype. We are using the OPENXML command to navigate the XML structure:

DECLARE @.idoc int
DECLARE @.doc xml
SET @.doc = 'GET THE XML COLUMN HERE
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc

SELECT *
FROM OPENXML (@.idoc, '/XML_INTERFACE/CREDITREPORT/OBJECTS/DBCOMMCREDITSCORE',2)
WITH (
Rating varchar(10) 'DBRATING',
OverFlow xml '@.mp:xmltext')
EXEC sp_xml_removedocument @.idoc

The result will be a column that shows the Rating field and an Overflow column that shows the XML string.

I can take the above code and put it in a stored procedure to return the values in a flat format. My questions:

Does anyone know how I can use a view to display the information? (Create View cannot have the above statements. Is there a way to create a tabular representation off of the SP result set, etc.)
Does anyone have a better way of taking an XML datatype and making this available for report writers?

Dan O

1. The XQuery "nodes()" function will work for this. It is similar to OPENXML, but it is just used from within a normal SELECT statement, so you could create views on top of it.

Here is an example using the nodes() function on the xml data type.

http://msdn2.microsoft.com/en-us/ms188282.aspx

2. nodes() seems like the way to go, but you could also try pre-shredding the xml into a more convenient relational structure if your XML has a lot of nesting that would require many joins to reassemble.