As the title explains, how to parse data from object types in Oracle to the XML format?
So for instance, if I have an object type CUSTOMER, how one can convert the data from an instance of that object to XML text?
Add a comment
|
2 Answers
First you have your object type created:
CREATE TYPE CUSTOMER AS OBJECT
(NAME VARCHAR2(100));
It must be converted to an XMLTYPE, and then you'll be able to get the XML text from it with the getstringval function.
DECLARE
v_customer CUSTOMER;
v_xml XMLTYPE;
BEGIN
v_customer:= NEW CUSTOMER('Josh');
v_xml := XMLTYPE(v_customer);
DBMS_OUTPUT.put_line(v_xml.getstringval);
END;
This will produce the following output:
<CUSTOMER><NAME>Josh</NAME></CUSTOMER>
answered Apr 13, 2017 at 13:55
Evandro Pomatti
15.3k22 gold badges118 silver badges181 bronze badges
1 Comment
Seyran
As an addition, if you need case sensitive xml when describe fields and type names in quotes, like
CREATE TYPE CUSTOMER AS OBJECT ("Name" VARCHAR2(100));' then the output will be <CUSTOMER><Name>Josh</Name></CUSTOMER>`you can use SYS_XMLGEN
SELECT SYS_XMLGEN(v_customer, XMLFormat(enclTag =>'CUSTOMER'))
INTO v_xml
FROM dual;
less convienient to use.. but also supports TABLE OF ... types and is "pretty printed" to include newlines in the text
ie
DECLARE
v_customer CUSTOMER;
v_xml XMLTYPE;
BEGIN
v_customer:= NEW CUSTOMER('Josh');
SELECT SYS_XMLGEN(v_customer, XMLFormat(enclTag =>'CUSTOMER'))
INTO v_xml
FROM dual;
DBMS_OUTPUT.put_line(v_xml.getstringval);
END;
/
<?xml version="1.0"?>
<CUSTOMER>
<NAME>Josh</NAME>
</CUSTOMER>
create type tab_of_cust as TABLE OF CUSTOMER;
DECLARE
v_customer CUSTOMER;
v_xml XMLTYPE;
v_tab tab_of_cust := tab_of_cust();
BEGIN
v_customer:= NEW CUSTOMER('Josh');
v_tab.extend(1);
v_tab(1) := v_customer;
SELECT SYS_XMLGEN(v_tab, XMLFormat(enclTag =>'CUST_TAB'))
INTO v_xml
FROM dual;
DBMS_OUTPUT.put_line(v_xml.getstringval);
END;
/
<?xml version="1.0"?>
<CUST_TAB>
<CUSTOMER>
<NAME>Josh</NAME>
</CUSTOMER>
</CUST_TAB>
