How to retrieve values in XML into a plsql table type
608473May 5 2011 — edited May 15 2011Hi
I have an XML doc like below which is a Web Service response message.
<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<SOAP-ENV:Body>
<GetOrgInput xmlns="http://www.example.org/ComplianceServices">
<ProgramName>PgmA-PgmB</ProgramName>
<ItemName>800 ADSL</ItemName>
<Orgs>923</Orgs>
<Orgs>1163</Orgs>
<Orgs>1103</Orgs>
</GetOrgInput>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
From the above doc you could see that i have multiple occurrences of "Orgs" element. Usually i am using the .extract and .getSTringVal() to retrieve the values as below.
l_string_variable := p_response.doc.extract('//'||p_name||'/child::text()',p_namespace).getStringVal();
But in this case if i do that, i am getting value as 92311631103. I need to get them into a table type of number. Pls advice me on how to achieve this.
Thanks
PKV