-- variables DECLARE @xml XML = ' ' -- use common table expression to shred data ;WITH ShreddedData (StateName, Abbreviation, Capital, Statehood, ID) AS ( SELECT x.node.value('@StateName', 'VARCHAR(255)'), x.node.value('@Abbreviation', 'VARCHAR(255)'), x.node.value('@Capital', 'VARCHAR(255)'), x.node.value('@Statehood', 'VARCHAR(255)'), x.node.value('@ID', 'INT') FROM @xml.nodes('states/s') x(node) ) SELECT * FROM ShreddedData;