SELECT XMLROOT(xmltype(t.col1.getblobval(nls_charset_id('UTF8')), nls_charset_id('CL8MSWIN1251')), VERSION '1.0" encoding="utf-8')
FROM zzz.temptable t;

-- в t.data содержатся блобы с xml
select xmltype(t.data, 871) 
from temptable  t where t.doc_format_id = 6 and t.culture_code = 'ru'

select [color=red]xmltype(t.data, 871) .getclobval() [/color]
from temptable  t where t.doc_format_id = 6 and t.culture_code = 'ru'

-- конвертим блоб в xml type
declare
v_clob    CLOB;
v_varchar VARCHAR2(32767);
v_start   PLS_INTEGER := 1;
v_buffer  PLS_INTEGER := 32767;
blob_in   blob;
x         xmltype;
BEGIN
DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
 
 select t.data into blob_in from zzz.temptable  t where t.doc_template_id = 100 and t.doc_format_id = 6 and t.culture_code = 'ru';
 
 FOR i IN 1 .. CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer) LOOP
 
   v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_in,
                                                         v_buffer,
                                                         v_start));
 
   DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);
 
   v_start := v_start + v_buffer;
 END LOOP;
 
 x := xmltype.createxml(v_clob);
 dbms_output.put_line(x.getclobval());
 
-- меняю кодировку, чтоб вместо иероглифов отображалась кириллица
[color=red]

select xmltype (x ,871)
from dual;

[/color]

end;

select dbms_lob.substr(t.data, 100)

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
	<xsl:output omit-xml-declaration="yes"/>
	<xsl:template match="/uuu-uuuuuuu">

		<xsl:variable name="currencyFormat">
			<xsl:choose>
				<xsl:when test="./client='zzzzz'">
					<xsl:text>0</xsl:text>
				</xsl:when>
				<xsl:when test="./client='xxxxx'">
					<xsl:text>0.0000</xsl:text>
				</xsl:when>
				<xsl:otherwise>
					<xsl:text>0.00</xsl:text>
				</xsl:otherwise>
			</xsl:choose>
		</xsl:variable>
		<xsl:variable name="ppppppp" select="format-number(translate(./current_ppppppp/current_ppppppp,&quot;,&quot;,&quot;.&quot;),$currencyFormat)"/>

		<xsl:if test="./doc_type = '97' or ./doc_type = '659'">
			<xsl:choose>
				<xsl:when test="./client='iiiiii'">
					<xsl:choose>
						<xsl:when test="./usage &lt;= 0">
							<xsl:text>гнпеаквчарпенголд </xsl:text>
							<xsl:value-of select="./current_date/current_date"/>
							<xsl:text> ррооло </xsl:text>
							<xsl:value-of select="$ppppppp"/>
							<xsl:text> </xsl:text>
							<xsl:value-of select="./cur/cur"/>
							<xsl:text>. гоперап.</xsl:text>
						</xsl:when>
						<xsl:otherwise>
							<xsl:text>невуыекнгол </xsl:text>
							<xsl:value-of select="$ppppppp"/>
							<xsl:text> </xsl:text>
							<xsl:value-of select="./cur/cur"/>
							<xsl:text>. лопаввыячрног </xsl:text>
						</xsl:otherwise>
					</xsl:choose>
				</xsl:when>

				<xsl:otherwise>
					<xsl:text>кеткерн </xsl:text>
					<xsl:value-of select="$ppppppp"/>
					<xsl:text> </xsl:text>
					<xsl:value-of select="./cur/cur"/>
					<xsl:text>. керткетькнт </xsl:text>
					<xsl:value-of select="./usage"/>
					<xsl:text>%.</xsl:text>
					<xsl:choose>
						<xsl:when test="./client='xxxxx'">
							<xsl:text/>
						</xsl:when>
						<xsl:when test="./client='zzzzz'">
							<xsl:text> уенркенркенр</xsl:text>
						</xsl:when>
						<xsl:otherwise>
							<xsl:text> кенркенркеп</xsl:text>
						</xsl:otherwise>
					</xsl:choose>
					<xsl:text>.</xsl:text>
				</xsl:otherwise>
			</xsl:choose>
		</xsl:if>

		<xsl:if test="./doc_type = '98' or ./doc_type = '660'">
			<xsl:choose>
				<xsl:when test="./client='zzzzz' or ./client='xxxxx'">
					<xsl:text>укеукпеуен </xsl:text>
				</xsl:when>
				<xsl:otherwise>
					<xsl:text>уенркенркрн </xsl:text>
				</xsl:otherwise>
			</xsl:choose>
			<xsl:value-of select="$ppppppp"/>
			<xsl:text> </xsl:text>
			<xsl:value-of select="./cur/cur"/>
			<xsl:if test="./client='zzzzz'">
				<xsl:text> екнернеуке</xsl:text>
			</xsl:if>
			<xsl:text> шгрншгр </xsl:text>
			<xsl:value-of select="./current_date/current_date"/>
			<xsl:choose>
				<xsl:when test="./client='zzzzz' or ./client='xxxxx'">
					<xsl:text>. ывапцукпцукпе.</xsl:text>
				</xsl:when>
				<xsl:otherwise>
					<xsl:text>. уакпцукпцук.</xsl:text>
				</xsl:otherwise>
			</xsl:choose>
		</xsl:if>
	</xsl:template>
</xsl:stylesheet>

select xmltype(t.data, 871)