3

I have table DOCUMENTS with:

DOCUMENTS
____________________
DOCUMENTID   int
USERID       int
CONTENT      text

I have following XML stored in TEXT column with name CONTENT in a SQL Server database

<?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>

I find a way how to query dingle XML attribute in a single row from table, with heavy conversion :

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%'

I need to make query like "select all rows in table that in fifth attribute in XML have value 'search' "

For me the general problem is that column type is not XML, but I have text column with stored xml inside. when I try cast, query, value directly server return that I can use it Only with XML column.

I would be very grateful if someone suggest how to do this!

Thanks!

Kamen

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
RCancer
  • 33
  • 1
  • 4
  • 1
    `ntext`, `text`, and `image` data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use `nvarchar(max)`, `varchar(max)`, and `varbinary(max)` instead. [See details here](http://msdn.microsoft.com/en-us/library/ms187993.aspx) - you should **convert** your column to `XML` and then use it – marc_s Oct 15 '14 at 21:07

1 Answers1

3

SQL Server doesn't allow inline XML conversion to have functions applied, i.e.: no CAST(...).query(), use a CTE to handle the conversion:

;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%'

One thing though: I saw Unicode (Russian?) characters in the Content column. It may be better to use utf-16 in your XML and ntext for column type.

text and ntext are also on the way out. If this is new code, use nvarchar(max)

Code Different
  • 90,614
  • 16
  • 144
  • 163
  • The problem is that I can read this SQL database and I cannot change anything ... even to make trigers or views :(. The language is Bulgarian (similar with Russian) and because of that I add some in your code 'CAST(REPLACE(CAST(content AS NVARCHAR(MAX)),'utf-8','utf-16') AS XML)' . Now all is perfect! – RCancer Oct 16 '14 at 07:42
  • And if is not presumptuous I have one more question: if is possible instead index of nodes to use node name? I mean if "Attribute Name" is "Number" to take values... – RCancer Oct 20 '14 at 11:16