Big EMPin Big EMPin - 1 year ago 63
SQL Question

Issue With Pulling Remote XML Data into Oracle Database

In an Oracle database, I am reading some XML from a RESTful web service that looks like this:

<ns1:parent xmlns:ns1=""
xs1:schemaLocation= "">
<ns1:value qualifiers="X" date="someTime">Value1</ns1:value>
<ns1:value qualifiers="X" date="someOtherTime">Value2</ns1:value>
<ns1:value qualifiers="X" date="some100thTime">Value100</ns1:value>

I am then trying to take this data and extract it into the database using the code below. However, when I do so it creates one record with only the SYSDATE field and the other two values are blank. I also attempted this with XMLTable, but that had the same result. I'm thinking there's an issue with the namespace, but I can't find the proper syntax and all other combinations that I've tried don't return any rows at all.

We also pull data from another, almost identical data source that does not use namespaces, and this code works fine (without all of the namespaces, of course). Am I getting the syntax of the namespaces wrong somewhere?

myXMLType XMLType := xmltype(We use a function to pull in the XML file here);
/*Outputting the XMLType here shows that data is in it*/
INSERT INTO output_table
ExtractValue(VALUE(p), '/values/ns1:value',
'xmlns:ns1=""') AS myValue,
ExtractValue(VALUE(p), '//values/ns1:value/@ns1:date',
'xmlns:ns1=""') AS myTime,
SYSDATE AS read_date
TABLE(XMLSequence(EXTRACT(myXMLType, '/ns1:parent/ns1:child/ns1:values',

Answer Source

You can use an XMLTable for this as you thought, but you need to supply an XMLNameSpaces clause too. This is using a bind variable called string which holds your XML text (with a couple of corrections):

var string varchar2(200);

  :string := '<ns1:parent xmlns:ns1="" 
  ns1:schemaLocation= "">
        <ns1:value qualifiers="X" date="someTime">Value1</ns1:value>
        <ns1:value qualifiers="X" date="someOtherTime">Value2</ns1:value>

SELECT myValue, myTime, SYSDATE AS read_date
FROM XMLTable(XMLNamespaces('' as "ns1"),
  passing XMLType(:string)
  columns myValue varchar2(20) path '.',
    myTime varchar2(20) path '@date'

MYVALUE              MYTIME               READ_DATE
-------------------- -------------------- ----------
Value1               someTime             2016-07-25
Value2               someOtherTime        2016-07-25

You can do the same thing with insert ... select using your myXMLType variable in the passing clause, instead of the XMLType(:string).

ExtractValue is deprecated anyway, and can only return one node so it would get ORA-19025 if you had the XPath right.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download