-- variables
DECLARE @xml XML = '
<states>
  <s>
    <name>Alabama</name>
    <code>AL</code>
  </s>
  <s>
    <name>Alaska</name>
    <code>AK</code>
  </s>
  <s>
    <name>American Samoa</name>
    <code>AS</code>
  </s>
  <s>
    <name>Arizona</name>
    <code>AZ</code>
  </s>
  <s>
    <name>Arkansas</name>
    <code>AR</code>
  </s>
</states>'

-- use common table expression to shred data
;WITH ShreddedData (name, code) AS
(
SELECT   x.node.value('name[1]', 'VARCHAR(255)'),
         x.node.value('code[1]', 'VARCHAR(255)')
FROM     @xml.nodes('states/s') x(node)
)
SELECT * FROM ShreddedData;