Mysql中怎么取得某个结点上所有内容

Mysql中如何取得某个结点下所有内容

RPT_XML结构：

<ROOT>

   <NEWS>

      <ROW1>

          <COL1>1</COL1>

          <COL2>2</COL3>

          <COL3>3</COL3>

      </ROW1>

      <ROW2>

          <COL1>4</COL1>

          <COL2>5</COL3>

          <COL3>6</COL3>

      </ROW2>

   </NEWS>

</ROOT>

需要取得<NEWS>结点下的所有内容，结果如下：

<NEWS>

      <ROW1>

          <COL1>1</COL1>

          <COL2>2</COL3>

          <COL3>3</COL3>

      </ROW1>

      <ROW2>

          <COL1>4</COL1>

          <COL2>5</COL3>

          <COL3>6</COL3>

      </ROW2>

</NEWS>

取得整个XML的内容的SQL是：SELECT T.RPT_XML FROM REPORT

可以通过存储过程来实现

CALL PROC_SAMPLE(#ID#,#DELFLG#) 调用存储过程

PROC_SAMPLE.sql文件内容

DROP PROCEDURE IF EXISTS `PROC_SAMPLE`;

CREATE PROCEDURE `PROC_SAMPLE`(IN `ID` CHAR(6),IN `DELFLG` CHAR(1)) BEGIN

#Routine body goes here…

DECLARE xmlStr TEXT;

DECLARE done INT;

DECLARE cur_string BLOB;

DECLARE cur_1 CURSOR FOR

     SELECT

       I.RPT_XML

     FROM

       RPT_TABLE I

     WHERE

       I.ID = ID

       AND I.DEL_FLG = DELFLG

DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END;

OPEN cur_1;

REPEAT F

   ETCH cur_1 INTO xmlStr;

   SELECT SUBSTR(xmlStr, INSTR(xmlStr,”<ROOT>”),INSTR(xmlStr,”</ROOT>”)-INSTR(xmlStr,”</ROOT>”))

   INTO xmlStr;

   SET cur_string = substr(xmlStr, LENGTH(‘<ROOT>’) + 1);

   SELECT cur_string;

UNTIL done END REPEAT;

CLOSE cur_1;

END;