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

问题描述

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

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表达式引用，XML_table_column的PATH子句为整个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屋！