如何在SQL Server 2008中解析XML？附示例XML数据

针对你提供的XML结构，SQL Server 2008可以通过内置的XML数据类型方法（value()、nodes()）结合XQuery来轻松解析，下面是具体的实现步骤和代码示例：

1. 先把XML数据转为XML类型变量（或表字段） #

首先我们需要把你的XML数据存储到XML类型的变量中（如果是已经存在表的XML字段里，可以跳过这一步直接用表字段）：

DECLARE @xmlData XML = N'<PW_main_Root_> 
<Trigger_data_new_ID>30</Trigger_data_new_ID> 
<Trigger_Event> 
<OperatorID>30</OperatorID> 
<MainData>516274981</MainData> 
<Variables> 
<Variable VarID="8040" Value="00000000"/> 
<Variable VarID="8140" Value="50000000"/> 
<Variable VarID="8240" Value="00000000"/> 
<Variable VarID="8340" Value="00000000"/> 
<Variable VarID="8440" Value="00000000"/> 
<Variable VarID="8540" Value="00000000"/> 
</Variables> 
</Trigger_Event> 
</PW_main_Root_>'

2. 解析单值节点（顶层和Trigger_Event内的单个数据） #

对于XML中唯一的节点（比如Trigger_data_new_ID、OperatorID），我们用value()方法提取数据，注意XPath路径要准确，并且加上[1]确保返回单一值（SQL Server要求value()必须返回单个节点）：

-- 提取顶层和Trigger_Event中的单值数据
SELECT
    @xmlData.value('(/PW_main_Root_/Trigger_data_new_ID)[1]', 'INT') AS Trigger_data_new_ID,
    @xmlData.value('(/PW_main_Root_/Trigger_Event/OperatorID)[1]', 'INT') AS OperatorID,
    @xmlData.value('(/PW_main_Root_/Trigger_Event/MainData)[1]', 'BIGINT') AS MainData

3. 解析重复节点（Variables下的多个Variable） #

如果XML中有重复的子节点（比如Variables下的多个Variable），我们需要用nodes()方法把这些节点拆分成多行，再结合value()提取属性值：

-- 拆分并提取所有Variable节点的属性
SELECT
    var.value('@VarID', 'INT') AS VarID,
    var.value('@Value', 'VARCHAR(20)') AS Value
FROM @xmlData.nodes('/PW_main_Root_/Trigger_Event/Variables/Variable') AS T(var)

4. 如果XML存储在表字段中的写法 #

如果你的XML数据已经存在某个表的XML类型字段里（比如表名为YourTable，字段名为XmlColumn），可以用CROSS APPLY来关联拆分的节点：

SELECT
    XmlColumn.value('(/PW_main_Root_/Trigger_data_new_ID)[1]', 'INT') AS Trigger_data_new_ID,
    XmlColumn.value('(/PW_main_Root_/Trigger_Event/OperatorID)[1]', 'INT') AS OperatorID,
    XmlColumn.value('(/PW_main_Root_/Trigger_Event/MainData)[1]', 'BIGINT') AS MainData,
    var.value('@VarID', 'INT') AS VarID,
    var.value('@Value', 'VARCHAR(20)') AS Value
FROM YourTable
CROSS APPLY XmlColumn.nodes('/PW_main_Root_/Trigger_Event/Variables/Variable') AS T(var)

一些注意事项 #

• 选择合适的数据类型：比如MainData是较大的数字，用BIGINT避免溢出；Value是固定长度的字符串，用VARCHAR(20)匹配你的数据格式
• XPath路径要准确：如果节点层级变化，需要对应调整路径
• 如果XML包含命名空间，需要先声明命名空间再解析（你的示例XML没有命名空间，所以无需处理）

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