a) I knew there was a way to accomplish that
b) I couldn't quickly turn it up via Google till I got the right keywords.
After seeing the solution that I ended up borrowing, I first checked the Oracle documentation on XMLTable for 10.2 to verify this was valid. As you can see by the documentation, the XML_passing_clause does allow for multiple expressions.
Curious as to see how this worked for different data types and on 10g and 11g, I created the following setup.
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
SQL>
SQL> CREATE TABLE XMLT
2 (xmlcol XMLType)
3 XMLTYPE column xmlcol store AS BINARY XML;
Table created
SQL>
SQL> INSERT INTO XMLT
2 VALUES
3 ('<Root>
4 <Object>
5 <ObjectID>ID_1</ObjectID>
6 <ObjectName>Name 1</ObjectName>
7 <ObjectValue>1</ObjectValue>
8 <ObjectDate>1980-01-01</ObjectDate>
9 </Object>
10 <Object>
11 <ObjectID>ID_2</ObjectID>
12 <ObjectName>Name 2</ObjectName>
13 <ObjectValue>2</ObjectValue>
14 </Object>
15 </Root>');
1 row inserted
SQL> commit;
Commit complete
sys_xmlgen works on both 10g and 11g for these scenarios. With this, everything is treated as a simple string so you just need to ensure the string you pass in matches up to the string in the XML.
SQL> SELECT xt.ObjID, xt.ObjName
2 FROM XMLT,
3 XMLTABLE('/Root/Object[ObjectID=$objID]'
4 PASSING XMLT.xmlcol,
5 sys_xmlgen('ID_1') as "objID"
6 COLUMNS
7 ObjID VARCHAR2(6) PATH 'ObjectID',
8 ObjName VARCHAR2(10) PATH 'ObjectName') xt;
OBJID OBJNAME
------ ----------
ID_1 Name 1
SQL> SELECT xt.ObjID, xt.ObjName
2 FROM XMLT,
3 XMLTABLE('/Root/Object[ObjectValue=$objVal]'
4 PASSING XMLT.xmlcol,
5 sys_xmlgen('2') as "objVal"
6 COLUMNS
7 ObjID VARCHAR2(6) PATH 'ObjectID',
8 ObjName VARCHAR2(10) PATH 'ObjectName') xt;
OBJID OBJNAME
------ ----------
ID_2 Name 2
SQL> SELECT xt.ObjID, xt.ObjName
2 FROM XMLT,
3 XMLTABLE('/Root/Object[ObjectDate=$objDt]'
4 PASSING XMLT.xmlcol,
5 sys_xmlgen('1980-01-01') as "objDt"
6 COLUMNS
7 ObjID VARCHAR2(6) PATH 'ObjectID',
8 ObjName VARCHAR2(10) PATH 'ObjectName') xt;
OBJID OBJNAME
------ ----------
ID_1 Name 1
Another way to do it in 11g is to use CAST to convert a string to the appropriate data type. I go overboard with the Date related queries. I will explain afterwards.
SQL> SELECT xt.ObjID, xt.ObjName
2 FROM XMLT,
3 XMLTABLE('/Root/Object[ObjectID=$objID]'
4 PASSING XMLT.xmlcol,
5 cast('ID_1' AS VARCHAR2(4)) as "objID"
6 COLUMNS
7 ObjID VARCHAR2(6) PATH 'ObjectID',
8 ObjName VARCHAR2(10) PATH 'ObjectName') xt;
OBJID OBJNAME
------ ----------
ID_1 Name 1
SQL> SELECT xt.ObjID, xt.ObjName
2 FROM XMLT,
3 XMLTABLE('/Root/Object[ObjectValue=$objVal]'
4 PASSING XMLT.xmlcol,
5 CAST('2' AS NUMBER) as "objVal"
6 COLUMNS
7 ObjID VARCHAR2(6) PATH 'ObjectID',
8 ObjName VARCHAR2(10) PATH 'ObjectName') xt;
OBJID OBJNAME
------ ----------
ID_2 Name 2
SQL> SELECT xt.ObjID, xt.ObjName
2 FROM XMLT,
3 XMLTABLE('/Root/Object[ObjectDate=$objDt]'
4 PASSING XMLT.xmlcol,
5 CAST('01-JAN-1980' AS DATE) as "objDt"
6 COLUMNS
7 ObjID VARCHAR2(6) PATH 'ObjectID',
8 ObjName VARCHAR2(10) PATH 'ObjectName') xt;
OBJID OBJNAME
------ ----------
ID_1 Name 1
SQL> SELECT xt.ObjID, xt.ObjName
2 FROM XMLT,
3 XMLTABLE('/Root/Object[ObjectDate=$objDt]'
4 PASSING XMLT.xmlcol,
5 CAST(DATE '1980-01-01' AS DATE) as "objDt"
6 COLUMNS
7 ObjID VARCHAR2(6) PATH 'ObjectID',
8 ObjName VARCHAR2(10) PATH 'ObjectName') xt;
OBJID OBJNAME
------ ----------
ID_1 Name 1
SQL> SELECT xt.ObjID, xt.ObjName
2 FROM XMLT,
3 XMLTABLE('/Root/Object[ObjectDate=$objDt]'
4 PASSING XMLT.xmlcol,
5 DATE '1980-01-01' as "objDt"
6 COLUMNS
7 ObjID VARCHAR2(6) PATH 'ObjectID',
8 ObjName VARCHAR2(10) PATH 'ObjectName') xt;
OBJID OBJNAME
------ ----------
ID_1 Name 1
SQL> SELECT xt.ObjID, xt.ObjName
2 FROM XMLT,
3 XMLTABLE('/Root/Object[ObjectDate=$objDt]'
4 PASSING XMLT.xmlcol,
5 TO_DATE('01-01-1980', 'MM-DD-YYYY') as "objDt"
6 COLUMNS
7 ObjID VARCHAR2(6) PATH 'ObjectID',
8 ObjName VARCHAR2(10) PATH 'ObjectName') xt;
OBJID OBJNAME
------ ----------
ID_1 Name 1
SQL> SELECT xt.ObjID, xt.ObjName
2 FROM XMLT,
3 XMLTABLE('/Root/Object[ObjectDate=$objDt]'
4 PASSING XMLT.xmlcol,
5 '1980-01-01' as "objDt"
6 COLUMNS
7 ObjID VARCHAR2(6) PATH 'ObjectID',
8 ObjName VARCHAR2(10) PATH 'ObjectName') xt;
OBJID OBJNAME
------ ----------
ID_1 Name 1
SQL> SELECT xt.ObjID, xt.ObjName
2 FROM XMLT,
3 XMLTABLE('/Root/Object[ObjectDate=$objDt]'
4 PASSING XMLT.xmlcol,
5 TO_DATE('01-01-1980', 'MM-DD-YYYY') as "objDt"
6 COLUMNS
7 ObjID VARCHAR2(6) PATH 'ObjectID',
8 ObjName VARCHAR2(10) PATH 'ObjectName') xt;
OBJID OBJNAME
------ ----------
ID_1 Name 1
With my setup, Oracle knows nothing about the XML in terms of data types so 2 and 1980-01-01 are simply strings to it. So, even though I was CASTing a string to a DATE, Oracle is converting the value back to a string during the XPath evaluation. (Note: This is not confirmed but highly suspected).
So why is using CAST good? It comes in useful when you have a schema registered within Oracle and the XMLType is based off of the schema. This provides Oracle with data type information and is especially true for Object Relational Storage where it is used to create data types for columns. Oracle knows the data type of the node so you want to pass in the same data type using an explicit conversion. This is the basic reasoning why you don't want to compare a number to a string. Oracle will implicitly convert one to the other and can cause issues it if encounters a string with "a1" in it that fails to convert to a number.. That is the purpose of CAST, so you can tell Oracle that a given parameter is a number or a date to avoid the implicit conversion.
Gotchas
- Running the above XMLTable with CAST SQL Statements will result in an ORA000932: inconsistent datatypes: expected - got
No comments:
Post a Comment