Hi @Alen Cappelletti ,
Your XML has a default namespace. It needs a special handling.
Please share a DDL and sample data population for the [dbo].[errors] table.
In the meantime, please try the following T-SQL.
It shows both ways: directly from the DB table as well as XML data type variable.
-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">;
<code>/IWCOR/CX_DS_EDM_FACET_ERROR/005056A509B11ED1BDCCCC5E8168819D</code>
<message xml:lang="it">bla bla bla</message>
<innererror>
<transactionid>9849849</transactionid>
<timestamp>20171220145553.0525760</timestamp>
<Error_Resolution>
<SAP_Transaction>ewrerwr</SAP_Transaction>
<SAP_Note>ewewe</SAP_Note>
</Error_Resolution>
<errordetails/>
</innererror>
</error>');
-- DDL and sample data population, end
;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/ado/2007/08/dataservices/metadata')
SELECT c.value('(message/text())[1]','VARCHAR(100)') AS [message]
FROM @tbl
CROSS APPLY xmldata.nodes('/error') AS t(c)
WHERE ID = 1;
DECLARE @xml XML =
N'<error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">;
<code>/IWCOR/CX_DS_EDM_FACET_ERROR/005056A509B11ED1BDCCCC5E8168819D</code>
<message xml:lang="it">bla bla bla</message>
<innererror>
<transactionid>9849849</transactionid>
<timestamp>20171220145553.0525760</timestamp>
<Error_Resolution>
<SAP_Transaction>ewrerwr</SAP_Transaction>
<SAP_Note>ewewe</SAP_Note>
</Error_Resolution>
<errordetails/>
</innererror>
</error>';
;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/ado/2007/08/dataservices/metadata')
SELECT c.value('(message/text())[1]','VARCHAR(100)') AS [message]
FROM @xml.nodes('/error') AS t(c);