如何在 Oracle PL/SQL 中解析一个简单的 XML 片段并将其加载到全局临时表中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2203825/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do you parse a simple XML snippet in Oracle PL/SQL and load it in a global temp table?
提问by AlexIEEE
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:
在 SQL Server 中，很容易解析包含用属性构造的简单 XML 片段的 vachar 变量并将其加载到临时表中 - 请参见下面的示例：
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
Is there a simple example that does the equivalent of this in Oracle PL/SQL?
是否有一个简单的示例在 Oracle PL/SQL 中执行此操作？
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.
在 Oracle 中有一个 DBMS_XMLSTORE 包，但它需要使用 ROWSET 和 ROW 元素的特定规范格式的 XML 片段。DBMS_XMLSTORE 似乎不适用于 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.
此外，我不能 100% 确定是否需要创建 XML 片段的 XSD 并将其注册到 Oracle 数据库上，然后才能使用任何其他 PL/SQL XML 工具/查询。
Thanks!
谢谢！
回答by APC
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.
坦率地说，Oracle 的 XML DB 实现有大量令人眼花缭乱的选项，并且并不总是清楚（至少对我而言）在任何给定场景中应用哪一个。在这种特殊情况下，您需要的是XMLTable()，它将 XQuery 转换为一组行。
First we create a table.
首先我们创建一个表。
SQL> create table t23
2 (field01 number
3 , field02 number
4 , field03 char(1)
5 )
6 /
Table created.
SQL>
Then we populate it ...
然后我们填充它......
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>
