Process Element XML and Attribute XML in single node.
DECLARE @T VARCHAR(400) SET @T = '<Log DeviceId="MainDevice001"> <Info UId="100000" Date="05/05/2014"> <Err>Error1</Err> </Info> <Info UId="100001" Date="05/05/2014"> <Err>Error2</Err> </Info> </Log>' DECLARE @Errorlogsxml AS XML SET @Errorlogsxml = Cast(@T AS XML) SELECT TAB1.COL1.value('@UId', 'VARCHAR(100)') AS Uid, X.Deviceid AS Deviceid, TAB1.COL1.value('@Date', 'DATETIME') AS Date, TAB1.COL1.value('(Err)[1]', 'VARCHAR(100)') AS Error FROM (SELECT TAB.COL.value('@DeviceId', 'VARCHAR(100)') AS Deviceid FROM @Errorlogsxml.nodes('/Log') AS Tab(COL)) X CROSS APPLY @Errorlogsxml.nodes('/Log/Info') AS Tab1(COL1)