如何在 Oracle 中使用命名空间查询 XML? [英] How to query XML with namespaces in Oracle?

问题描述

我需要从包含完整 XML 文档的 XMLType 变量中提取 PLSQL 过程中的数据，具有以下结构(以下简化):

I need to extract data in PLSQL procedure from XMLType variable containing complete XML document, with following structure (below simplified):

<?xml version="1.0" encoding="utf-8"?>
<AA xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://my.domain/cat1/">
  <Element>
    <ID>2</ID>
    <Value>46544</Value>
  <Element>
</AA>

我正在使用 XMLTable 函数，但是使用简单的 /AA/Element XPath 表达式没有获取任何数据:

I'm using XMLTable function, but with simple /AA/Element XPath expression getting no data:

SELECT C1, C2
INTO v_id, v_val
FROM XMLTable('/AA/Element'
                   passing v_MyXML columns
                    C1 number path 'ID',
                    C2 number path 'Value'
                )

不包含以下任何表达式:

Neither with any of below expressions:

'/*.AA/Element'
'declare default element namespace "http://my.domain/cat1/"; /AA/Element'
'declare namespace xsi="http://www.w3.org/2001/XMLSchema-instance"; declare namespace xsd="http://www.w3.org/2001/XMLSchema"; declare default element namespace "http://jpk.mf.gov.pl/wzor/2016/03/09/03094/"; /AA/Element'

我能够提取数据的唯一方法是修改文档/变量并简单地替换

Only way I was able to extract the data was to modify document/variable and simply replace

<AA xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://my.domain/cat1/">

与

<AA>

这不是完美的解决方案，因为我需要修改文档并返回具有适当属性的初始结构.有人可以建议如何修改 XPath 表达式以获取数据吗?或者可能使用任何其他方法忽略来自 AA 元素的命名空间?

It's not the perfect solution as I need to modify the document and return it's initial structure with proper attributes. Could anybody suggest how to modify XPath expression to be able to fetch data? Or maybe use any other method ignoring namespaces from AA element?

推荐答案

@JensErat 已经提供了 XML 背景，所以我不必.相反，您将在下面找到一个如何在 Oracle PL/SQL 中应用所有这些的工作示例.

@JensErat already provided the XML background so I don't have to. Instead below you'll find a working example how to apply all that in Oracle PL/SQL.

您需要使用 XML 命名空间子句="nofollow">xmltable:

You need to use XML namespace clause of xmltable:

XMLNAMESPACES 子句包含一组 XML 命名空间声明.这些声明由计算行的 XQuery 表达式(计算的 XQuery_string)和 XML_table_column 的 PATH 子句中的 XPath 表达式引用，后者计算整个 XMLTable 函数的列.如果要在 COLUMNS 子句的 PATH 表达式中使用限定名称，则需要指定 XMLNAMESPACES 子句.

The XMLNAMESPACES clause contains a set of XML namespace declarations. These declarations are referenced by the XQuery expression (the evaluated XQuery_string), which computes the row, and by the XPath expression in the PATH clause of XML_table_column, which computes the columns for the entire XMLTable function. If you want to use qualified names in the PATH expressions of the COLUMNS clause, then you need to specify the XMLNAMESPACES clause.

您也可以使用 default XML 命名空间子句:

You can also use default XML namespace clause:

xmlnamespaces(default 'http://my.domain/cat1/')

那么你就不必使用命名空间前缀了.

then you don't have to use namespace prefix.

没有默认命名空间的示例

declare
  v_xml constant xmltype := xmltype('<AA xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://my.domain/cat1/">
  <Element>
    <ID>2</ID>
    <Value>46544</Value>
  </Element>
</AA>'
);
 v_id number;
 v_value number;
begin
  select   id,   value_
    into v_id, v_value
  from xmltable(
    xmlnamespaces('http://my.domain/cat1/' as "foo"),
    '/foo:AA/foo:Element' passing v_xml
    columns
    id number path 'foo:ID',
    value_ number path 'foo:Value'
  );

  dbms_output.put_line('(v_id = ' || v_id || ')(v_value = ' || v_value || ')');
end;
/

具有默认命名空间的示例

declare
  v_xml constant xmltype := xmltype('<AA xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://my.domain/cat1/">
  <Element>
    <ID>2</ID>
    <Value>46544</Value>
  </Element>
</AA>'
);
 v_id number;
 v_value number;
begin
  select   id,   value_
    into v_id, v_value
  from xmltable(
    xmlnamespaces(default 'http://my.domain/cat1/'),
    '/AA/Element' passing v_xml
    columns
    id number path 'ID',
    value_ number path 'Value'
  );

  dbms_output.put_line('(v_id = ' || v_id || ')(v_value = ' || v_value || ')');
end;
/

示例运行:

SQL> @so58
(v_id = 2)(v_value = 46544)

PL/SQL procedure successfully completed.

SQL>

这篇关于如何在 Oracle 中使用命名空间查询 XML?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！