Parsing XML into a Table Structure… possible?

Well let me elaborate a bit.

The use case (which I’m now hating R.K. for bringing up cause I wont let it go now LOL ) where this applies is….

“I want to insert into a temp table the results of a dynamic sql statement”

The caveat being this has to be done WITHOUT knowing the structure or being able to predefine it.

This is where I’m at right now….

DECLARE @receiveTable TABLE(xml_data XML) DECLARE @xml_data XML DECLARE @strSQL NVARCHAR(2000) SET @strSQL = 'SELECT * INTO #tmp1 FROM sysobjects;DECLARE @tbl TABLE(xml_data xml); DECLARE @xml xml;



Set @xml = (Select * from #tmp1 FOR XML AUTO); INSERT INTO @tbl(xml_data) SELECT @xml; SELECT * FROM @tbl'
INSERT INTO @receiveTable

EXEC (@strSQL
)
SET @xml_data = (SELECT * FROM @receiveTable
)
SELECT @xml_data

So how do I now take the XML and parse it back into a table structure format WITHOUT predefining the structure? Any XML gurus out there that are reading this… help would be greatly appreciated!! :)

(Now the question is not whether or not I should or shouldn’t be doing this with the dynamic sql and the xml and my formatting, etc… this is strictly a “Can it be done” question)

I’ll also be updating this and posting the solution or my acceptance that it just can’t be done