请求协助编写SQL提取XML中dim_value字段值

我在名为awfmsvalues的表中，有一个名为xml_data的列存储如下XML数据：

<?xml version="1.0" encoding="utf-16"?>
<m>
    <agladdress change_type="2" row_id="31c17cbd-cc84-4cbf-afd4-42ca7c1cd351" last_update="2024-02-23      09:51:52">
        <key>
            <c col="client">EA</c>
            <c col="attribute_id">A4</c>
            <c col="dim_value">TEST123</c>
            <c col="address_type">3</c>
            <c col="sequence_no">1</c>
        </key>
        <c old="" col="e_mail">test@email.co.uk</c>
    </agladdress>
    <acuheader change_type="0" row_id="3ac032da-683e-44bc-9ab6-6f58a70108df" last_update="2024-02-23 09:50:23">
        <key>
            <c col="client">EA</c>
            <c col="apar_id">TEST123</c>
        </key>
    </acuheader>
</m>

尝试用以下SQL提取dim_value的值：

select 
    x.xml_data.value('(/m/agladdress/key/dim_value)[1]', 'VARCHAR(20)') as shredded_dim
from
    (select cast(xml_data as xml) as xml_data from awfmsvalues where client = 'EA')x

执行后返回“failed to create result”错误，请问语法是否正确？如何解决？

问题分析与解决 #

你的SQL核心问题是XPath路径错误：XML中不存在名为dim_value的独立节点，实际存储该值的是<c>节点，通过col="dim_value"属性来标识。

正确SQL写法 #

select 
    x.xml_data.value('(/m/agladdress/key/c[@col="dim_value"])[1]', 'VARCHAR(20)') as shredded_dim
from
    (select cast(xml_data as xml) as xml_data from awfmsvalues where client = 'EA')x

补充说明 #

1. XPath修正逻辑：使用[@col="dim_value"]筛选出key节点下col属性为dim_value的<c>节点，再提取其文本内容。
2. 编码注意事项：XML声明为utf-16，如果xml_data列是VARCHAR类型，转换为XML时可能出现编码冲突，建议确保原列用NVARCHAR存储，避免解析错误。

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