使用XMLQUERY选择并更新Oracle BLOB列

2 回答

• 0

  要从XML中进行选择，您可以使用 ExtractValue(XmlType, XPath) 或 XmlTable 将Xml clob转换为可查询的XML表 . 对于BLOB转换，您应该能够用 XmlType(blob_value, 1) 包装它，然后您可以在其上执行任何与XML相关的功能 .

  SELECT ExtractValue(
            XmlType('<test><node1>value1</node1><node2>value2</node2></test>'), 
                    '/test/node1') as Node1 
  FROM dual;
  

  或使用 XmlTable

  SELECT xt.Node1, xt.Node2
  FROM XmlTable('/test/block'
           PASSING XmlType('<test>
  <block><node1>value1a</node1><node2>value2a</node2></block>
  <block><node1>value1b</node1><node2>value2b</node2></block>
  <block><node1>value1c</node1><node2>value2c</node2></block>
  </test>')
          COLUMNS
          "Node1"     VARCHAR2(20)   PATH 'node1',
          "Node2"     VARCHAR2(20)   PATH 'node2') AS xt;
  

  使用UpdateXml，假设我正在更新的记录在列中包含上述XML：

  UPDATE MyTable SET xml_data =
  UpdateXml(xml_data, '/test/block/node2[text() = "value2b"]/text()', 'value2z')
  WHERE data_id = 1;
  

  以上应该更新 node2 ，其值为 value2b ，现在改为 value2z . 然后返回新XML并将其分配给匹配 data_id = 1 的记录中的 xml_data 列 .

  请注意，在上面的查询中，它使用的是一个已经是XmlType类型的列 . 您正在使用 BLOB . 我会问，是否有理由将其作为 BLOB 而不是 CLOB 或 XmlType ？如果要存储 VARCHAR 类型数据，则应该使用后两种类型之一，如果要存储各种 VARCHAR 数据，则为 CLOB ;如果存储严格的XML数据，则为 XmlType （无论如何都是 CLOB 的更具体类型） .

  如果您使用 BLOB 数据类型，则需要执行大量转换 . 使用 XmlType(blob_data, 1) 可以让你从 BLOB 到 XmlType ，但回去你可能需要使用 UTL_RAW.CAST_TO_RAW(xml_data) . 所以查询将成为：

  UPDATE MyTable SET clob_data =
  UTL_RAW.CAST_TO_RAW(
      UpdateXml(XmlType(clob_data, 1), '/test/block/node2[text() = "value2b"]/text()', 'value2z').GetClobVal()
  )
  WHERE data_id = 1;
  

  这是一个独立的工作示例，显示了上面提到的各种方法：

  DECLARE varchar_data    VARCHAR2(500);
          blob_data       BLOB;
          xml_data        XMLType;
          node1Val        VARCHAR(20);
          node2Val        VARCHAR(20);
  
  BEGIN
      select '<test>
  <group><node1>value1a</node1><node2>value2a</node2></group>
  <group><node1>value1b</node1><node2>value2b</node2></group>
  <group><node1>value1c</node1><node2>value2c</node2></group>
  <group><node1>value1d</node1><node2>value2d</node2></group>
  </test>' into varchar_data from dual;
  
      select UTL_RAW.CAST_TO_RAW(varchar_data) into blob_data from dual;
  
      select XmlType(blob_data, 1) into xml_data from dual;
      dbms_output.put_line(xml_data.getClobVal());
  
      select xt.Node1, xt.Node2
      into node1Val, node2Val
      from XmlTable('/test/group' 
          passing XmlType(blob_data, 1)
          columns Node1     VARCHAR2(20)    path 'node1',
                  Node2     VARCHAR2(20)    path 'node2'
          ) xt
      where xt.Node1 = 'value1c';
      dbms_output.put_line('node1Val = ''' || node1Val || ''', node2Val = ''' || node2Val || ''';'); 
  
      -- Using UpdateXml to update the XML, that will return an XmlType 
      -- so we call GetClobVal() to let CAST_TO_RAW convert to BLOB.
      select UTL_RAW.CAST_TO_RAW(
          UpdateXml(
              XmlType(blob_data, 1), 
              '/test/group/node2[../node1/text() = "value1c"]/text()', 
              'zzzz').GetClobVal()
          ) into blob_data
      from dual; 
  
      select XmlType(blob_data, 1) into xml_data from dual;
      dbms_output.put_line(xml_data.getClobVal());
  
      select xt.Node1, xt.Node2
      into node1Val, node2Val
      from XmlTable('/test/group' 
          passing XmlType(blob_data, 1)
          columns Node1     VARCHAR2(20)    path 'node1',
                  Node2     VARCHAR2(20)    path 'node2'
          ) xt
      where xt.Node1 = 'value1c';
      dbms_output.put_line('node1Val = ''' || node1Val || ''', node2Val = ''' || node2Val || ''';'); 
  
  END;
  

  回复于 2024-04-27T04:53:48+08:00
• 0

  create table testxmlBlob (p blob);
  

  将一些xml文档插入表中 .

  insert into  testxmlBlob values( utl_raw.cast_to_raw('<?xml version="1.0"?>
  <catalog>
     <book id="bk101">
        <author>Gambardella, Matthew</author>
        <title>XML Developer''s Guide</title>
        <genre>Computer</genre>
        <price>44.95</price>
        <publish_date>2000-10-01</publish_date>
        <description>An in-depth look at creating applications 
        with XML.</description>
     </book>
     <book id="bk102">
        <author>Ralls, Kim</author>
        <title>Midnight Rain</title>
        <genre>Fantasy</genre>
        <price>5.95</price>
        <publish_date>2000-12-16</publish_date>
        <description>A former architect battles corporate zombies, 
        an evil sorceress, and her own childhood to become queen 
        of the world.</description>
     </book></catalog>')) ;
  

  查看插入的xml . xmltype(blob,csid -charset enocoding id '0' is default)

  select xmlserialize(document xmltype(p,0)) from  testxmlBlob;
  

  使用xmlquery将 <description> 更改为 <descriptionNew> . 它也可用于更新语句 update testxmlBlob set p = XMLQuery(....) ...

  select XMLQuery('copy $i := $p1
                modify
                (
               for $j in $i/catalog/book/description
               let $newn := <descriptionNew>newDescription</descriptionNew>
               return replace node $j with $newn)
               return $i             
               ' PASSING xmltype(p,0) AS "p1"     
            RETURNING CONTENT) readable
            ,xmlserialize(document XMLQuery('copy $i := $p1
                modify
                (
               for $j in $i/catalog/book/description
               let $newn := <descriptionNew>newDescription</descriptionNew>
               return replace node $j with $newn)
               return $i             
               ' PASSING xmltype(p,0) AS "p1" 
            RETURNING CONTENT) as BLOB) blob_value
            from  testxmlBlob;
  

  Other example how to use xmlquery to update

  回复于 2024-04-27T04:53:48+08:00