如何在 Oracle PL/SQL 中解析一个简单的 XML 片段并将其加载到全局临时表中?

2021-12-24 00:00:00 xml parsing oracle plsql

在 SQL Server 中，很容易解析包含用属性构造的简单 XML 片段的 vachar 变量并将其加载到临时表中 - 请参见下面的示例:

In SQL Server it is easy to parse a vachar variable that contains a simple XML snippet constructed with attributes and load it into a temp table - see example below:

declare @UpdateXML VARCHAR(8000)

set @UpdateXML='<ArrayOfRecords>
  <Record Field01="130" Field02="1700" Field03="C" />
  <Record Field01="131" Field02="1701" Field03="C" />
  <Record Field01="132" Field02="1702" Field03="C" />
 </ArrayOfRecords>'

DECLARE @hdoc int

EXEC sp_xml_preparedocument @hdoc OUTPUT, @UpdateXML

INSERT 
INTO #tblTemp( 
  [Field01],
  [Field02],
  [Field03]
 )
SELECT * 
FROM OPENXML(@hdoc, '//ArrayOfRecords/Record') 
WITH ( Field01 int,
  Field02 int,
  Field03 char(1)
 )

EXEC sp_xml_removedocument @hdoc

是否有一个简单的例子可以在 Oracle PL/SQL 中做到这一点?

Is there a simple example that does the equivalent of this in Oracle PL/SQL?

在 Oracle 中有一个 DBMS_XMLSTORE 包，但它需要使用 ROWSET 和 ROW 元素的特定规范格式的 XML 片段.DBMS_XMLSTORE 似乎不适用于 XML 属性.

In Oracle there is an DBMS_XMLSTORE package but it wants the XML snippet in a specific canonical format using ROWSET and ROW elements. DBMS_XMLSTORE does not appear to work with XML attributes.

此外，我不确定是否需要为我的 XML 片段创建 XSD 并将其注册到 Oracle 数据库上，然后才能使用任何其他 PL/SQL XML 工具/查询.

Also, I am not 100% sure if I need to create an XSD of my XML snippet and register that on the Oracle database before I can use any of the other PL/SQL XML tools/queries.

谢谢！

推荐答案

坦率地说，Oracle 的 XML DB 实现有许多令人眼花缭乱的选项，而且并不总是清楚(至少对我而言)哪个选项适用于任何给定的场景.在这种特殊情况下，您想要的是 XMLTable()，它将 XQuery 转换为一组行.

Oracle's XML DB implementation has a frankly bewildering number of options, and it is not always clear (at least to me) which one applies in any given scenario. In this particular case the one you want is XMLTable(), which turns an XQuery into a set of rows.

首先我们创建一个表.

SQL> create table t23
  2      (field01 number
  3       , field02 number
  4       , field03 char(1)
  5       )
  6  /

Table created.

SQL>

然后我们填充它...

SQL> declare
  2      x varchar2(2000) := '<ArrayOfRecords>
  3                        <Record Field01="130" Field02="1700" Field03="C" />
  4                        <Record Field01="131" Field02="1701" Field03="C" />
  5                        <Record Field01="132" Field02="1702" Field03="C" />
  6                   </ArrayOfRecords>';
  7  begin
  8      insert into t23
  9      select *
 10      from xmltable
 11          ( '/ArrayOfRecords/Record'
 12             passing xmltype (x)
 13             columns f1 number path '@Field01'
 14                     , f2 number path '@Field02'
 15                     , f3 char(1) path '@Field03'
 16          )
 17      ;
 18  end;
 19  /

PL/SQL procedure successfully completed.

SQL>

最后我们证明它有效......

Finally we prove it worked....

SQL> select * from t23
  2  /

   FIELD01    FIELD02 F
---------- ---------- -
       130       1700 C
       131       1701 C
       132       1702 C

SQL>

相关文章