How do you read XML column in SQL Server 2008?

Try something like this:

SELECT   Cust.value('(ItemId)[1]', 'int') AS 'ItemID',   Cust.value('(Value)[1]', 'Varchar(50)') AS 'Customer Name'FROM   dbo.Sales.CustomerList.nodes('/ArrayOfCustomers/Customer') AS AOC(Cust)

That should give you an output something like this:

ItemID  Customer Name   1         Mr Smith   2         Mr Bloggs

You need to use CROSS APPLY from table to XML column

create table sales (customerlist xml)insert sales select '    <ArrayOfCustomers xmlns:xsd="http://www.w3.org/2001/XMLSchema"                              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">       <Customer>           <ItemId>1</ItemId>           <Value>Mr Smith</Value>       </Customer>       <Customer>          <ItemId>2</ItemId>          <Value>Mr Bloggs</Value>       </Customer>    </ArrayOfCustomers>'

Your query:

SELECT   N.C.value('ItemId[1]', 'int') ItemId,   N.C.value('Value[1]', 'varchar(100)') ValueFROM dbo.SalesCROSS APPLY CustomerList.nodes('//Customer') N(C)

EDIT - note
The query above was written quickly to illustrate working with xml columns in a table (multi-row). For performance reasons, don't use '//Customer' but use an absolute path instead '/ArrayOfCustomers/Customer'. '//Customer' will go through the entire XML to find Customer nodes anywhere in the XML at any level.

Try this query:

SELECT  AccountDetail.value('(/Accounts/Account/AccountNumber)[1]', 'varchar(100)') AS AccountNumber,     AccountDetail.value('(/Accounts/Account/PayeeName)[1]', 'varchar(1000)') AS PayeeName,     AccountDetail.value('(/Accounts/Account/Amount)[1]', 'decimal(20,2)') AS AmountFROM [dbo].[AccountDetails]