I have an XMLTYPE column that contains similar XML structure but with different namespaces. I am trying to extract some value from that XML on different rows by using dynamic namespace string with the extractValue
operator, but so far I could not make it work.
To be a bit more explicit, here is the query I am trying to run:
SELECT extractValue(X.XML, '/ns1:a', 'xmlns:ns1="'||XSD.NAMESPACE||'"')
FROM XML_TEST X
JOIN XSD_TEST XSD ON X.XSD_ID = XSD.ID
Here is the SQL to create the tables and the data for it:
CREATE TABLE XSD_TEST (NAMESPACE VARCHAR2(1024), ID NUMBER(19) PRIMARY KEY);
CREATE TABLE XML_TEST(XML XMLTYPE, XSD_ID NUMBER(19));
ALTER TABLE XML_TEST ADD CONSTRAINT FK_XSD_ID FOREIGN KEY(XSD_ID) REFERENCES XSD_TEST(ID);
INSERT INTO XSD_TEST (NAMESPACE, ID) VALUES ('http://my.test/v1', 1);
INSERT INTO XSD_TEST (NAMESPACE, ID) VALUES ('http://my.test/v2', 2);
INSERT INTO XML_TEST (XML, XSD_ID) VALUES (XMLTYPE('<?xml version="1.0" encoding="UTF-8" standalone="no"?><v1:a xmlns:v1="http://my.test/v1">TEST1</v1:a>'), 1);
INSERT INTO XML_TEST (XML, XSD_ID) VALUES (XMLTYPE('<?xml version="1.0" encoding="UTF-8" standalone="no"?><v2:a xmlns:v2="http://my.test/v2">TEST2</v2:a>'), 2);
If I run :
SELECT extractValue(X.XML, '/ns1:a', 'xmlns:ns1="'||XSD.NAMESPACE||'"')
FROM XML_TEST X
JOIN XSD_TEST XSD ON X.XSD_ID = XSD.ID
WHERE X.XSD_ID = 1
It properly return TEST1.
If I run :
SELECT extractValue(X.XML, '/ns1:a', 'xmlns:ns1="'||XSD.NAMESPACE||'"')
FROM XML_TEST X
JOIN XSD_TEST XSD ON X.XSD_ID = XSD.ID
WHERE X.XSD_ID = 2
It properly return TEST2.
But if I run :
SELECT extractValue(X.XML, '/ns1:a', 'xmlns:ns1="'||XSD.NAMESPACE||'"')
FROM XML_TEST X
JOIN XSD_TEST XSD ON X.XSD_ID = XSD.ID;
It returns TEST1 and (null)
Can someone tell me why I am getting such results and how I could actually get the "correct" result: TEST1 and TEST2?
Following on from comments, extractValue()
is deprecated, which might explain why it's behaving the same in 12c as it did in 10g, though the code in the question works in 11g. You may have more joy using XMLQuery or XMLTable in 12c; but I don't have a 12c instance to test these on, so some of these 11gR2 observations also may not hold, though I would hope most would.
With an XMLQuery, you can't embed the namespace path with concatenation:
SELECT XMLQuery('declare namespace ns1="'||XSD.NAMESPACE||'"; /ns1:a/text()'
PASSING X.XML RETURNING CONTENT) AS VALUE
FROM XML_TEST X
JOIN XSD_TEST XSD ON X.XSD_ID = XSD.ID;
ORA-19109: RETURNING keyword expected
But you can use a CTE to generate the full XPath:
SELECT cast(XMLQuery('declare namespace ns1="http://my.test/v1"; /ns1:a/text()'
PASSING X.XML RETURNING CONTENT) as varchar2(30)) AS VALUE
FROM XML_TEST X
JOIN XSD_TEST XSD ON X.XSD_ID = XSD.ID;
VALUE
----------
TEST1
TEST2
Or as an alternative approach, you could wildcard the namespace (I'm casting here because my thin driver doesn't like the returned content, but you may not need to do that):
SELECT CAST(XMLQuery('//*[local-name() = ''a'']/text()'
PASSING X.XML RETURNING CONTENT) AS VARCHAR(10)) AS VALUE
FROM XML_TEST X;
VALUE
----------
TEST1
TEST2
Or wildcard the namespace, but then restrict using a passed variable, which is kind of neat:
SELECT CAST(XMLQuery('//*[local-name() = ''a'' and namespace-uri() = $ns1]/text()'
PASSING X.XML, XSD.NAMESPACE AS "ns1" RETURNING CONTENT) AS VARCHAR(10)) AS VALUE
FROM XML_TEST X
JOIN XSD_TEST XSD ON X.XSD_ID = XSD.ID;
VALUE
----------
TEST1
TEST2
With XMLTable, you can't pass the column value directly in either:
SELECT T.*
FROM XML_TEST X
JOIN XSD_TEST XSD ON X.XSD_ID = XSD.ID
CROSS JOIN XMLTable(XMLNamespaces(XSD.NAMESPACE as "ns1"), '/ns1:a'
PASSING X.XML
COLUMNS value VARCHAR2(80) PATH '.'
) T;
ORA-19102: XQuery string literal expected
You can use the wildcard approach here too:
SELECT T.*
FROM XML_TEST X
CROSS JOIN XMLTable('for $i in /*[local-name() = ''a''] return $i'
PASSING X.XML
COLUMNS value VARCHAR2(10) PATH '.'
) T;
VALUE
----------
TEST1
TEST2
Or passing the URI again:
SELECT T.*
FROM XML_TEST X
JOIN XSD_TEST XSD ON X.XSD_ID = XSD.ID
CROSS JOIN XMLTable('for $i in /*[local-name() = ''a'' and namespace-uri() = $ns1] return $i'
PASSING X.XML, XSD.NAMESPACE AS "ns1"
COLUMNS value VARCHAR2(10) PATH '.'
) T;
VALUE
----------
TEST1
TEST2
SQL Fiddle, also on 11gR2 (and needing a cast on the first query too in this environment).
It will be interesting to see how these behave in 12c, and which works best for you. Assuming they work at all...