oracle - 使用XMLTABLE和xquery从xml中提取数据

Question

我有以下 XML 片段:

<per:Person xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.something.com/2014/11/bla/webservice.xsd"
    xmlns:per="http://www.something.com/2014/11/bla/person">
    <per:Initials>E.C.</per:Initials>
    <per:FirstName>Erik</per:FirstName>
    <per:LastName>Flipsen</per:LastName>
    <per:BirthDate>1980-07-01</per:BirthDate>
    <per:Gender>Male</per:Gender>
</per:Person>

我想从这个 xml 中提取 PL/SQL 中的一些数据。我想使用 XMLTABLE，因为 EXTRACT 和 EXTRACTVALUE 函数已弃用。

我可以使用此查询提取数据:

select pers.Initials,
       pers.Firstname
    into lsInitials,
         lsFirstname
    from
    XMLTABLE ('*:Person' passing pxRequest
    columns Initials           PATH '*:Initials',
            Firstname          PATH '*:FirstName'
    )  pers;

我对命名空间使用通配符，因为我并不真正关心发送方对命名空间使用什么缩写，无论如何我都知道获取数据的确切路径。

通过这段代码，我有两件事让我困惑:

• 根据 http://docs.oracle.com/database/121/SQLRF/functions268.htm#SQLRF06232 的文档PATH 应该是可选的，但是，一旦我从 COLUMNS 部分中删除 PATH，我就不再得到任何结果。

编辑:

我发现，当我删除元素的 namespace 并将其设置为大写时，它就可以工作。因此，列名称似乎需要与 xml 元素名称相匹配才能正常工作。我还没弄清楚如何让它与命名空间 XML 一起工作。

• 文档还指出“对于除 FOR ORDINALITY 列之外的每个结果列，您必须指定列数据类型”，但是，如果没有它，它似乎也可以正常工作。为列和我将数据提取到的变量指定它似乎也有点多余。知道如果不指定数据类型会给我带来麻烦吗？

可运行的代码示例:

SET SERVEROUTPUT ON;
DECLARE

    pxRequest xmltype := xmltype('<per:Person xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                                    xsi:schemaLocation="http://www.something.com/2014/11/bla/webservice.xsd"
                                    xmlns:per="http://www.something.com/2014/11/bla/person">
                                    <per:Initials>E.C.</per:Initials>
                                    <per:FirstName>Erik</per:FirstName>
                                    <per:LastName>Flipsen</per:LastName>
                                    <per:BirthDate>1980-01-01</per:BirthDate>
                                    <per:Gender>Male</per:Gender>
                                </per:Person>');
    lsInitials  varchar2(100);
    lsFirstname varchar2(100);                                


begin

    select pers.Initials,
           pers.Firstname
        into lsInitials,
             lsFirstname
        from
        XMLTABLE ('*:Person' passing pxRequest
        columns Initials           PATH '*:Initials',
                Firstname          PATH '*:FirstName'
        )  pers;


    dbms_output.put_line(lsInitials);
    dbms_output.put_line(lsFirstname);

end;                            

Answer 1

根据你的第一个问题，the documentation you linked今天有关于省略 PATH 的内容:

The optional PATH clause specifies that the portion of the XQuery result that is addressed by XQuery expression string is to be used as the column content.

If you omit PATH, then the XQuery expression column is assumed. For example:

(... COLUMNS xyz)

is equivalent to

XMLTable(... COLUMNS xyz PATH 'XYZ')

You can use different PATH clauses to split the XQuery result into different virtual-table columns.

列xyz 被假定为'XYZ' 的原因是Oracle 默认情况下不区分大小写(默认为全部大写)。如果您将列定义为 "aBcD"，则 PATH 值将被假定为 'aBcD'

<小时/>

关于指定数据类型的第二个问题:如果您要提取的数据始终是文本数据，那么您也许可以不指定数据类型。

但是，如果您开始处理日期、时间戳、 float 等内容，那么您可能会遇到问题。您需要使用 TO_* 函数手动转换它们，或者您可以在列定义中指定它们的数据类型。如果不这样做，Oracle 可以随意隐式转换它，但它认为合适，这可能会产生意想不到的后果。