create table input_xml(
inp_xml sys.xmltype,
inp_xsl sys.xmltype,
result sys.xmltype);

create table employees2(
  firstname varchar(50),
  lastname varchar(50),
  position varchar(50),
  email varchar(50),
  category varchar(50),
  age number
  );
  
drop table employees2;
  
DECLARE
  insCtx DBMS_XMLStore.ctxType;
  rows NUMBER;
  xmldoc CLOB :=
    '<ROWSET>
    <ROW>
        <FIRSTNAME> Tim </FIRSTNAME>
        <LASTNAME>Browne</LASTNAME>
        <POSITION>CEO</POSITION>
        <EMAIL>TBrowne@Infoteria.com</EMAIL>
        <CATEGORY>Officer</CATEGORY>
        <AGE>25</AGE>
    </ROW>
    <ROW>
        <FIRSTNAME> David </FIRSTNAME>
        <LASTNAME>Silverlight</LASTNAME>
        <POSITION>Chief XML Evangelist</POSITION>
        <EMAIL>HeadGeek@XMLPitstop.com</EMAIL>
        <CATEGORY>Evangelist</CATEGORY>
        <AGE>33</AGE>
    </ROW>
    <ROW>
        <FIRSTNAME> Mike </FIRSTNAME>
        <LASTNAME>Damphouse</LASTNAME>
        <POSITION>Chief Marketing Officer </POSITION>
        <EMAIL>MDamphouse@Infoteria.com</EMAIL>
        <CATEGORY>Officer</CATEGORY>
        <AGE>33</AGE>
    </ROW>
    <ROW>
        <FIRSTNAME> Todd </FIRSTNAME>
        <LASTNAME>Headrick</LASTNAME>
        <POSITION>Chief Technology Officer</POSITION>
        <EMAIL>Theadrick@Infoteria.com</EMAIL>
        <CATEGORY>Officer</CATEGORY>
        <AGE>33</AGE>
    </ROW>
    <ROW>
        <FIRSTNAME> Constance </FIRSTNAME>
        <LASTNAME>Culp</LASTNAME>
        <POSITION>Vice President of Operations</POSITION>
        <EMAIL>CCulp@Infoteria.com</EMAIL>
        <CATEGORY>Vice President</CATEGORY>
        <AGE>33</AGE>
    </ROW>
</ROWSET>';
BEGIN
  insCtx := DBMS_XMLStore.newContext('ragpaai.employees2'); -- get saved context
  DBMS_XMLStore.clearUpdateColumnList(insCtx); -- clear the update settings 
  -- set the columns to be updated as a list of values 
  DBMS_XMLStore.setUpdateColumn(insCtx,'FIRSTNAME'); 
  DBMS_XMLStore.setUpdateColumn(insCtx,'LASTNAME'); 
  DBMS_XMLStore.setUpdatecolumn(insCtx,'POSITION'); 
  DBMS_XMLStore.setUpdateColumn(insCtx,'EMAIL'); 
  DBMS_XMLStore.setUpdateColumn(insCtx,'CATEGORY'); 
  DBMS_XMLStore.setUpdatecolumn(insCtx,'AGE'); 
  -- Now insert the doc.
  -- This will only insert into EMPNO, SAL and HIREDATE columns
  rows := DBMS_XMLStore.insertXML(insCtx, xmlDoc);
  -- Close the context
  DBMS_XMLStore.closeContext(insCtx); 
END;
/  


insert into input_xml values(
--inpus
XMLTYPE.createxml('<?xml version="1.0" encoding="UTF-8"?>
<Company>
	<Employee>
		<FirstName>Tim</FirstName>
		<LastName>Browne</LastName>
		<Position>CEO</Position>
		<Email>TBrowne@Infoteria.com</Email>
		<Category>Officer</Category>
		<Age>25</Age>
	</Employee>
	<Employee>
		<FirstName>David</FirstName>
		<LastName>Silverlight</LastName>
		<Position>Chief XML Evangelist</Position>
		<Email>HeadGeek@XMLPitstop.com</Email>
		<Category>Evangelist</Category>
		<Age>33</Age>
	</Employee>
	<Employee>
		<FirstName>Mike </FirstName>
		<LastName>Damphouse</LastName>
		<Position>Chief Marketing Officer </Position>
		<Email>MDamphouse@Infoteria.com</Email>
		<Category>Officer</Category>
		<Age>33</Age>
	</Employee>
	<Employee>
		<FirstName>Todd</FirstName>
		<LastName>Headrick</LastName>
		<Position>Chief Technology Officer</Position>
		<Email>Theadrick@Infoteria.com</Email>
		<Category>Officer</Category>
		<Age>33</Age>
	</Employee>
	<Employee>
		<FirstName>Constance</FirstName>
		<LastName>Culp</LastName>
		<Position>Vice President of Operations</Position>
		<Email>CCulp@Infoteria.com</Email>
		<Category>Vice President</Category>
		<Age>33</Age>
	</Employee>
</Company>'),
--xlt
XMLTYPE.createxml('<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="/">
<ROWSET>
<xsl:for-each select="Company/Employee">
<ROW>
<FIRSTNAME> <xsl:value-of select="FirstName"/> </FIRSTNAME>
<LASTNAME><xsl:value-of select="LastName"/></LASTNAME>
		<POSITION><xsl:value-of select="Position"/></POSITION>
		<EMAIL><xsl:value-of select="Email"/></EMAIL>
		<CATEGORY><xsl:value-of select="Category"/></CATEGORY>
		<AGE><xsl:value-of select="Age"/></AGE>
</ROW>
</xsl:for-each>
</ROWSET>
</xsl:template>
</xsl:stylesheet>
'),
--result

XMLTransform(
--xml
XMLTYPE.createxml('<?xml version="1.0" encoding="UTF-8"?>
<Company>
	<Employee>
		<FirstName>Tim</FirstName>
		<LastName>Browne</LastName>
		<Position>CEO</Position>
		<Email>TBrowne@Infoteria.com</Email>
		<Category>Officer</Category>
		<Age>25</Age>
	</Employee>
	<Employee>
		<FirstName>David</FirstName>
		<LastName>Silverlight</LastName>
		<Position>Chief XML Evangelist</Position>
		<Email>HeadGeek@XMLPitstop.com</Email>
		<Category>Evangelist</Category>
		<Age>33</Age>
	</Employee>
	<Employee>
		<FirstName>Mike </FirstName>
		<LastName>Damphouse</LastName>
		<Position>Chief Marketing Officer </Position>
		<Email>MDamphouse@Infoteria.com</Email>
		<Category>Officer</Category>
		<Age>33</Age>
	</Employee>
	<Employee>
		<FirstName>Todd</FirstName>
		<LastName>Headrick</LastName>
		<Position>Chief Technology Officer</Position>
		<Email>Theadrick@Infoteria.com</Email>
		<Category>Officer</Category>
		<Age>33</Age>
	</Employee>
	<Employee>
		<FirstName>Constance</FirstName>
		<LastName>Culp</LastName>
		<Position>Vice President of Operations</Position>
		<Email>CCulp@Infoteria.com</Email>
		<Category>Vice President</Category>
		<Age>33</Age>
	</Employee>
</Company>'),
--xslt
XMLTYPE.createxml('<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="/">
<ROWSET>
<xsl:for-each select="Company/Employee">
<ROW>
<FIRSTNAME> <xsl:value-of select="FirstName"/> </FIRSTNAME>
<LASTNAME><xsl:value-of select="LastName"/></LASTNAME>
		<POSITION><xsl:value-of select="Position"/></POSITION>
		<EMAIL><xsl:value-of select="Email"/></EMAIL>
		<CATEGORY><xsl:value-of select="Category"/></CATEGORY>
		<AGE><xsl:value-of select="Age"/></AGE>
</ROW>
</xsl:for-each>
</ROWSET>
</xsl:template>
</xsl:stylesheet>')));


