HOWTO: Create DDL statements from XML

This was once posted on a Oracle OTN XMLDB forum; and though I thought it was easy, it wasn’t at all. It took me some sweat and I learned more about XPath than I had done so far and the (in)possibilities of using XPath within an Oracle database… All in all it was a good exercise.

Also see: http://forums.oracle.com/forums/thread.jspa?threadID=495729&tstart=0

var xmldoc varchar2(4000)
 
begin
	:xmldoc := 
	'
		EMP
			EMPNO
				Number(5)
			
			ENAME
				Varchar2(100)
			
			SAL
				Number(7,2)
			
		
		DEPT
			DEPTNO
				Number(5)
			
			DNAME
				Varchar2(50)
			
		 
	';
end;
/

select  'CREATE TABLE '
       ||extract(value(v1),'/Origination/Dimension[1]/text()')
       ||'('
       ||extract(value(v1),'/Origination/Dimension[1]/Measure[1]/text()')
       ||extract(value(v1),'/Origination/Dimension[1]/Measure[1]/DataType/text()')
       ||','
       ||extract(value(v1),'/Origination/Dimension[1]/Measure[2]/text()')
       ||extract(value(v1),'/Origination/Dimension[1]/Measure[2]/DataType/text()')
       ||','
       ||extract(value(v1),'/Origination/Dimension[1]/Measure[3]/text()')
       ||extract(value(v1),'/Origination/Dimension[1]/Measure[3]/DataType/text()')
       ||');' 
from   table(xmlsequence(extract(xmltype(:xmldoc),'/Origination'))) v1
UNION
select  'CREATE TABLE '
       ||extract(value(v1),'/Origination/Dimension[2]/text()')
       ||'('
       ||extract(value(v1),'/Origination/Dimension[2]/Measure[1]/text()')
       ||extract(value(v1),'/Origination/Dimension[2]/Measure[1]/DataType/text()')
       ||','
       ||extract(value(v1),'/Origination/Dimension[2]/Measure[2]/text()')
       ||extract(value(v1),'/Origination/Dimension[2]/Measure[2]/DataType/text()')
       ||');' 
from   table(xmlsequence(extract(xmltype(:xmldoc),'/Origination'))) v1
;


SQL> r
  1  select  'CREATE TABLE '
  2         ||extract(value(v1),'/Origination/Dimension[1]/text()')
  3         ||'('
  4         ||extract(value(v1),'/Origination/Dimension[1]/Measure[1]/text()')
  5         ||extract(value(v1),'/Origination/Dimension[1]/Measure[1]/DataType/text()')
  6         ||','
  7         ||extract(value(v1),'/Origination/Dimension[1]/Measure[2]/text()')
  8         ||extract(value(v1),'/Origination/Dimension[1]/Measure[2]/DataType/text()')
  9         ||','
 10         ||extract(value(v1),'/Origination/Dimension[1]/Measure[3]/text()')
 11         ||extract(value(v1),'/Origination/Dimension[1]/Measure[3]/DataType/text()')
 12         ||');'
 13  from   table(xmlsequence(extract(xmltype(:xmldoc),'/Origination'))) v1
 14  UNION
 15  select  'CREATE TABLE '
 16         ||extract(value(v1),'/Origination/Dimension[2]/text()')
 17         ||'('
 18         ||extract(value(v1),'/Origination/Dimension[2]/Measure[1]/text()')
 19         ||extract(value(v1),'/Origination/Dimension[2]/Measure[1]/DataType/text()')
 20         ||','
 21         ||extract(value(v1),'/Origination/Dimension[2]/Measure[2]/text()')
 22         ||extract(value(v1),'/Origination/Dimension[2]/Measure[2]/DataType/text()')
 23         ||');'
 24  from   table(xmlsequence(extract(xmltype(:xmldoc),'/Origination'))) v1
 25*

'CREATETABLE'||EXTRACT(VALUE(V1),'/ORIGINATION/DIMENSION[1]/TEXT()')||'('||EXTRA
--------------------------------------------------------------------------------
CREATE TABLE DEPT
                        (DEPTNO
                                Number(5),DNAME
                                Varchar2(50));

CREATE TABLE EMP
                        (EMPNO
                                Number(5),ENAME
                                Varchar2(100),SAL
                                Number(7,2));