将oracle blob转换为xml类型 [英] convert oracle blob to xml type

问题描述

我有使用MSSQL 2008的经验，最近不得不从MSSQL迁移到Oracle 10g. 设计(Oracle)表的人(我需要从中提取数据的列)对他们需要存储的XML使用了BLOB类型的列.

I have experience using MSSQL 2008 and I recently had to move from MSSQL to Oracle 10g. The people who designed the (Oracle) table, which has a column I need to extract data from, used a BLOB type column for the XML they need to store.

在MSSQL中，您只需将XML字符串存储为XML类型或使用VARCHAR(MAX).假设表myTable的列为myColumn，该列为包含<ROOT><a>111</a></ROOT>的VARCHAR(MAX)如果您想将VARCHAR(MAX)类型转换为XML类型，则只需编写如下内容:

In MSSQL you would have simply stored your XML string in an XML type or used a VARCHAR(MAX). Assume a table myTable with a column called myColumn which is a VARCHAR(MAX) containing <ROOT><a>111</a></ROOT> If you wanted to convert the VARCHAR(MAX) type to an XML type you would simply write something like:

SELECT CONVERT(XML, myColumn) FROM myTable

如果需要，可以使用XQuery从转换后的列获取数据，如下所示:

if you wanted, you could then use XQuery to get data from the converted column, like so:

SELECT CONVERT(XML, myColumn).query('/ROOT/a')

如果myColumn是BLOB，您将如何在Oracle 10g中完成相同的工作，而不必编写存储过程，但仍使其可重用? BLOB中的文本为UFT-8.

How would you accomplish the same thing in Oracle 10g if myColumn was a BLOB, without having to write a stored procedure but still making it reusable? The text in the BLOB is UFT-8.

非常感谢您的帮助，因为我急急需要此帮助.

I would really appreciate your assistance, as I kind of need this in a hurry.

推荐答案

select
XMLType( BLOB_COLUMN,
         1 /* this is your character set ID.
                   1 == USASCII */
       ) as XML
from my_table;

更多字符集: http://www.mydul.net/charsets.html

这篇关于将oracle blob转换为xml类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！