I am attempting to extract information from XML stored in a CLOB column. I've searched the forums and thus far have been unable to get the data to pull as needed. I have a basic understanding of SQL but this is beyond me.
The XML is similar to the following:
<?xml version="1.0" encoding="UTF-8"?>
<Response>
<Header>
<OrderNum value="12354321"/>
<ExtractDate value="11-30-2012"/>
<RType value="Status"/>
<Company value="Company"/>
</Header>
<Body>
<Status>
<Order>
<ActivityType value="ValidateRequest"/>
<EndUser>
<Name value="Schmo, Joe"/>
<Address>
<SANO value="12345"/>
<SASN value="Mickey Mouse"/>
<SATH value="Lane"/>
<SASS value="N"/>
<City value="Orlando"/>
<State value="FL"/>
<Zip value="34786"/>
<Number value="5550000"/>
</Address>
</EndUser>
<COS value="1"/>
<TOS value="3"/>
<MainNumber value="5550000"/>
</Order>
<ErrorCode value="400"/>
<ErrorMessage value="RECEIVED"/>
</Status>
</Body>
</Response>
I want to get the values under "Address".
I've tried the following but it returns "NULL".
SELECT EXTRACTVALUE(XMLTYPE(RESPONSE_CLOB),'/Response/Body/Status/Order/EndUser/Address/SANO') AS SANO
FROM RESPONSE_TABLE
WHERE ROWNUM < 2
I am trying to get it so I can pull the "12345" assigned as "value" in "SANO" (ultimately getting the value for other fields, but want to at least get the one working first).
You're currently retrieving the text value of the node, but 12345 is the value attribute of the element rather than its text content. So you would need to use the @attribute syntax, i.e.:
SELECT EXTRACTVALUE(XMLTYPE(RESPONSE_CLOB),'/Response/Body/Status/Order/EndUser/Address/SANO/@value') AS SANO
FROM RESPONSE_TABLE
WHERE ROWNUM < 2;
SANO
--------------------
12345
But extractvalue is deprecated; assuming you're on a recent version of Oracle it would be better to use an XMLQuery:
SELECT XMLQUERY(
'/Response/Body/Status/Order/EndUser/Address/SANO/@value'
PASSING XMLTYPE(RESPONSE_CLOB)
RETURNING CONTENT
) AS SANO
FROM RESPONSE_TABLE
WHERE ROWNUM < 2;
You may find it even easier to use an XMLTable - necessary if an XML document has multiple Address nodes, but even with just one pulling the values out as columns is less repetitive, and it makes it easier to retrieve suitable data types:
select x.*
from response_table rt
cross join xmltable(
'/Response/Body/Status/Order/EndUser/Address'
passing xmltype(rt.response_clob)
columns sano number path 'SANO/@value',
sasn varchar2(30) path 'SASN/@value',
sath varchar2(10) path 'SATH/@value'
-- etc.
) x
where rownum < 2;
SANO SASN SATH
-------------------- ------------------------------ ----------
12345 Mickey Mouse Lane
Read more about using these functions to query XML data.