如何在 ORACLE 中的 VARCHAR 中转换 XMLTYPE？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35039363/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to convert XMLTYPE in VARCHAR in ORACLE?
提问by Nemanja
I have two columns in my table(TRANSACTION) in ORACLE which are XMLTYPE(XML_IN and XML_OUT). My procedure is not working because I don't know how to convert them to VARCHAR or something(I just think that this is the error). My procedure is:
我在 ORACLE 的表 (TRANSACTION) 中有两列是 XMLTYPE(XML_IN 和 XML_OUT)。我的程序不起作用，因为我不知道如何将它们转换为 VARCHAR 或其他内容（我只是认为这是错误）。我的程序是：
PROCEDURE SEARCH_XML
(
P_ID_TRANSACTION IN TRANSACTION.ID_TRANSACTION%TYPE,
P_CURSOR OUT T_CURSOR
)
IS
BEGIN
OPEN P_CURSOR FOR
SELECT T.XML_IN, T.XML_OUT
FROM TRANSACTION T
WHERE T.ID_TRANSACTION = P_ID_TRANSACTION;
END SEARCH_XML;
When I call this procedure error message in VisualStudio2008 is: "Unsupported oracle data type USERDEFINED encountered." Any idea how is this working?
当我在 VisualStudio2008 中调用此过程时，错误消息是：“遇到不受支持的 oracle 数据类型 USERDEFINED。” 知道这是如何工作的吗？
回答by MT0
XMLTypehas two methods: getStringVal()and getClobVal()which will convert the XML structure to their string representations (as a VARCHAR2and CLOBrespectively). Unless you know that your XML output is going to always be less than 4000 characters (bytes) then you will probably want to use getClobVal()like this:
XMLType有两种方法：getStringVal()和getClobVal()将 XML 结构转换为它们的字符串表示形式（分别作为 aVARCHAR2和CLOB）。除非您知道您的 XML 输出将始终少于 4000 个字符（字节），否则您可能希望getClobVal()像这样使用：
PROCEDURE SEARCH_XML
(
P_ID_TRANSACTION IN TRANSACTION.ID_TRANSACTION%TYPE,
P_CURSOR OUT T_CURSOR
)
IS
BEGIN
OPEN P_CURSOR FOR
SELECT T.XML_IN.getClobVal() AS XML_IN,
T.XML_OUT.getClobVal() AS XML_OUT
FROM TRANSACTION T
WHERE T.ID_TRANSACTION = P_ID_TRANSACTION;
END SEARCH_XML;
