如何在SQL Server TEXT列中查询包含XML（不是xml列类型）的值

Question

我有表DOCUMENTS，其中：

DOCUMENTS
____________________
DOCUMENTID   int
USERID       int
CONTENT      text

我在SQL Server数据库中以名称CONTENT将以下XML存储在TEXT列中

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<IDMSDocument>
    <DocumentContent>
        <Attribute Name="Number" GUID="{FFFFFFFF-0000-0000-0000-000000000001}">
            <IDMSType>3060</IDMSType>
            <Value Type="Integer">
                <Value>122</Value>
            </Value>
        </Attribute>
        <Attribute Name="Date" GUID="{FFFFFFFF-0000-0000-0000-000000000002}">
            <IDMSType>3061</IDMSType>
            <Value Type="DateTime">
                <Date>10-09-2014</Date>
            </Value>
        </Attribute>
        <Attribute Name="袩褍斜谢懈泻褍胁邪薪械" GUID="{CA646F55-5229-4FC5-AA27-494B25023F4E}">
            <IDMSType>3062</IDMSType>
            <Value Type="String">
                <Value>袛邪</Value>
            </Value>
        </Attribute>
        <Attribute Name="袛邪褌邪" GUID="{AC0465B0-4FB4-49E2-B4FA-70901068FD9B}">
            <IDMSType>3063</IDMSType>
            <Value Type="DateTime">
                <Date>01-10-2014</Date>
            </Value>
        </Attribute>
        <Attribute Name="袩褉械写屑械褌 薪邪 锌芯褉褗褔泻邪" GUID="{04A4EC72-6F33-461F-98DD-D8D271997788}">
            <IDMSType>3065</IDMSType>
            <Value Type="String">
                <Value>袠蟹斜芯褉 薪邪 泻芯薪褋褍谢褌邪薪褌懈</Value>
            </Value>
        </Attribute>
    </DocumentContent>
</IDMSDocument>

我找到一种方法，可以通过大量转换从表中查询单行XML属性：

DECLARE @strContent NVARCHAR(MAX);
DECLARE @xmlContent XML;
SET @strContent = (select content from DOCUMENTS where DocumentID=24);
SET @xmlContent = CAST(REPLACE(CAST(@strContent AS NVARCHAR(MAX)),'utf-8','utf-16') AS XML) 
SELECT @xmlContent.query('/IDMSDocument/DocumentContent/Attribute[5]/Value/Value') 
where @xmlContent.value('(/IDMSDocument/DocumentContent/Attribute[5]/Value/Value)[1]', 'nvarchar(max)') like '%search%'

我需要进行如下查询：“选择XML中第五个属性中具有值’search’的表中的所有行”

对我来说，一般的问题是列类型不是XML，但是我的text列中有存储的xml。当我尝试强制转换，查询，直接向服务器返回值时，我只能将其与XML列一起使用。

如果有人建议如何做，我将不胜感激！

谢谢！

假面

Answer 1

SQL Server不允许内联XML转换应用函数，即：no CAST(...).query()，请使用CTE处理转换：

;WITH cte AS (
  SELECT DocumentID, UserID, CAST(Content AS XML) AS XMLContent
  FROM Documents
)

SELECT XMLContent.query('/IDMSDocument/DocumentContent/Attribute[5]/Value/Value') 
FROM cte
WHERE XMLContent.value('(/IDMSDocument/DocumentContent/Attribute[5]/Value/Value)[1]', 'nvarchar(max)') like '%search%'

但是，有一件事：我在Content列中看到了Unicode（俄语）字符。最好utf-16在XML和ntext列类型中使用。

text而且ntext还在出路。如果这是新代码，请使用nvarchar(max)