如何在TSQL中读取并过滤XML列中的指定字段？

首先假设你的SerializedOrder字段XML结构类似如下（若实际结构不同，只需调整节点路径即可）：

<Order>
  <ProductType>Physical</ProductType>
  <ISDRCEligible>true</ISDRCEligible>
  <ISDRCApplied>false</ISDRCApplied>
  <Item type="Goods">
    <IsAncillary>false</IsAncillary>
    <IsSameQtyReq>true</IsSameQtyReq>
  </Item>
  <TaxInfo>
    <TaxCode>TAX-US-01</TaxCode>
    <Country>US</Country>
  </TaxInfo>
</Order>

无命名空间XML的查询 #

如果你的XML没有使用命名空间，用以下查询提取目标字段并过滤：

SELECT
  SerializedOrder.value('(/Order/ProductType)[1]', 'NVARCHAR(100)') AS ProductType,
  SerializedOrder.value('(/Order/ISDRCEligible)[1]', 'BIT') AS ISDRCEligible,
  SerializedOrder.value('(/Order/ISDRCApplied)[1]', 'BIT') AS ISDRCApplied,
  SerializedOrder.value('(/Order/Item/@type)[1]', 'NVARCHAR(50)') AS [type],
  SerializedOrder.value('(/Order/Item/IsAncillary)[1]', 'BIT') AS IsAncillary,
  SerializedOrder.value('(/Order/Item/IsSameQtyReq)[1]', 'BIT') AS IsSameQtyReq,
  SerializedOrder.value('(/Order/TaxInfo/TaxCode)[1]', 'NVARCHAR(50)') AS TaxCode,
  SerializedOrder.value('(/Order/TaxInfo/Country)[1]', 'NVARCHAR(2)') AS Country
FROM TransactionOrder
-- 示例过滤条件：按需修改
WHERE
  SerializedOrder.value('(/Order/ProductType)[1]', 'NVARCHAR(100)') = 'Physical'
  AND SerializedOrder.value('(/Order/ISDRCEligible)[1]', 'BIT') = 1;

带命名空间XML的查询 #

如果XML包含命名空间（例如<Order xmlns="http://your-namespace-url">），必须先声明命名空间：

WITH XMLNAMESPACES (DEFAULT 'http://your-namespace-url')
SELECT
  SerializedOrder.value('(/Order/ProductType)[1]', 'NVARCHAR(100)') AS ProductType,
  SerializedOrder.value('(/Order/ISDRCEligible)[1]', 'BIT') AS ISDRCEligible,
  SerializedOrder.value('(/Order/ISDRCApplied)[1]', 'BIT') AS ISDRCApplied,
  SerializedOrder.value('(/Order/Item/@type)[1]', 'NVARCHAR(50)') AS [type],
  SerializedOrder.value('(/Order/Item/IsAncillary)[1]', 'BIT') AS IsAncillary,
  SerializedOrder.value('(/Order/Item/IsSameQtyReq)[1]', 'BIT') AS IsSameQtyReq,
  SerializedOrder.value('(/Order/TaxInfo/TaxCode)[1]', 'NVARCHAR(50)') AS TaxCode,
  SerializedOrder.value('(/Order/TaxInfo/Country)[1]', 'NVARCHAR(2)') AS Country
FROM TransactionOrder
WHERE
  SerializedOrder.value('(/Order/ProductType)[1]', 'NVARCHAR(100)') = 'Physical'
  AND SerializedOrder.value('(/Order/ISDRCEligible)[1]', 'BIT') = 1;

核心注意点 #

• 所有.value()调用必须加[1]索引：SQL Server要求明确返回单个节点值，不指定索引会报错。
• 匹配数据类型：布尔值用BIT，文本用NVARCHAR(n)，确保和XML中值的类型对应。
• 处理重复节点：如果XML有多个重复节点（比如多个<Item>），用CROSS APPLY配合.nodes()拆分提取：

SELECT
  SerializedOrder.value('(/Order/ProductType)[1]', 'NVARCHAR(100)') AS ProductType,
  ItemNode.value('@type', 'NVARCHAR(50)') AS [type],
  ItemNode.value('IsAncillary[1]', 'BIT') AS IsAncillary
FROM TransactionOrder
CROSS APPLY SerializedOrder.nodes('/Order/Item') AS Items(ItemNode)
WHERE
  SerializedOrder.value('(/Order/ISDRCEligible)[1]', 'BIT') = 1;

内容的提问来源于stack exchange，提问作者Chaitanya Reddy