First off, I'll start by asking you not to shoot me down in flames; I did not design this system, I just have to work with the resulting, erm, pile of steaming ....! I can only apologise for inflicting it upon the rest of the world!
I'm on 10.2.0.4 (although we'll be migrating to 11.2.0.2 or (hopefully) 11.2.0.3 in the next couple of months or so, so I'm open to additional 11g answers), and we currently store our XMLTYPE columns (Marco, look away now!) as CLOBs, and will continue to do so when we've gone to 11g (sadly, GoldenGate won't replicate binary XML *{:-( )
We currently get XML from a number of different systems passed in, and - of course - each one passes it in differently. In particular, we get passed XML of the following format:
-- ignore this, had to put in an extra one to get the formatting to work! {noformat}*scratches head*{noformat}
<node1>
<node2>
<node3>
<node4>value1</node4>
<node5>
<node6>value2</node6>
<node7>value3</node7>
</node5>
</node3>
<node8>
<node9 att1="name1">
<node10>value4</node10>
<node11>value5</node11>
.....
</node9>
</node8>
</node2>
</node1>
and it gets messed around via a number of replaces and concatenation (after converting the XMLTYPE back to a CLOB and then back into an XMLTYPE...) into the following format:
<made_up_node1 att_new1="name2">
<node9_renamed att1="name1">
<node10>value4</node10>
<node11>value5</node11>
.....
</node9_renamed>
<made_up_node2 att_new2="name3">
<made_up_node3>
<node4>value1</node4>
<node6>value2</node6>
<node7>value3</node7>
</made_up_node3>
</made_up_node2>
</made_up_node1>
The above takes the entirety of the node9, puts it under a new root node and renames it, plus takes some of the values from nodes before node9 and puts them in a new set of nodes at the end.
I've worked out how to rename the "node9" to "node9_renamed":
with my_xml as (select xmltype('<node1>'||
' <node2>'||
' <node3>'||
' <node4>value1</node4>'||
' <node5>'||
' <node6>value2</node6>'||
' <node7>value3</node7>'||
' </node5>'||
' </node3>'||
' <node8>'||
' <node9 att1="name1">'||
' <node10>value4</node10>'||
' <node11>value5</node11>'||
' </node9>'||
' </node8>'||
' </node2>'||
'</node1>') xml_col
from dual),
trans_xml as (select XMLTransform(mx.xml_col,
xmltype('<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">'||
'<xsl:output method="xml"/>'||
' <xsl:template match="node()|@*">'||
' <xsl:copy>'||
' <xsl:apply-templates select="node()|@*"/>'||
' </xsl:copy>'||
' </xsl:template>'||
' <xsl:template match="node9">'||
' <xsl:element name="node9_renamed">'||
' <xsl:apply-templates select="node()|@*"/>'||
' </xsl:element>'||
' </xsl:template>'||
'</xsl:stylesheet>')) new_xml
from my_xml mx)
select *
from trans_xml;
but I got that from googling and searching the forums. I know nothing about xslt and, after looking at the documentation on http://www.w3.org/TR/xslt, I've just about managed to work out what most of the xslt does above, but can't for the life of me work out if my requirement is even possible, let alone how to go about doing it!
So, my question is: is it possible to rewrite the XML as per "my" requirements using XMLTRANSFORM and if it is, how do I go about it? Or, is it going to be better to stick to the current method of extracting stuff from the XML, concatenating it as appropriate and then converting back to an XMLTYPE?
Edited by: Boneist on 31-Jul-2012 15:59 - corrected formatting