Read data from Excel XML file

Copied from http://stackoverflow.com/questions/6472533/xquery-and-node-ids

 

;WITH XMLNAMESPACES(
    DEFAULT 'urn:schemas-microsoft-com:office:spreadsheet'
    ,'urn:schemas-microsoft-com:office:office' as o
    ,'urn:schemas-microsoft-com:office:excel' as x 
    ,'urn:schemas-microsoft-com:office:spreadsheet' as ss
    ,'http://www.w3.org/TR/REC-html40' as html
)

SELECT 
        t.r.value('../Row[1]/*[1]', 'varchar(50)') 
        ,t.r.value('(*)[1]', 'varchar(50)')
FROM dbo.SSRSReports s
OUTER APPLY s.rdl.nodes('/Workbook/Worksheet[1]/Table/Row[position() > 1]') t(r)
WHERE ID=3