oracle 从 pl/sql 中超过 4000 个字符的 Varchar 转换为 XMLTYPE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20186838/
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
Converting from a Varchar in pl/sql which is more than 4000 characters to a XMLTYPE
提问by user3030396
I came across a problem where I need to convert a string of more than 4000 characters to a XMLTYPE. I tried using
我遇到了一个问题，需要将超过 4000 个字符的字符串转换为 XMLTYPE。我尝试使用
XMLTYPE.CREATEXML("MY STRING")
XMLTYPE("MY STRING")
In both cases, I got 'String literal too long..' error.
在这两种情况下，我都收到了“字符串文字太长..”错误。
回答by psaraj12
We can pass only the data in xml format for creating an XMLTYPE instance and use CLOB to pass the string value and then use the EXTRACTVALUE Function to retreive the data
我们可以只传递 xml 格式的数据来创建一个 XMLTYPE 实例并使用 CLOB 传递字符串值，然后使用 EXTRACTVALUE 函数来检索数据
DECLARE
str CLOB;
x XMLTYPE;
y CLOB:='<TEXT>MY STRING</TEXT>';
BEGIN
x:= XMLTYPE.CREATEXML(y);
select to_clob(extractvalue(x,'/TEXT')) into str FROM DUAL;
dbms_output.put_line(str);
END;
Please check the links XMLTYPE Examplesand XMLTYPE Documantationfor more practical examples
请查看链接XMLTYPE 示例和XMLTYPE 文档以获得更多实用示例
回答by Adam
If you want to select the whole XML type returned as a Varchar you can doing the following. Here I'm selecting the whole xml value.
如果您想选择作为 Varchar 返回的整个 XML 类型，您可以执行以下操作。在这里，我选择了整个 xml 值。
DECLARE
str VARCHAR2(4000);
x XMLTYPE;
y CLOB:='<TEXT>MY STRING</TEXT>';
BEGIN
x:= XMLTYPE.CREATEXML(y);
str := x.EXTRACT('/').getstringval();
dbms_output.put_line(str);
END;
OUTPUT:<TEXT>MY STRING</TEXT>
输出：<TEXT>MY STRING</TEXT>
You can drill down to child nodes also like the following.
您也可以向下钻取到子节点，如下所示。
DECLARE
str VARCHAR2(4000);
x XMLTYPE;
y CLOB:='<PARENT><TEXT>MY STRING</TEXT></PARENT>';
BEGIN
x:= XMLTYPE.CREATEXML(y);
str := x.EXTRACT('PARENT/TEXT').getstringval();
dbms_output.put_line(str);
END;
OUTPUT:<TEXT>MY STRING</TEXT>
输出：<TEXT>MY STRING</TEXT>
Finally if you just want the value you can do.
最后，如果你只想要你可以做的价值。
DECLARE
str VARCHAR2(4000);
x XMLTYPE;
y CLOB:='<PARENT><TEXT>MY STRING</TEXT></PARENT>';
BEGIN
x:= XMLTYPE.CREATEXML(y);
str := x.EXTRACT('PARENT/TEXT/text()').getstringval();
dbms_output.put_line(str);
END;
OUTPUT:MY STRING
输出：MY STRING
