DECLARE @XML XML;

SELECT @XML =

'<ROOT>

<Customers>

<CustomerId>1111</CustomerId>

<CompanyName>Sean Chai</CompanyName>

<City>NY</City>

</Customers>

<Customers>

<CustomerId>1112</CustomerId>

<CompanyName>Tom Johnston</CompanyName>

<City>LA</City>

</Customers>

<Customers>

<CustomerId>1113</CustomerId>

<CompanyName>Institute of Art</CompanyName>

</Customers>

</ROOT>';

SELECT --R.Node.query('.'),

R.Node.query('.').value('(/Customers/CustomerId/.)[1]','varchar(100)') AS CustomerID,

R.Node.query('.').value('(/Customers/CompanyName/.)[1]','varchar(100)') AS CompanyName

FROM @XML.nodes('/ROOT/Customers') R(Node);

You'll need to use your column name instead of the XML variable, but it should do what you want.