我正在尝试获取数据类型保存在表列中的数据<XMLTYPE>。我必须使用过程/查询获取所有带有值的字段名称

Question

    <record>
      <field>
        <fieldName>employee id</fieldName>
        <fieldValue>2000001</fieldValue>
      </field>
      <field>
        <fieldName>employee name</fieldName>
        <fieldValue>pankaj kumar</fieldValue>
      </field>
    </record>

提取的数据应该是这样：

员工ID |员工名称
2000001 |pankaj kumar

Answer 1

CREATE TABLE houses(
  HOUSE_ID NUMBER(4),
  house_add XMLTYPE,
  HOUSE_NAME VARCHAR2(35)
  );

将值插入表;

 INSERT INTO houses VALUES 
   (100, XMLType('<house whNo="100"> 
               <Building>Owned</Building>
               </house>'), 'housename1');

fech： -

SELECT house_add  FROM HOUSES W;

o/p：- <house whNo="100"> <BUILDING>OWNED</BUILDING> </house>

获取建筑细节： -

SELECT  
  w.house_add.extract('/house/Building/text()').getStringVal()     "Building"
  FROM HOUSES W;

o/p： - 拥有

对于您的情况： -

WITH T AS
(select xmltype('<?xml version = "1.0"?>
  <record>
      <FIELD>
       <Field Name="employee id"  fieldValue="2000001"/>
      </FIELD>
      <FIELD>
       <Field Name="employee name"  fieldValue="pankajkumar"/>
      </FIELD>
    </record>
') XML FROM DUAL
)
SELECT  Y.NAME, Y.VALUE
FROM T     ,
                       XMLTABLE('/record/FIELD/Field'
              PASSING T.XML
               COLUMNS NAME VARCHAR2(20) PATH '@Name',
                 VALUE VARCHAR2(20) PATH '@fieldValue'  
               )Y

输出： -

NAme             Value
employee id      2000001
employee name   pankajkumar

这将帮助您！