从Oracle CLOB-XML中提取值，返回NULL

Question

有人能解释一下，如何提取"NamedSubString名称"以及"；值"；变成两列的表格？我的XML数据在CLOB列中。我从下面的代码开始，但它总是返回NULL。

SELECT EXTRACTVALUE(XMLTYPE(clob_column), '/XMLScript/Command/Print/text()') 
FROM table;

<XMLScript Version="2.0">
<Command> 
<Print JobName="2381_4750_20210330_104608">
<Format>\molpesr03datamolpedprojsCelldokentwVorlagenBartender.txt</Format>
<NamedSubString Name="WF_PRODUKTART">
<Value>ÖÄÜöäüß'</Value> 
</NamedSubString> 
<NamedSubString Name="FORMAT"><Value></Value></NamedSubString><NamedSubString Name="IDENTICALCOPIESOFLABEL"><Value></Value></NamedSubString><NamedSubString Name="JOBNAME"><Value></Value></NamedSubString><NamedSubString Name="NAMEDSUBSTRINGS"><Value></Value></NamedSubString><NamedSubString Name="PRINTER"><Value></Value></NamedSubString><NamedSubString Name="PRINTTOFILENAME"><Value></Value></NamedSubString><NamedSubString Name="WF_AUFPOS_ID"><Value>16487298</Value></NamedSubString>
<PrintSetup> 
<IdenticalCopiesOfLabel>1</IdenticalCopiesOfLabel>
<Printer>Zebra ZT410 (300 dpi)</Printer>
<PrintToFileName></PrintToFileName> 
</PrintSetup> 
</Print> 
</Command> 
</XMLScript> 

Thx

Answer 1

您应该能够使用多个CROSS JOIN和XMLTABLE函数提取值，如下所示：

WITH
your_table
AS
(SELECT    EMPTY_CLOB ()
|| '<XMLScript Version="2.0">
<Command> 
<Print JobName="2381_4750_20210330_104608">
<Format>\molpesr03datamolpedprojsCelldokentwVorlagenBartender.txt</Format>
<NamedSubString Name="WF_PRODUKTART">
<Value>ÖÄÜöäüß</Value> 
</NamedSubString> 
<NamedSubString Name="FORMAT"><Value></Value></NamedSubString><NamedSubString Name="IDENTICALCOPIESOFLABEL"><Value></Value></NamedSubString><NamedSubString Name="JOBNAME"><Value></Value></NamedSubString><NamedSubString Name="NAMEDSUBSTRINGS"><Value></Value></NamedSubString><NamedSubString Name="PRINTER"><Value></Value></NamedSubString><NamedSubString Name="PRINTTOFILENAME"><Value></Value></NamedSubString><NamedSubString Name="WF_AUFPOS_ID"><Value>16487298</Value></NamedSubString>
<PrintSetup> 
<IdenticalCopiesOfLabel>1</IdenticalCopiesOfLabel>
<Printer>Zebra ZT410 (300 dpi)</Printer>
<PrintToFileName></PrintToFileName> 
</PrintSetup> 
</Print> 
</Command> 
</XMLScript>'    AS clob_column
FROM DUAL)
SELECT d.*
FROM your_table  yt
CROSS JOIN
XMLTABLE ('/XMLScript/Command/Print'
PASSING xmltype (yt.clob_column)
COLUMNS records XMLTYPE PATH 'NamedSubString') r
CROSS JOIN
XMLTABLE ('NamedSubString'
PASSING r.records
COLUMNS "NAME" PATH '@Name', "VALUE" PATH 'Value') d;

NAME       VALUE
_________________________ ___________
WF_PRODUKTART             ╓─▄÷Σⁿ▀
FORMAT
IDENTICALCOPIESOFLABEL
JOBNAME
NAMEDSUBSTRINGS
PRINTER
PRINTTOFILENAME
WF_AUFPOS_ID              16487298