Oracle resolves the second modification of the XML completion version

In fact, the XML string is like an external table in Oracle, so Oracle has some rules for parsing XML strings that are very demanding. The data provided by the XML string is a table, so Oracle must provide a column header that is consistent with the XML data

Example One

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 () PATH '/user_deal_info/user_deal_id ', Deal_inure_time VARCHAR2 () PATH '/user_deal_info/deal_inure_time ', Deal_expire_time VARCHAR2 () PATH '/user_deal_info/deal_expire_time ', Deal_create_time VARCHAR2 (() 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" Specifying XML version of encoding= "gb2312" Specifying XML character encoding of a string above < > You can omit the contents of the

Example two (single-line normal display)

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 () PATH '/row/tp_name ', Tp_parentid number PATH '/row/tp_parentid ', Tp_sname VARCHAR2 (() 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>

Example three (single-line normal display, characters without single quotation marks.) character automatically adds single quotation mark after data write-back

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 () PATH '/row/tp_name ', Tp_parentid number PATH '/row/tp_parentid ', Tp_sname VARCHAR2 (() PATH '/row/tp_sname '); <ROW> <TP_ID>112</TP_ID> <TP_NAME> Computer network Technology </TP_NAME> <TP_PARENTID>0</TP_PARENTID> <TP_SNAME>R</TP_SNAME> </ROW> --The anti-write statements are as follows: Insert into XMLTABLE (tp_id, Tp_name, Tp_parentid, Tp_sname) VALUES (112, ' computer network technology ', 0, ' R ');

Example four (characters in an XML file cannot be added single quotes ' and double quotes ")-

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 () PATH '/user_deal_info/row_time ', Deal_expire_time VARCHAR2 (() 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> --The anti-write statements are as follows: INSERT into XMLTABLE (user_deal_id, Deal_inure_time, Deal_expire_time) VALUES (' 1000100001 ', ' "ORACLE" ', ' 3000 0101 ');

Example four (multi-line normal display)

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 resolves the second modification of the XML completion version