Using GET method with webservice I have stored below XML format response in a CLOB variable. Now i need to parse it into columns and rows of a table. Due to different format of below XML, am not able to store into table. Could you please suggest how to do that.
XML data:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<p:testQueryResponse xsi:schemaLocation="http://hl7gbo.test.abc.com/v401439 testQueryResponse.xsd" xmlns:p1="http://hl7gbo.test.abc.com/v401439" xmlns:xsi="http://www.w3w.orgs/2001/XMLs" xmlns:p="http://hl7gbo.test.abc.com/v401439">
<header>
<id identifierName="MQTIAQUERYID" extension="bunstBrch"/>
<creationTime value="212303244093450.966"/>
<acknowledgement>
<typeCode code="AA"/>
<targetMessage>
<id identifierName="TID" extension="targetmessage"/>
</targetMessage>
<acknowledgementDetail>
<typeCode/>
<code code="200"/>
<text value="Successfully Completed"/>
</acknowledgementDetail>
</acknowledgement>
</header>
<test> -- rows start from here
<testInfo>
\<id>
\<item identifierName="CCODE" extension="ADP"/> -- CCODE,PCODE &UCODE are column names
\<item identifierName="PCODE" extension="112318"/>
\<item identifierName="PRIMARYCODE" extension="11dDE="/>
\<item identifierName="UCODE" extension="87339048"/>
\</id>
\<code codeSystemName="RarsraN" codeSystem="ADP" code="12448">
\<displayName value="HEV DNA, AN, LEOA"/>
\<translation valueSet="UCODE" codeSystem="DBS" code="8700011348"/>
\<translation codingRationale="P" valueSet="TESTID" codeSystem="DBS" code="11348RXE="/>
\<translation valueSet="TESTID" codeSystem="DBS" code="11348RXE"/>
\<translation valueSet="TESTID" codeSystem="DBS" code="XE11348"/>
\<translation valueSet="ADC" codeSystemName="TESTNAME" codeSystem="TNM" code="435633"/>
\</code>
\<handlingCode>
\<item valueSet="Yes" code="ORDERABLEBYINDICATOR"/>
\<item valueSet="No" code="RINDICATOR"/>
\<item valueSet="AVC Referral" code="PERFTYPE"/>
\<item valueSet="No" code="CLIENTINDICATOR"/>
\<item valueSet="Yes" code="INDFLAG"/>
\<item valueSet="No" code="DEFAULTINDICATOR"/>
\</handlingCode>
\<aliasInfo>
\<aliasLIS>
\<code codeSystemName="CORPORATE" codeSystem="AVS"/>
\<text value="RNA"/>
\</aliasLIS>
\<aliasLIS>
\<code codeSystemName="CORPORATE" codeSystem="AVS"/>
\<text value="HEPATIT"/>
\</aliasLIS>
\</aliasInfo>
\</testInfo>
\<performer>
\<location>
\<organization>
\<code codeSystem="AVS" code="PO"/>
\<name>
\<item>
\<part value="SJC" type="TITLE"/>
\</item>
\</name>
\<addr>
\<item use="H">
\<part value="23458 HWY" type="SAL"/>
\<part value="JUNA" type="CTY"/>
\<part value="ND" type="BTA"/>
\<part value="12345-4364" type="ZIP"/>
\</item>
\</addr>
\</organization>
\</location>
\</performer>
\<IInfo>
\<code codeSystemName="adsCorporate" codeSystem="UTR" code="863520"/>
\</IInfo>
</test>
</p:testQueryResponse>