Ytukyg

I'm having trouble to query an xml from the database.
The original column in the database is of the type ntext, but an xml text is stored in this column.
When I cast the column to xml type, this is one of the results:

<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.desoft.de/activerepo/fileprops">

  <props>

    <prop ns="ARM:" elem="_NoFilter">

      <value xsi:type="xsd:boolean">true</value>

    </prop>

    <prop ns="DAV:" elem="displayname">

      <value xsi:type="xsd:string">RE: Demande de Rendez vous pour le 31 Janvier</value>

    </prop>

    <prop ns="DAV:" elem="getcontenttype">

      <value xsi:type="xsd:string">message/rfc822</value>

    </prop>

    <prop ns="DAV:" elem="creationdate">

      <value xsi:type="xsd:dateTime">2018-10-18T18:41:41.3993879</value>

    </prop>

    <prop ns="tm:mail:" elem="account">

      <value xsi:type="xsd:string">mailjournal@gg.com</value>

    </prop>

    <prop ns="tm:mail:" elem="journal">

      <value xsi:type="xsd:boolean">true</value>

    </prop>

    <prop ns="tm:mail:" elem="box">

      <value xsi:type="xsd:string">Inbox</value>

    </prop>

    <prop ns="tm:mail:" elem="onlyattachments">

      <value xsi:type="xsd:boolean">false</value>

    </prop>

    <prop ns="tm:mail:" elem="user">

      <value xsi:type="xsd:string">mailjournal@gg.com</value>

    </prop>

    <prop ns="urn:schemas:httpmail:" elem="attachments">

      <value xsi:type="xsd:boolean">true</value>

    </prop>

    ...

  </props>

</root>

Now I have to read single node values from the xml column, but actually this does not work and I don't know why.

select

    top 10

    cast(properties as xml),

    cast(properties as xml).value('(/props/prop[@elem="displayname"]/value)[1]', 'nvarchar(255)') as testRead,

    *

from   

       tm_cas_files f (nolock)

where

       properties is not null     

       and id = '64A663EF-A466-4EB0-97DC-94554ADFDFC9'

If I try to read the xml this way, the column testRead is always null.
Any hints for me? Thanks in advance for your comments and help.

You need to declare your default namespace here, as it's declared in your XML. This gets you the result you want:

CREATE TABLE dbo.SampleTable (nXML ntext);

INSERT INTO dbo.SampleTable (nXML)

VALUES 

(N'<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.desoft.de/activerepo/fileprops">

  <props>

    <prop ns="ARM:" elem="_NoFilter">

      <value xsi:type="xsd:boolean">true</value>

    </prop>

    <prop ns="DAV:" elem="displayname">

      <value xsi:type="xsd:string">RE: Demande de Rendez vous pour le 31 Janvier</value>

    </prop>

    <prop ns="DAV:" elem="getcontenttype">

      <value xsi:type="xsd:string">message/rfc822</value>

    </prop>

    <prop ns="DAV:" elem="creationdate">

      <value xsi:type="xsd:dateTime">2018-10-18T18:41:41.3993879</value>

    </prop>

    <prop ns="tm:mail:" elem="account">

      <value xsi:type="xsd:string">mailjournal@gg.com</value>

    </prop>

    <prop ns="tm:mail:" elem="journal">

      <value xsi:type="xsd:boolean">true</value>

    </prop>

    <prop ns="tm:mail:" elem="box">

      <value xsi:type="xsd:string">Inbox</value>

    </prop>

    <prop ns="tm:mail:" elem="onlyattachments">

      <value xsi:type="xsd:boolean">false</value>

    </prop>

    <prop ns="tm:mail:" elem="user">

      <value xsi:type="xsd:string">mailjournal@gg.com</value>

    </prop>

    <prop ns="urn:schemas:httpmail:" elem="attachments">

      <value xsi:type="xsd:boolean">true</value>

    </prop>

    ...

  </props>

</root>');



GO

WITH XMLNAMESPACES(DEFAULT 'http://www.desoft.de/activerepo/fileprops', 'http://www.w3.org/2001/XMLSchema-instance' AS xsi,'http://www.w3.org/2001/XMLSchema' AS xsd)

SELECT X.XMLData.value('(root/props/prop[2]/value/text())[1]','varchar(50)') AS [value]

FROM dbo.SampleTable ST

     CROSS APPLY (VALUES(CONVERT(xml,ST.nXML))) X(XMLData);



GO

DROP TABLE dbo.SampleTable;

GO

I've now made it work with your clues. The additional problem was that there were line breaks in the XML text. These had to be removed as well. Also the xml declaration was part of the text. This I removed as well. Then it worked. Here is the final code:

WITH XMLNAMESPACES(DEFAULT 'http://www.desoft.de/activerepo/fileprops', 'http://www.w3.org/2001/XMLSchema-instance' AS xsi,'http://www.w3.org/2001/XMLSchema' AS xsd)

select

    top 10

    cast(replace(replace(cast(properties as nvarchar(max)),CHAR(13) + CHAR(10),''),'<?xml version="1.0" encoding="utf-16"?>','') as xml).value('(root/props/prop[@elem="from"]/value)[1]', 'nvarchar(max)')

from   

       tm_cas_files f (nolock)

where

       properties is not null     

       and id = '64A663EF-A466-4EB0-97DC-94554ADFDFC9'

Thanks very much for your help!

Using XMLQuire to play with your xml tells me that the path to your answer starts with root :

/root/props/prop[2]/value

so you want '(/root/props/prop[@elem="displayname"]/value)[1]'