其实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> |