在xml里有两个解析xml的函数extract和extractVALUE,这两个函数可以带两个参数,也可以带上个参数,第三个参数是命令空间,对于namespace_string,刚开始我也很疑惑,然后去网上找了很久的资料也没弄明白,因为没有范例,最后在自己的尝试下解决了,
extract官网API如下:
extract官网API如下:
样例xml报文如下:
User1
45df847e-c02e-64d4-f6a1-76a9c8f2c537
Business Solution - Business Broadband
Yes
FB000120170119181518436
2017011914381897
Non-Residential CA Customised PIR
N
BBNC1000001230
NEUC
Mrs
rrrrrr
rrrrrr
44444444
44444444
www@qq.com.cn
Mrs
rrrrrr
rrrrrr
44444444
44444444
www@qq.com.cn
2017-02-04
09:00-13:00
Gigabit Ethernet UTP (1Gbps) over GPON
2
IEEE802.1q
250
250
A
FB0001-BzHighBB_Ded-EUC-01
D
1022
50
50
B
FB0001-NR-SmartUC_Fixed-EUC-01
A
1049
5
5
直接获取节点或者节点值样例sql如下:
select extract(xmltype('<?xml version="1.0" encoding="utf-8"?>
User1
45df847e-c02e-64d4-f6a1-76a9c8f2c537
Business Solution - Business Broadband
Yes
FB000120170119181518436
2017011914381897
'),
'/soapenv:Envelope/soapenv:Body/euc:EUCRevisionNewOrderRequest/body',
'xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:euc="http://platform.nucleusconnect.com/wsdl/EUCServices"')
from dual a
select extractVALUE(xmltype('<?xml version="1.0" encoding="utf-8"?>
User1
45df847e-c02e-64d4-f6a1-76a9c8f2c537
Business Solution - Business Broadband
Yes
FB000120170119181518436
2017011914381897
'),
'/soapenv:Envelope/soapenv:Body/euc:EUCRevisionNewOrderRequest/body/SalesOrderId',
'xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:euc="http://platform.nucleusconnect.com/wsdl/EUCServices"')
from dual a通过xmltable转换为表格sql样例如下:
SELECT *
FROM XMLTABLE(XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS
"soapenv1",
'http://platform.nucleusconnect.com/wsdl/EUCServices' AS
"euc1"),
'$B/soapenv1:Envelope/soapenv1:Body/euc1:EUCRevisionNewOrderRequest/body'
PASSING
(select xmltype('<?xml version="1.0" encoding="utf-8"?>
User1
45df847e-c02e-64d4-f6a1-76a9c8f2c537
Business Solution - Business Broadband
Yes
FB000120170119181518436
2017011914381897
')
from dual a ) AS B COLUMNS SalesOrderId
VARCHAR2(128) PATH '/body/SalesOrderId')