时间戳

首页 > 解决方案 > 如何使用 value() 方法在没有 WITH XMLNAMESPACES 子句的情况下使用 SQL 获取 XML 节点值？

sql - 如何使用 value() 方法在没有 WITH XMLNAMESPACES 子句的情况下使用 SQL 获取 XML 节点值？

问题描述

这个乍一看似乎相当简单，但需要注意的是 XML 数据必须是包含非 XML 数据字段的 SQL 语句的一部分。

假设以下 XML 存储在名为 XMLData 的字段中：

样品 1

<rtEvent>
  <ctx>
    <LEAD_ID>3933667137</LEAD_ID>
    <SALE_ID>6098929</SALE_ID>
  </ctx>
</rtEvent>

我可以使用 SQL XML 查询的 value() 方法获取 XML 结构中任何节点的值，并将其与返回集中的任何其他非 XML 字段组合在一个语句中：

SELECT XMLData.value('(/rtEvent/ctx/LEAD_ID)[1]', 'nvarchar(max)') LeadId, 
       RecordNo, ProspectName 
FROM SampleData

但是向 XML 结构添加命名空间会增加一些复杂性，并且以前的工作不再是一种选择，因为查询现在必须考虑命名空间属性。

样品 2

<rtEvent>
  <ctx xmlns="urn:xtk:queryDef">
    <LEAD_ID>906320142</LEAD_ID>
    <SALE_ID>6537962</SALE_ID>
  </ctx>
</rtEvent>

如果我只需要检索 LEAD_ID 的值，我相信这些选项会更明显，因为我可以在查询之前使用 WITH XMLNAMESPACES 子句，但是当我需要来自XML 结构作为总查询的一部分，其中将包括记录中的其他字段。并且在查询之前放置 required 子句不会给出我期望的结果。也许我做错了什么。

我将如何从第二个示例中获取 LEAD_ID 值？

这是我尝试过的，结果值都是NULL：

WITH XMLNAMESPACES ('urn:xtk:queryDef' as ns)
SELECT XMLDat.value('rtEvent/ns:ctx/LEAD_ID[1]', 'nvarchar(max)') LeadId, 
       RecordNo, ProspectName 
FROM SampleData

标签： sqlxml

解决方案

您的 XML 具有默认命名空间，但您的根元素<rtEvent>不在其中。此默认命名空间适用于<ctx> 所有嵌套元素：

像这样试试

DECLARE @mockTable TABLE(XMLDat XML);
INSERT INTO @mockTable VALUES
(
N'<rtEvent>
  <ctx xmlns="urn:xtk:queryDef">
    <LEAD_ID>906320142</LEAD_ID>
    <SALE_ID>6537962</SALE_ID>
  </ctx>
</rtEvent>'
);

WITH XMLNAMESPACES ('urn:xtk:queryDef' as ns)
SELECT XMLDat.value('(rtEvent/ns:ctx/ns:LEAD_ID)[1]', 'nvarchar(max)') LeadId
FROM @mockTable t;

一些事情要知道：

  • 你自己的做法确实错过了ns:前面的LEAD_ID
  • 您自己的方法使用[1]after LEAD_ID，但您必须使用(somexpath)[1]来保证单例值。可能有几个<ctx>元素...
  • 该元素<rtEvents>根本没有命名空间

或者，您可以使用如下通配符：

SELECT XMLDat.value('(*:rtEvent/*:ctx/*:LEAD_ID)[1]', 'nvarchar(max)') LeadId
FROM @mockTable t

但是这种方法容易产生含糊不清的错误，一般建议是：尽可能具体。

推荐阅读