oracle解析xml完成版第二次修改

其实XML字符串就好像是ORACLE中的外部表，因此Oracle对 解析XML字符串一些规则要求非常严格。XML字符串提供的数据就是一张表，所以Oracle必须提供跟 xml数据一致的列头

示例一

SELECT * FROM XMLTABLE('$B/DEAL_BASIC/USER_DEAL_INFO' PASSING

    XMLTYPE('<?xml version="1.0" encoding="gb2312" ?>&xml') AS B   

    COLUMNS USER_DEAL_ID VARCHAR2(50) PATH    '/USER_DEAL_INFO/USER_DEAL_ID',

    DEAL_INURE_TIME VARCHAR2(50)    PATH    '/USER_DEAL_INFO/DEAL_INURE_TIME',

    DEAL_EXPIRE_TIME VARCHAR2(50)   PATH    '/USER_DEAL_INFO/DEAL_EXPIRE_TIME',

    DEAL_CREATE_TIME VARCHAR2(50)   PATH    '/USER_DEAL_INFO/DEAL_CREATE_TIME');

   

   

   

<DEAL_BASIC>

   <USER_DEAL_INFO>

   <USER_DEAL_ID>1000100001</USER_DEAL_ID>

   <DEAL_INURE_TIME>20081130</DEAL_INURE_TIME>

   <DEAL_EXPIRE_TIME>30000101</DEAL_EXPIRE_TIME>

   <DEAL_CREATE_TIME>20081130</DEAL_CREATE_TIME>

   </USER_DEAL_INFO>     

</DEAL_BASIC>

xml version="1.0" 指定xml的版本

encoding="gb2312" 指定xml字符串的字符编码 

以上 < >中的内容可以省略

  

示例二(单行正常显示)

SELECT * FROM XMLTABLE('$B/ROW' PASSING

    XMLTYPE('<?xml version="1.0" encoding="gb2312" ?>&xml') AS B

    COLUMNS

    TP_ID   NUMBER              PATH        '/ROW/TP_ID',

    TP_NAME VARCHAR2(50)        PATH        '/ROW/TP_NAME',

    TP_PARENTID NUMBER          PATH        '/ROW/TP_PARENTID',

    TP_SNAME VARCHAR2(50)       PATH        '/ROW/TP_SNAME');

      

   

<ROW>

  <TP_ID>112</TP_ID>

  <TP_NAME>ORACLE</TP_NAME>

  <TP_PARENTID>0</TP_PARENTID>

  <TP_SNAME>R</TP_SNAME>

</ROW>

示例三 (单行正常显示，字符不加单引号。数据反写之后 字符自动添加 单引号)

SELECT * FROM XMLTABLE('$B/ROW' PASSING

    XMLTYPE('<?xml version="1.0" encoding="gb2312" ?>&xml') AS B

    COLUMNS

    TP_ID   NUMBER              PATH        '/ROW/TP_ID',

    TP_NAME VARCHAR2(50)        PATH        '/ROW/TP_NAME',

    TP_PARENTID NUMBER          PATH        '/ROW/TP_PARENTID',

    TP_SNAME VARCHAR2(50)       PATH        '/ROW/TP_SNAME');

      

   

<ROW>

  <TP_ID>112</TP_ID>

  <TP_NAME>计算机网络技术</TP_NAME>

  <TP_PARENTID>0</TP_PARENTID>

  <TP_SNAME>R</TP_SNAME> 

</ROW>

--反写语句如下：

insert into XMLTABLE (TP_ID, TP_NAME, TP_PARENTID, TP_SNAME) values (112, '计算机网络技术', 0, 'R');

示例四 (xml文件中字符不能添加单引号  ’ 和 双引号 ")-

SELECT * FROM XMLTABLE('$B/DEAL_BASIC/USER_DEAL_INFO' PASSING

   XMLTYPE(' &XML') AS B

    COLUMNS USER_DEAL_ID NUMBER         PATH    '/USER_DEAL_INFO/ROW_ID',

    DEAL_INURE_TIME VARCHAR2(50)        PATH    '/USER_DEAL_INFO/ROW_TIME',

    DEAL_EXPIRE_TIME VARCHAR2(50)       PATH    '/USER_DEAL_INFO/ROW_NAME');

   

   

   

<DEAL_BASIC>    

   <USER_DEAL_INFO>

   <ROW_ID>1000100001</ROW_ID>

   <ROW_TIME>ORACLE</ROW_TIME>

   <ROW_NAME>30000101</ROW_NAME>

   </USER_DEAL_INFO>

     

   <USER_DEAL_INFO>

   <ROW_ID>1000100001</ROW_ID>

   <ROW_TIME>"ORACLE"</ROW_TIME>

   <ROW_NAME>30000101</ROW_NAME>

   </USER_DEAL_INFO>       

</DEAL_BASIC> 

--反写语句如下：  insert into XMLTABLE (USER_DEAL_ID, DEAL_INURE_TIME, DEAL_EXPIRE_TIME)  values ('1000100001', '"ORACLE"', '30000101');

示例四(多行正常显示)

SELECT * FROM XMLTABLE('$B/ORDERLIST/ORDER' PASSING

    XMLTYPE(' &XML') AS B

    COLUMNS ORDERID    NUMBER     PATH    '/ORDER/ORDERID',

    ORDERNUMBER        NUMBER     PATH    '/ORDER/ORDERNUMBER',

    ORDERPRICEE        NUMBER     PATH    '/ORDER/ORDERPRICE');

<ORDERLIST>

        <ORDER>

        <ORDERID>1</ORDERID>

        <ORDERNUMBER>857544544</ORDERNUMBER>

        <ORDERPRICE>54</ORDERPRICE>

        </ORDER>

       

        <ORDER>

        <ORDERID>2</ORDERID>

        <ORDERNUMBER>858544544</ORDERNUMBER>

        <ORDERPRICE>63</ORDERPRICE>

        </ORDER>

       

        <ORDER>

        <ORDERID>3</ORDERID>

        <ORDERNUMBER>454854555</ORDERNUMBER>

        <ORDERPRICE>781</ORDERPRICE>

        </ORDER>       

</ORDERLIST>