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 := 
	'<origination>
		<dimension>EMP
			<measure>EMPNO
				<datatype>Number(5)</datatype>
			</measure>
			<measure>ENAME
				<datatype>Varchar2(100)</datatype>
			</measure>
			<measure>SAL
				<datatype>Number(7,2)</datatype>
			</measure>
		</dimension>
		<dimension>DEPT
			<measure>DEPTNO
				<datatype>Number(5)</datatype>
			</measure>
			<measure>DNAME
				<datatype>Varchar2(50)</datatype>
			</measure>
		</dimension> 
	</origination>';
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));
Marco Gralike Written by: