i assumed that you have a table which is having xml column.

DECLARE @xml TABLE (id INT IDENTITY(1,1), xmldoc XML)

INSERT INTO @xml

SELECT '<?xml version="1.0"?>

<CC>

<Type>1</Type>

<Exp>1112</Exp>

<Number>123456</Number>

<Name><![CDATA[RobRobertson]]></Name>

<Amt>18.06</Amt>

<Auth>33875</Auth>

</CC>'

UNION ALL

SELECT '<?xml version="1.0"?>

<CC>

<Type>2</Type>

<Exp>1113</Exp>

<Number>123456</Number>

<Name><![CDATA[RobRobertson000]]></Name>

<Amt>182.06</Amt>

<Auth>3387225</Auth>

</CC>'

UNION ALL

SELECT '<?xml version="1.0"?>

<CC>

<Type>3</Type>

<Exp>1114</Exp>

<Number>123456</Number>

<Name><![CDATA[RobRobertson001]]></Name>

<Amt>1812132.06</Amt>

<Auth>33875</Auth>

</CC>'

SELECT a.id, a.xmldoc

,x.l.value('Type[1]','INT') AS [Type]

,x.l.value('Exp[1]','INT') AS [Exp]

,x.l.value('Name[1]','VARCHAR(30)') AS [Name]

,x.l.value('Amt[1]','DECIMAL(18,2)') AS [Amt]

,x.l.value('Auth[1]','INT') AS [Auth]

FROM @xml a

CROSS APPLY xmldoc.nodes('CC') x(l)

hope it helps.