您需要使用crossapply 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>'

您的查询：

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

编辑-注解
上面的查询编写得很快，说明了如何处理表（多行）中的xml列。出于性能原因，请不要使用“//customer”，而是使用绝对路径“/arrayofcustomers/customer”//“客户”将遍历整个xml以查找 Customer xml中任何级别的任何节点。

尝试此查询：

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 Amount
FROM [dbo].[AccountDetails]

尝试以下操作：

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)

它应该会给你一个类似这样的输出：

ItemID  Customer Name
   1         Mr Smith
   2         Mr Bloggs