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>

 

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