Mixing
SQL XML - Read value from xml column
0
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.
sql-server xml edited Nov 21 '18 at 8:58
Praveen Valavan
449
asked Nov 21 '18 at 8:33
dns_nxdns_nx
1,41211729
add a comment |
0
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.
sql-server xml edited Nov 21 '18 at 8:58
Praveen Valavan
449
asked Nov 21 '18 at 8:33
dns_nxdns_nx
1,41211729
What is the expected result here? Also, why are you still usingntext? It's been deprecated since at least SQL Server 2005.
– Larnu
Nov 21 '18 at 8:50
It's not me usingntext. it is from a system we are using. I can't change this. The expected result is the valueRE: Demande de Rendez vous pour le 31 Janvierfrom the above example.
– dns_nx
Nov 21 '18 at 8:57
add a comment |
0
0
0
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.
sql-server xml edited Nov 21 '18 at 8:58
Praveen Valavan
449
asked Nov 21 '18 at 8:33
dns_nxdns_nx
1,41211729
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.
sql-server xml sql-server xml edited Nov 21 '18 at 8:58
Praveen Valavan
449
asked Nov 21 '18 at 8:33
dns_nxdns_nx
1,41211729
edited Nov 21 '18 at 8:58
Praveen Valavan
449
asked Nov 21 '18 at 8:33
dns_nxdns_nx
1,41211729
edited Nov 21 '18 at 8:58
Praveen Valavan
449
edited Nov 21 '18 at 8:58
Praveen Valavan
449
edited Nov 21 '18 at 8:58
Praveen Valavan
449
449
asked Nov 21 '18 at 8:33
dns_nxdns_nx
1,41211729
asked Nov 21 '18 at 8:33
dns_nxdns_nx
1,41211729
asked Nov 21 '18 at 8:33
dns_nxdns_nx
1,41211729
1,41211729
What is the expected result here? Also, why are you still usingntext? It's been deprecated since at least SQL Server 2005.
– Larnu
Nov 21 '18 at 8:50
It's not me usingntext. it is from a system we are using. I can't change this. The expected result is the valueRE: Demande de Rendez vous pour le 31 Janvierfrom the above example.
– dns_nx
Nov 21 '18 at 8:57
add a comment |
What is the expected result here? Also, why are you still usingntext? It's been deprecated since at least SQL Server 2005.
– Larnu
Nov 21 '18 at 8:50
It's not me usingntext. it is from a system we are using. I can't change this. The expected result is the valueRE: Demande de Rendez vous pour le 31 Janvierfrom the above example.
– dns_nx
Nov 21 '18 at 8:57
What is the expected result here? Also, why are you still using
ntext? It's been deprecated since at least SQL Server 2005.– Larnu
Nov 21 '18 at 8:50
What is the expected result here? Also, why are you still using
ntext? It's been deprecated since at least SQL Server 2005.– Larnu
Nov 21 '18 at 8:50
It's not me using
ntext. it is from a system we are using. I can't change this. The expected result is the value RE: Demande de Rendez vous pour le 31 Janvier from the above example.– dns_nx
Nov 21 '18 at 8:57
It's not me using
ntext. it is from a system we are using. I can't change this. The expected result is the value RE: Demande de Rendez vous pour le 31 Janvier from the above example.– dns_nx
Nov 21 '18 at 8:57
add a comment |
3 Answers
3
active
oldest
votes
1
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
answered Nov 21 '18 at 9:06
LarnuLarnu
17.8k41731
I tried your solution, but it did not succeed. The value was evennull
– dns_nx
Nov 21 '18 at 10:39
What did you run? If you run the full the fll SQL I've provided returns the valueRE: Demande de Rendez vous pour le 31 Janvier, @dns_nx . This mean that the SQL you have used isn't correct.
– Larnu
Nov 21 '18 at 10:40
@dns_nx db<>fiddle
– Larnu
Nov 21 '18 at 10:42
1
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(properties as xml), cast(properties as xml).value(' (/root/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'works for me. The two changes to OP's code are just (1) correct the path and (2) add the namespace
– Chris F Carroll
Nov 21 '18 at 11:14
add a comment |
0
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!
answered Nov 21 '18 at 12:13
dns_nxdns_nx
1,41211729
add a comment |
-1
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]'
answered Nov 21 '18 at 9:26
Chris F CarrollChris F Carroll
4,34613242
How does help the OP get the data using T-SQL?
– Larnu
Nov 21 '18 at 9:40
You're never going to get a non-null result out of the SQL if the xpath is wrong?
– Chris F Carroll
Nov 21 '18 at 9:45
No, that isn't true. If you use the wrong namespace, you will. If you take my answer, and comment out the line startingWITH XMLNAMESPACES, the return value will beNULL. But this answer doesn't help the OP on how to use XQuery, just tells them the node that the value is in. There's no detail on how they would get that value from the xml afterwards.
– Larnu
Nov 21 '18 at 9:45
I tried your solution, but it did not succeed
– dns_nx
Nov 21 '18 at 10:39
@Larnu I agree that the namespace is missing and yes that will also prevent a result. Equally, saying that the namespace is missing but ignoring the incorrect path is also only half way there? Secondly, a point-and-click tool saves hours when still on the xml learning curve.
– Chris F Carroll
Nov 21 '18 at 11:16
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53407992%2fsql-xml-read-value-from-xml-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
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
answered Nov 21 '18 at 9:06
LarnuLarnu
17.8k41731
I tried your solution, but it did not succeed. The value was evennull
– dns_nx
Nov 21 '18 at 10:39
What did you run? If you run the full the fll SQL I've provided returns the valueRE: Demande de Rendez vous pour le 31 Janvier, @dns_nx . This mean that the SQL you have used isn't correct.
– Larnu
Nov 21 '18 at 10:40
@dns_nx db<>fiddle
– Larnu
Nov 21 '18 at 10:42
1
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(properties as xml), cast(properties as xml).value(' (/root/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'works for me. The two changes to OP's code are just (1) correct the path and (2) add the namespace
– Chris F Carroll
Nov 21 '18 at 11:14
add a comment |
1
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
answered Nov 21 '18 at 9:06
LarnuLarnu
17.8k41731
I tried your solution, but it did not succeed. The value was evennull
– dns_nx
Nov 21 '18 at 10:39
What did you run? If you run the full the fll SQL I've provided returns the valueRE: Demande de Rendez vous pour le 31 Janvier, @dns_nx . This mean that the SQL you have used isn't correct.
– Larnu
Nov 21 '18 at 10:40
@dns_nx db<>fiddle
– Larnu
Nov 21 '18 at 10:42
1
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(properties as xml), cast(properties as xml).value(' (/root/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'works for me. The two changes to OP's code are just (1) correct the path and (2) add the namespace
– Chris F Carroll
Nov 21 '18 at 11:14
add a comment |
1
1
1
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
answered Nov 21 '18 at 9:06
LarnuLarnu
17.8k41731
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
answered Nov 21 '18 at 9:06
LarnuLarnu
17.8k41731
answered Nov 21 '18 at 9:06
LarnuLarnu
17.8k41731
answered Nov 21 '18 at 9:06
LarnuLarnu
17.8k41731
answered Nov 21 '18 at 9:06
LarnuLarnu
17.8k41731
17.8k41731
I tried your solution, but it did not succeed. The value was evennull
– dns_nx
Nov 21 '18 at 10:39
What did you run? If you run the full the fll SQL I've provided returns the valueRE: Demande de Rendez vous pour le 31 Janvier, @dns_nx . This mean that the SQL you have used isn't correct.
– Larnu
Nov 21 '18 at 10:40
@dns_nx db<>fiddle
– Larnu
Nov 21 '18 at 10:42
1
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(properties as xml), cast(properties as xml).value(' (/root/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'works for me. The two changes to OP's code are just (1) correct the path and (2) add the namespace
– Chris F Carroll
Nov 21 '18 at 11:14
add a comment |
I tried your solution, but it did not succeed. The value was evennull
– dns_nx
Nov 21 '18 at 10:39
What did you run? If you run the full the fll SQL I've provided returns the valueRE: Demande de Rendez vous pour le 31 Janvier, @dns_nx . This mean that the SQL you have used isn't correct.
– Larnu
Nov 21 '18 at 10:40
@dns_nx db<>fiddle
– Larnu
Nov 21 '18 at 10:42
1
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(properties as xml), cast(properties as xml).value(' (/root/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'works for me. The two changes to OP's code are just (1) correct the path and (2) add the namespace
– Chris F Carroll
Nov 21 '18 at 11:14
I tried your solution, but it did not succeed. The value was even
null– dns_nx
Nov 21 '18 at 10:39
I tried your solution, but it did not succeed. The value was even
null– dns_nx
Nov 21 '18 at 10:39
What did you run? If you run the full the fll SQL I've provided returns the value
RE: Demande de Rendez vous pour le 31 Janvier, @dns_nx . This mean that the SQL you have used isn't correct.– Larnu
Nov 21 '18 at 10:40
What did you run? If you run the full the fll SQL I've provided returns the value
RE: Demande de Rendez vous pour le 31 Janvier, @dns_nx . This mean that the SQL you have used isn't correct.– Larnu
Nov 21 '18 at 10:40
@dns_nx db<>fiddle
– Larnu
Nov 21 '18 at 10:42
@dns_nx db<>fiddle
– Larnu
Nov 21 '18 at 10:42
1
1
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(properties as xml), cast(properties as xml).value(' (/root/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' works for me. The two changes to OP's code are just (1) correct the path and (2) add the namespace– Chris F Carroll
Nov 21 '18 at 11:14
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(properties as xml), cast(properties as xml).value(' (/root/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' works for me. The two changes to OP's code are just (1) correct the path and (2) add the namespace– Chris F Carroll
Nov 21 '18 at 11:14
add a comment |
0
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!
answered Nov 21 '18 at 12:13
dns_nxdns_nx
1,41211729
add a comment |
0
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!
answered Nov 21 '18 at 12:13
dns_nxdns_nx
1,41211729
add a comment |
0
0
0
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!
answered Nov 21 '18 at 12:13
dns_nxdns_nx
1,41211729
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!
answered Nov 21 '18 at 12:13
dns_nxdns_nx
1,41211729
answered Nov 21 '18 at 12:13
dns_nxdns_nx
1,41211729
answered Nov 21 '18 at 12:13
dns_nxdns_nx
1,41211729
answered Nov 21 '18 at 12:13
dns_nxdns_nx
1,41211729
1,41211729
add a comment |
add a comment |
-1
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]'
answered Nov 21 '18 at 9:26
Chris F CarrollChris F Carroll
4,34613242
How does help the OP get the data using T-SQL?
– Larnu
Nov 21 '18 at 9:40
You're never going to get a non-null result out of the SQL if the xpath is wrong?
– Chris F Carroll
Nov 21 '18 at 9:45
No, that isn't true. If you use the wrong namespace, you will. If you take my answer, and comment out the line startingWITH XMLNAMESPACES, the return value will beNULL. But this answer doesn't help the OP on how to use XQuery, just tells them the node that the value is in. There's no detail on how they would get that value from the xml afterwards.
– Larnu
Nov 21 '18 at 9:45
I tried your solution, but it did not succeed
– dns_nx
Nov 21 '18 at 10:39
@Larnu I agree that the namespace is missing and yes that will also prevent a result. Equally, saying that the namespace is missing but ignoring the incorrect path is also only half way there? Secondly, a point-and-click tool saves hours when still on the xml learning curve.
– Chris F Carroll
Nov 21 '18 at 11:16
add a comment |
-1
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]'
answered Nov 21 '18 at 9:26
Chris F CarrollChris F Carroll
4,34613242
How does help the OP get the data using T-SQL?
– Larnu
Nov 21 '18 at 9:40
You're never going to get a non-null result out of the SQL if the xpath is wrong?
– Chris F Carroll
Nov 21 '18 at 9:45
No, that isn't true. If you use the wrong namespace, you will. If you take my answer, and comment out the line startingWITH XMLNAMESPACES, the return value will beNULL. But this answer doesn't help the OP on how to use XQuery, just tells them the node that the value is in. There's no detail on how they would get that value from the xml afterwards.
– Larnu
Nov 21 '18 at 9:45
I tried your solution, but it did not succeed
– dns_nx
Nov 21 '18 at 10:39
@Larnu I agree that the namespace is missing and yes that will also prevent a result. Equally, saying that the namespace is missing but ignoring the incorrect path is also only half way there? Secondly, a point-and-click tool saves hours when still on the xml learning curve.
– Chris F Carroll
Nov 21 '18 at 11:16
add a comment |
-1
-1
-1
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]'
answered Nov 21 '18 at 9:26
Chris F CarrollChris F Carroll
4,34613242
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]'
answered Nov 21 '18 at 9:26
Chris F CarrollChris F Carroll
4,34613242
edited Nov 21 '18 at 11:19
answered Nov 21 '18 at 9:26
Chris F CarrollChris F Carroll
4,34613242
answered Nov 21 '18 at 9:26
Chris F CarrollChris F Carroll
4,34613242
answered Nov 21 '18 at 9:26
Chris F CarrollChris F Carroll
4,34613242
4,34613242
How does help the OP get the data using T-SQL?
– Larnu
Nov 21 '18 at 9:40
You're never going to get a non-null result out of the SQL if the xpath is wrong?
– Chris F Carroll
Nov 21 '18 at 9:45
No, that isn't true. If you use the wrong namespace, you will. If you take my answer, and comment out the line startingWITH XMLNAMESPACES, the return value will beNULL. But this answer doesn't help the OP on how to use XQuery, just tells them the node that the value is in. There's no detail on how they would get that value from the xml afterwards.
– Larnu
Nov 21 '18 at 9:45
I tried your solution, but it did not succeed
– dns_nx
Nov 21 '18 at 10:39
@Larnu I agree that the namespace is missing and yes that will also prevent a result. Equally, saying that the namespace is missing but ignoring the incorrect path is also only half way there? Secondly, a point-and-click tool saves hours when still on the xml learning curve.
– Chris F Carroll
Nov 21 '18 at 11:16
add a comment |
How does help the OP get the data using T-SQL?
– Larnu
Nov 21 '18 at 9:40
You're never going to get a non-null result out of the SQL if the xpath is wrong?
– Chris F Carroll
Nov 21 '18 at 9:45
No, that isn't true. If you use the wrong namespace, you will. If you take my answer, and comment out the line startingWITH XMLNAMESPACES, the return value will beNULL. But this answer doesn't help the OP on how to use XQuery, just tells them the node that the value is in. There's no detail on how they would get that value from the xml afterwards.
– Larnu
Nov 21 '18 at 9:45
I tried your solution, but it did not succeed
– dns_nx
Nov 21 '18 at 10:39
@Larnu I agree that the namespace is missing and yes that will also prevent a result. Equally, saying that the namespace is missing but ignoring the incorrect path is also only half way there? Secondly, a point-and-click tool saves hours when still on the xml learning curve.
– Chris F Carroll
Nov 21 '18 at 11:16
How does help the OP get the data using T-SQL?
– Larnu
Nov 21 '18 at 9:40
How does help the OP get the data using T-SQL?
– Larnu
Nov 21 '18 at 9:40
You're never going to get a non-null result out of the SQL if the xpath is wrong?
– Chris F Carroll
Nov 21 '18 at 9:45
You're never going to get a non-null result out of the SQL if the xpath is wrong?
– Chris F Carroll
Nov 21 '18 at 9:45
No, that isn't true. If you use the wrong namespace, you will. If you take my answer, and comment out the line starting
WITH XMLNAMESPACES, the return value will be NULL. But this answer doesn't help the OP on how to use XQuery, just tells them the node that the value is in. There's no detail on how they would get that value from the xml afterwards.– Larnu
Nov 21 '18 at 9:45
No, that isn't true. If you use the wrong namespace, you will. If you take my answer, and comment out the line starting
WITH XMLNAMESPACES, the return value will be NULL. But this answer doesn't help the OP on how to use XQuery, just tells them the node that the value is in. There's no detail on how they would get that value from the xml afterwards.– Larnu
Nov 21 '18 at 9:45
I tried your solution, but it did not succeed
– dns_nx
Nov 21 '18 at 10:39
I tried your solution, but it did not succeed
– dns_nx
Nov 21 '18 at 10:39
@Larnu I agree that the namespace is missing and yes that will also prevent a result. Equally, saying that the namespace is missing but ignoring the incorrect path is also only half way there? Secondly, a point-and-click tool saves hours when still on the xml learning curve.
– Chris F Carroll
Nov 21 '18 at 11:16
@Larnu I agree that the namespace is missing and yes that will also prevent a result. Equally, saying that the namespace is missing but ignoring the incorrect path is also only half way there? Secondly, a point-and-click tool saves hours when still on the xml learning curve.
– Chris F Carroll
Nov 21 '18 at 11:16
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53407992%2fsql-xml-read-value-from-xml-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
What is the expected result here? Also, why are you still using
ntext? It's been deprecated since at least SQL Server 2005.– Larnu
Nov 21 '18 at 8:50
It's not me using
ntext. it is from a system we are using. I can't change this. The expected result is the valueRE: Demande de Rendez vous pour le 31 Janvierfrom the above example.– dns_nx
Nov 21 '18 at 8:57