ORA-19025:EXTRACTVALUE僅回傳一個節點的值

我可以成功地從只有一個節點的 xml 定義中讀取。但是當我試圖從 xml 定義中讀取多個節點時，我收到以下錯誤 -

ORA-19025: EXTRACTVALUE 僅回傳一個節點的值。

下面是我的xml定義

<form xmlns:fr="http://orbeon.org/oxf/xml/form-runner" fr:data-format-version="4.0.0">
    <applicant>
        <userId>1234</userId>
        <userName>Dan Johnson</userName>
        <userEmail>[email protected]</userEmail>                                            
    </applicant>       
    <background>
        <additionalDuties>Nothing</additionalDuties>
           <grid-7>
            <position>Service desk analyst</position>
            <workplace>Chicago</workplace>
            <date>1234</date>
            <duties>NA</duties>
           </grid-7>
           <grid-7>
             <position>Service desk analyst 1</position>
             <workplace>New york</workplace>
             <date>123123</date>
             <duties>NA</duties>
           </grid-7>
    </background>
</form>

SQL查詢

SELECT extractvalue(xml, '//*/userId/text()') AS "E-number",
       extractvalue(xml, '//*/userName/text()') AS "Name",
       extractvalue(xml, '//*/userEmail/text()') AS "Email",
       extractvalue(xml, '//*/additionalDuties/text()') AS "Responsibilities",
       extractvalue(xml, '//*/position/text()') AS "Position",
       extractvalue(xml, '//*/workplace/text()') AS "Workplace",
       extractvalue(xml, '//*/date/text()') AS "Date",
       extractvalue(xml, '//*/duties/text()') AS "Duties"
  FROM form_data fd
 WHERE form = 'form-name'

uj5u.com熱心網友回復：

您可以使用XMLTABLE：

SELECT x.*
FROM   table_name t
       CROSS JOIN XMLTABLE(
         '/form/background/grid-7'
         PASSING XMLTYPE(xml)
         COLUMNS
           "E-number"         NUMBER        PATH './../../applicant/userId',     
           "Name"             VARCHAR2(150) PATH './../../applicant/userName',
           "Email"            VARCHAR2(150) PATH './../../applicant/userEmail',
           "Responsibilities" VARCHAR2(150) PATH './../additionalDuties',
           "Position"         VARCHAR2(150) PATH 'position',
           "Workplace"        VARCHAR2(150) PATH 'workplace',
           "Date"             NUMBER        PATH 'date',
           "Duties"           VARCHAR2(150) PATH 'duties' 
        ) x

其中，對于樣本資料：

CREATE TABLE table_name (xml) AS
SELECT TO_CLOB('<form xmlns:fr="http://orbeon.org/oxf/xml/form-runner" fr:data-format-version="4.0.0">
    <applicant>
        <userId>1234</userId>
        <userName>Dan Johnson</userName>
        <userEmail>[email protected]</userEmail>                                            
    </applicant>       
    <background>
        <additionalDuties>Nothing</additionalDuties>
        <grid-7>
            <position>Service desk analyst</position>
            <workplace>Chicago</workplace>
            <date>1234</date>
            <duties>NA</duties>
        </grid-7>
        <grid-7>
             <position>Service desk analyst 1</position>
             <workplace>New york</workplace>
             <date>123123</date>
             <duties>NA</duties>
        </grid-7>
    </background>
</form>') FROM DUAL

輸出：

電子號碼	名稱	電子郵件	責任	位置	作業場所	日期	職責
1234	丹·約翰遜	[email protected]	沒有什么	服務臺分析師	芝加哥	1234	不適用
1234	丹·約翰遜	[email protected]	沒有什么	服務臺分析師 1	紐約	123123	不適用

db<>在這里擺弄

uj5u.com熱心網友回復：

您可以使用XMLTABLEOracle 推薦的功能EXTRACTVALUE，而不是不推薦使用的功能，例如

WITH form_data( xml ) AS
(
 SELECT XMLType(
          REPLACE(
                  '<form xmlns:fr="http://orbeon.org/oxf/xml/form-runner" fr:data-format-version="4.0.0">
                         the rest of your XML is embedded here
                   </form>','grid-','grid')
          )
   FROM dual
)
SELECT "E-number", "Name", "Email", "Responsibilities", 
       "Position", "Workplace","Date","Duties"
  FROM form_data,
  XMLTABLE('/form/applicant'
      PASSING xml
      COLUMNS 
             "E-number" VARCHAR2(150) PATH 'userId',
             "Name"     VARCHAR2(150) PATH 'userName',
             "Email"    VARCHAR2(150) PATH 'userEmail'
         ),
  XMLTABLE('/form/background/grid7'
      PASSING xml
      COLUMNS          
             "Responsibilities" VARCHAR2(150) PATH 'duties',
             "Position"         VARCHAR2(150) PATH 'position',
             "Workplace"        VARCHAR2(150) PATH 'workplace',
             "Date"             VARCHAR2(150) PATH 'date',
             "Duties"           VARCHAR2(150) PATH 'duties' 
         )   

Demo

標籤：

上一篇：Maven編譯的Java級別問題

下一篇：如何統計T-SQL中的xml屬性值？