We have a complex XML schema as a web service response.
The problem: How to extract data from it.
Our best results at using XMLTABLE give us empty rows.
Please help us untangle this!
I have checked case, paths .. there is something I am missing.
The XML, below (a web service response), is stored as CLOB, then cast as XMLTYPE in view XML_V:
create view xml_v as
select id, XMLPARSE(DOCUMENT response WELLFORMED) as xml_doc
from ws_response;
where ws_response is ( id NUMBER, ws_response CLOB);
This version (we have tried many) of our query returns all NULLs.
Please help!
Is this the best approach?
What are we missing - syntax, path?
Goal: Extract data from the XML and store in a table, the table being the one column per extracted values.
This is sample XML data - the actual web service response follow the same general structure but may have more or fewer elements - for example, they may have i
SELECT xml_v.id, test_detail.* --- list all of them
FROM xml_v,
XMLTABLE(
XMLNAMESPACES('[http://hl7gbo.test.qdx.com/v201608](http://hl7gbo.test.qdx.com/v201608)' as "p",
'[http://hl7gbo.common.qdx.com/v201608](http://hl7gbo.common.qdx.com/v201608)' as "p1",
'[http://www.w3.org/2001/XMLSchema-instance](http://www.w3.org/2001/XMLSchema-instance)' as "xsi",
DEFAULT '[http://hl7gbo.test.qdx.com/v201608](http://hl7gbo.test.qdx.com/v201608)'),
'//test'
PASSING xml\_v.xml\_doc
COLUMNS
LOCAL\_CODE VARCHAR2(800) path '/testInfo/id/item\[@identifierName ="PRIMARYORDERCODE"\]/@extention',
SITE\_CODE VARCHAR2(20) path '/testInfo/id/item\[@identifierName ="COMPENDIUMCODE"\]/@extention', --,
PRIMARY\_QLS\_MNEMONIC VARCHAR2(800) path '/testInfo/id/item\[@identifierName ="PRIMARYORDERCODE"\]/@extention',
UNIT\_CODE VARCHAR2(50) path '/testInfo/id/item\[@identifierName ="UNITCODE"\]/@extention',
TEST\_NAME VARCHAR2(200) path '/testInfo/code/displayName/@value',
NATIONAL\_TEST\_CODE VARCHAR2(50) path '/testInfo/code/translation\[@valueSet="NTC" and [@code](https://forums.oracle.com/ords/apexds/user/code)System="QDI" and [@code](https://forums.oracle.com/ords/apexds/user/code)SystemName="Quest Diagnostics Inc"\]',
TEST\_METHOD VARCHAR2(2000) path '/medicalInformation/clinicalInformation/code\[@code="METHODOLOGY"\]/text/@value', -- ???
TEST\_METHOD\_LIS VARCHAR2(2000) path '/medicalInformation/clinicalInformation/code\[@code="METHODOLOGY-LIS"\]/text/@value', -- ???
INTENDED\_USE VARCHAR2(2000) path '/medical
Information/clinicalInformation/code[@code="CLINICALSIGNIFICANCE"]/text/@value', -- ?
INTENDED\_USE\_LIS VARCHAR2(2000) path '/medicalInformation/clinicalInformation/code\[@code="CLINICALSIGNIFICANCE-LIS"\]/text/@value', --?
PREFERRED\_SAMPLE\_TYPE path '/specimenHandling/annotations/annotation/code\[@code="PREFERREDSPECIMEN"\]/text/@value',
PREFERRED\_SAMPLE\_TYPE\_LIS path '/specimenHand
ling/annotations/annotation/code[@code="PREFERREDSPECIMEN-LIS"]/text/@value'
) test\_detail;
This returns NULL column values:
For a schema this comple we a guessing at these paths.
So far, all NULL rows returned.
All suggestions and direction appreciated.
Thank you - Karen
Sample XML:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<p:test xsi:schemaLocation="http://hl7gbo.test.qdx.com/v201608 test.xsd" xmlns:p1="http://hl7gbo.common.qdx.com/v201608" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://hl7gbo.test.qdx.com/v201608">
\<header>
\<id identifierName="QQQQQUERYID"/>
\<creationTime value="20200319223850.911"/>
\<acknowledgement>
\<typeCode code="AA"/>
\<targetMessage>
\<id identifierName="TARGETID" extension="targetmessage"/>
\</targetMessage>
<acknowledgementDetail>
\<typeCode code="I"/>
\<code code="200"/>
\<text value="Successfully Completed"/>
\</acknowledgementDetail>
\</acknowledgement>
\</header>
\<test>
\<testInfo>
\<id>
\<item identifierName="COMPENDIUMCODE" extension="ABC"/>
\<item identifierName="PRIMARYORDERCODE" extension="21382"/>
\<item identifierName="UNITCODE" extension="21382"/>
\</id>
\<code codeSystemName="Anywhereville" codeSystem="ABC" code="21382">
\<displayName value="CA 125 \[29256X\]"/>
\<translation valueSet="UNITCODE" codeSystem="ABC" code="21382"/>
\<translation valueSet="UNITCODE" codeSystem="DBS" code="8707529256"/>
\<translation codingRationale="P" valueSet="TESTID" codeSystem="DBS" code="29256RAMD="/>
\<translation valueSet="NTC" codeSystemName="My Company Inc" codeSystem="QDI" code="29256"/>
\</code>
\<statusCode code="Active"/>
\<handlingCode>
\<item valueSet="No" code="PROFILEINDICATOR"/>
\<item valueSet="MyCo Referral" code="PERFLABTYPE"/>
\<item valueSet="No" code="PAICINDICATOR"/>
\<item valueSet="Yes" code="PUBLISHINDOSMANUAL"/>
\<item valueSet="Yes" code="NATIONALCOMPENDIUMTEST"/>
\<item valueSet="S" code="NATIONALCOMPENDIUMTESTMATCHTYPE"/>
\</handlingCode>
\<aliasInfo>
\<aliasLIS>
\<code codeSystemName="Anywhereville" codeSystem="ABC"/>
\<text value="CA 125"/>
\</aliasLIS>
\<aliasLIS>
\<code codeSystemName="Anywhereville" codeSystem="ABC"/>
\<text value="CANCER ANTIGEN 125"/>
\</aliasLIS>
\<aliasLIS>
\<code codeSystemName="Anywhereville" codeSystem="ABC"/>
\<text value="Carbohydrate Antigen 125"/>
\</aliasLIS>
\<aliasLIS>
\<code codeSystemName="Anywhereville" codeSystem="ABC"/>
\<text value="OC125"/>
\</aliasLIS>
\<aliasLIS>
\<code codeSystemName="Anywhereville" codeSystem="ABC"/>
\<text value="Ovarian Tumor Marker"/>
\</aliasLIS>
\</aliasInfo>
\<dOSCatalogName>
\<text value="CA 125 \[29256X\]"/>
\</dOSCatalogName>
\<reportingTitle>
\<title value="CA 125 \[29256X\]"/>
\</reportingTitle>
\</testInfo>
\<performer>
\<location>
\<organization>
\<code codeSystem="ABC" code="AMBC/>
\<name>
\<item>
\<part value="My Company Institute" type="TITLE"/>
\</item>
\</name>
\<handlingCode>
\<item valueSet="No" code="ABCINDFLAG"/>
\</handlingCode>
\<addr>
\<item use="H">
\<part value="1234 Anywhere Drive" type="SAL"/>
\<part value="Anywhereville" type="CTY"/>
\<part value="MA" type="STA"/>
\<part value="11111" type="ZIP"/>
\</item>
\</addr>
\</organization>
\</location>
\</performer>
\<origin>
\<location>
\<organization>
\<code code="SKB"/>
\<name>
\<item>
\<part value="Atlantis" type="TITLE"/>
\</item>
\</name>
\<handlingCode>
\<item valueSet="Yes" code="QQQINDFLAG"/>
\<item valueSet="No" code="QQQDEFAULTTESTFLAG"/>
\<item valueSet="Yes" code="QQQDOWNLOADEDTESTFLAG"/>
\</handlingCode>
\</organization>
\</location>
\<location>
\<organization>
\<code code="STL"/>
\<name>
\<item>
\<part value="Lorax" type="TITLE"/>
\</item>
\</name>
\<handlingCode>
\<item valueSet="Yes" code="QQQINDFLAG"/>
\<item valueSet="No" code="QQQDEFAULTTESTFLAG"/>
\<item valueSet="Yes" code="QQQDOWNLOADEDTESTFLAG"/>
\</handlingCode>
\</organization>
\</location>
\<location>
\<organization>
\<code code="DAL"/>
\<name>
\<item>
\<part value="Dorothos" type="TITLE"/>
\</item>
\</name>
\<handlingCode>
\<item valueSet="Yes" code="QQQINDFLAG"/>
\<item valueSet="No" code="QQQDEFAULTTESTFLAG"/>
\<item valueSet="Yes" code="QQQDOWNLOADEDTESTFLAG"/>
\</handlingCode>
\</organization>
\</location>
\<location>
\<organization>
\<code code="TMl"/>
\<name>
\<item>
\<part value="Toulous" type="TITLE"/>
\</item>
\</name>
\<handlingCode>
\<item valueSet="Yes" code="QQQINDFLAG"/>
\<item valueSet="No" code="QQQDEFAULTTESTFLAG"/>
\<item valueSet="Yes" code="QQQDOWNLOADEDTESTFLAG"/>
\</handlingCode>
\</organization>
\</location>
\<location>
\<organization>
\<code code="PHP"/>
\<name>
\<item>
\<part value="Hershey" type="TITLE"/>
\</item>
\</name>
\<handlingCode>
\<item valueSet="Yes" code="QQQINDFLAG"/>
\<item valueSet="No" code="QQQDEFAULTTESTFLAG"/>
\<item valueSet="Yes" code="QQQDOWNLOADEDTESTFLAG"/>
\</handlingCode>
\</organization>
\</location>
\<location>
\<organization>
\<code code="MET"/>
\<name>
\<item>
\<part value="Metro" type="TITLE"/>
\</item>
\</name>
\<handlingCode>
\<item valueSet="Yes" code="QQQINDFLAG"/>
\<item valueSet="No" code="QQQDEFAULTTESTFLAG"/>
\<item valueSet="Yes" code="QQQDOWNLOADEDTESTFLAG"/>
\</handlingCode>
\</organization>
\</location>
\</origin>
\<analytes>
\<analyteComponent>
\<sequenceNumber value="1"/>
\<analyte>
\<code codeSystemName="Anywhereville" codeSystem="ABC" code="21382">
\<displayName value="CA 125"/>
\<translation codeSystem="LN" code="10334-1">
\<displayName value="Cancer Ag 125 \[Units/volume\] in Serum or Plasma"/>
\<translation codeSystem="LOINCCOMPONENTNAME">
\<displayName value="Cancer Ag 125"/>
</translation>
\<translation codeSystem="LOINCSHORTNAME">
\<displayName value="Cancer Ag125 SerPl-aCnc"/>
</translation>
\</translation>
\</code>
\<title value="CA 125"/>
\<methodCode>
\<item valueSet="U/mL" code="UNIT"/>
\</methodCode>
\<analyteDetails>
\<handlingCode>
\<item valueSet="No" code="REPORTABLE-AOE"/>
\</handlingCode>
\</analyteDetails>
\</analyte>
\</analyteComponent>
\</analytes>
\<scheduling>
\<scheduleEvent>
\<code code="SETUPDAYS-LIS"/>
\<text value="Tuesday-Saturday Night ~Report available: Next Day"/>
\</scheduleEvent>
\</scheduling>
\<specimenHandling>
\<annotations>
\<annotation>
\<code code="PREFERREDSPECIMEN-LIS"/>
\<text value="1.0 mL Serum"/>
\</annotation>
\<annotation>
\<code code="MINIMUMVOLUME-LIS"/>
\<text value="0.5 mL"/>
\</annotation>
\<annotation>
\<code code="OTHERACCEPTABLESPECIMEN-LIS"/>
\<text value="1.0 mL Serum (Min0.5 mL)~Red-top tube (no gel); Room Temperature;"/>
\</annotation>
\<annotation>
\<code code="SPECIMENSTABILITY-LIS"/>
\<text value="Room Temperature: 10 days~Refrigerated: 12 days~Frozen: 28 days"/>
\</annotation>
\<annotation>
\<code code="TRANSPORTTEMPERATURE-LIS"/>
\<text value="Room Temperature"/>
\</annotation>
\<annotation>
\<code code="TRANSPORTCONTAINER-LIS"/>
\<text value="Serum Separator Tube"/>
\</annotation>
\<annotation>
\<code code="TRANSPORTTEMPERATURE-LIS-STANDARD-FORMAT"/>
\<text value="Room Temperature"/>
\</annotation>
\<annotation>
\<code code="TRANSPORTCONTAINER-LIS-STANDARD-FORMAT"/>
\<text value="Serum Separator Tube"/>
\</annotation>
\</annotations>
\</specimenHandling>
\<billingInfo>
\<cPTCodes>
\<cPTCode>
\<code code="86304"/>
\</cPTCode>
\</cPTCodes>
\</billingInfo>
\<medicalInformation>
\<clinicalInformation>
\<code code="METHODOLOGY-LIS"/>
\<text value="Immunoassay"/>
\</clinicalInformation>
\<clinicalInformation>
\<code code="CLINICALSIGNIFICANCE-LIS"/>
\<text value="The CA 125 level can provide ..blah blah blah blah ... in the general population."/>
\</clinicalInformation>
\</medicalInformation>
\</test>
</p:test>
T