DECLARE @XML XML = '<root><Books><Book><ID>2</ID><Name>Sql Server Admin</Name>
<Author><Name>Joseph</Name><Age>35</Age></Author></Book></Books></root>' ;

SELECT @XML.query('/root/Books/Book/Author') ;

DECLARE @XML XML = '<root><Books><Book><ID>2</ID><Name>Sql Server Admin</Name>
<Author><Name>Joseph</Name><Age>35</Age></Author></Book></Books></root>' ;

SELECT 
@XML.value('(/root/Books/Book/Author/Name/text())[1]','varchar(100)') AS Name, 
@XML.value('(/root/Books/Book/Author/Age/text())[1]','varchar(100)') AS Age
FOR XML PATH('') ;

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) ;