There are a couple of ways to do this.

Here are some samples. Try these, see if they get you started in the right direction.

DECLARE @XML XML = '<root><Books><Book><ID>2</ID><Name>Sql Server Admin</Name>

<Author><Name>Joseph</Name><Age>35</Age><Blah>Whatever</Blah></Author></Book></Books></root>' ;

-- Example 1

SELECT

@XML.value('(/root/Books/Book/Author/*/text())[1]','varchar(100)') AS Col1,

@XML.value('(/root/Books/Book/Author/*/text())[2]','varchar(100)') AS Col2

FOR XML PATH('') ;

-- Example 2

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS Row,

x.y.value('local-name(.)', 'VARCHAR(50)') AS ColName,

x.y.value('.', 'VARCHAR(50)') AS ColValue

FROM @XML.nodes('/root/Books/Book/Author/*') x (y) ;

On the second one, you can use a dynamic pivot to turn that into named columns and rows. There's a sample of how to do a dynamic pivot here: http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx. There are other examples around - Bing/Google "tsql dynamic pivot" and you'll find plenty of articles and examples around.

Example 2 will pull any number of sub-values from within the Author node and get the tag name and value for each.