无法在 SQL Server 中将 TEXT 转换为 XML [英] Unable to cast TEXT to XML in SQL Server

问题描述

基本上我有一个名为 XML 的列，它的类型是 TEXT；由于其他原因，这无法更改，但我想知道如何将其转换为 XML.

Basically I have a column named XML that is of type TEXT; this cannot be changed for other reason, but I was wondering how I could cast it to XML.

它给了我一个错误

XML 解析:第 1 行，第 39 个字符，无法切换编码

XML parsing: line 1, character 39, unable to switch the encoding

尝试这样做时.无论如何，它仍然可以将其格式化为 XML 吗?我真的被困在这一点上.

when trying to do this. Is there anyways around it to still get it formatted to XML? I'm really stuck at this point.

列内数据:

<?xml version="1.0" encoding="utf-16"?>
<Record>
     <UserGuid>c624a356-9f18-403c-b404-790e79034c7d</UserGuid>
</Record>

这是转换的 SQL 代码:

Here is the cast SQL code:

SELECT CAST(XML AS XML).value('(/Record/UserGuid)[1]', 'NVARCHAR(max)')
FROM tbl_Module_RequestForms_Items

推荐答案

您的问题是:您的 XML 带有 encoding="utf-16"，但您的列是非 Unicode 列......

Your problem is: you have XML with an encoding="utf-16", but your column is a non-Unicode column......

假设您也不能将其更改为 NTEXT，则您必须执行两个嵌套的 CAST 来实现您想要的:

Assuming that you cannot change it to NTEXT either, you have to do two nested CAST to achieve what you're looking for:

SELECT 
    CAST(CAST(XML AS NTEXT) AS XML).value('(/Record/UserGuid)[1]', 'NVARCHAR(max)')
FROM 
    tbl_Module_RequestForms_Items

首先，您需要转换为NTEXT(或NVARCHAR(MAX))，然后您必须将该结果转换为XML, 在您可以使用它之前.

First, you need to cast to NTEXT (or NVARCHAR(MAX)), and then you have to cast that result to XML, before you can use it.

提示:删除那些其他原因"并将其转换为 XML 数据类型，如果您确实需要将其用作 XML .....

Tip: remove those "other reasons" and convert this to XML datatype if you really need to use it as XML .....

这篇关于无法在 SQL Server 中将 TEXT 转换为 XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！