Using Oracle 11g again...Moving on from yesterdays XML examples, I added some more complexity and things started to go wrong!
- Why am I getting null values back?
- How do I get attribute values?
- Does this method scale to multiple rows?
After some investigation...
I'm getting null values back because my new XML had a default namespace. A default namespace needs to be passed into the extract function, otherwise it returns null. I could just hard code this in, or have a variable, but I think that would be a pain, so I've added a new inline query to get the default namespace value dynamically
Getting attribute values is straight forward XPath stuff, just use the @ symbol. Easy.
But all of my data is being concatinated together in one row! Well of course is it, the table it's selecting from only has one row. So this needs the XML to be converted into a table.
OK Here we go.
1. Create the table (if not done so on the previous post)
CREATE TABLE xml_test
( id NUMBER(9,0),
xml CLOB );
2. Insert some XML with 2 records, a default namespace and an attribute.
INSERT INTO xml_test VALUES ( 2,
'<?xml version="1.0" encoding="iso-8859-1"?>
<mydata MyAttribute="Fuzzy" xmlns:="http://www.abc.com">
<dude>
<id>100</id>
<name>John</name>
<address>
<line1>10 High Road</line1>
<line2>London</line2>
</address>
<phone>123456789</phone>
</dude>
<dude>
<id>101</id>
<name>Frank</name>
<address>
<line1>11 High Road</line1>
<line2>London</line2>
</address>
<phone>987654321</phone>
</dude>
</mydata>');
3. Query the data with the new query
WITH
xmlData AS
( SELECT xt.id,
XMLTYPE(xt.xml) xml
FROM xml_test xt
WHERE xt.id = 2
),
namespace AS
( SELECT xd.id,
'xmlns="'||XMLCAST(XMLQUERY('namespace-uri(.)' PASSING xd.xml RETURNING CONTENT) AS VARCHAR2(4000))||'"' defaultNs
FROM xmlData xd
)
SELECT xd.id,
xd.xml.EXTRACT('//mydata/@MyAttribute',ns.defaultNs).GETSTRINGVAL() myAttribute,
x.COLUMN_VALUE.EXTRACT('//dude/name/text()',ns.defaultNs).GETSTRINGVAL() dudeName,
x.COLUMN_VALUE.EXTRACT('//dude/address/line1/text()',ns.defaultNs).GETSTRINGVAL() dudeAddress
FROM xmlData xd,
namespace ns,
TABLE(XMLSEQUENCE(EXTRACT(xd.xml,'/mydata/dude',ns.defaultNs))) x
WHERE xd.id = ns.id
ORDER BY 1
;
And you should get...
ID MYATTRIBUTE DUDENAME DUDEADDRESS
-- ----------- -------- ------------
2 Fuzzy John 10 High Road
2 Fuzzy Frank 11 High Road
Phew
Posts
