Oracle 12c，将SOAP响应存储到变量中或插入到表中

Question

我是PL/SQL的新手，使用SOAP ws。我设法获得了SOAP响应XML，并使用XMLTable从中提取数据，但我得到了奇怪的数据格式。以下是我遇到问题的选择：

select item
from XMLTable(
XMLNamespaces (
default 'urn:DHCPProv',
'http://schemas.xmlsoap.org/soap/envelope/' as "soap",
'http://schemas.xmlsoap.org/soap/encoding/' as "soapenc"
),
'/soap:Envelope/soap:Body/getDhcpForPortResponse/soapenc:Array/item/item'
passing XMLType('<?xml version="1.0" encoding="UTF-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" 
xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
soap:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" 
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<getDhcpForPortResponse
xmlns="urn:DHCPProv">
<soapenc:Array
soapenc:arrayType="soapenc:Array[2]"
xsi:type="soapenc:Array">
<item
soapenc:arrayType="xsd:string[5]"
xsi:type="soapenc:Array">
<item
xsi:type="xsd:string">
qbtp8482tv
</item>
<item
xsi:type="xsd:string">
111.11.111.111
</item>
<item
xsi:type="xsd:string">
bc644ba2501c
</item>
<item
xsi:type="xsd:string">
MF5601T_AMD-NDF
</item>
<item
xsi:type="xsd:string"/>
</item>
<item
soapenc:arrayType="xsd:string[5]"
xsi:type="soapenc:Array">
<item
xsi:type="xsd:string">
qbtp8482tv
</item>
<item
xsi:type="xsd:string">
222.22.222.222
</item>
<item
xsi:type="xsd:string">
704fb8f3e4e1
</item>
<item
xsi:type="xsd:string">
MF5601T_AMD-NDF
</item>
<item
xsi:type="xsd:string"/>
</item>
</soapenc:Array>
</getDhcpForPortResponse>
</soap:Body>
</soap:Envelope>')
columns item varchar2(4000) path '.'
);

我想知道是否有一种方法可以将这种格式存储到变量中，或者以某种方式将其插入表中？谢谢

Answer 1

您的XML在节点值中有换行符和空格。如果你想删除那些你可以做的：

select rtrim(ltrim(item, chr(32)||chr(10)), chr(10)||chr(32)) as item

它给出10行：

ITEM
--------------
qbtp8482tv
111.11.111.111
bc644ba2501c
MF5601T_AMD-NDF
(null)
qbtp8482tv
222.22.222.222
704fb8f3e4e1
MF5601T_AMD-NDF
(null)

您可以使用过滤器排除null：

where rtrim(ltrim(item, chr(32)||chr(10)), chr(10)||chr(32)) is not null;

它给出8行：

ITEM
--------------
qbtp8482tv
111.11.111.111
bc644ba2501c
MF5601T_AMD-NDF
qbtp8482tv
222.22.222.222
704fb8f3e4e1
MF5601T_AMD-NDF

如果您想将这些插入到表中，那么只需将insert into your_table (your_column)从select ...中放入即可

db<gt；小提琴