Extract node value from XML field stored like nvarchar(max)

Question

HI all,
sincerely I'm not an expert of XPath and the use of XML into a t-sql statement.

I got a sql query that could be
SELECT
a.response /* nvarchar(max) field... it's a XML in origin */
FROM [db].[dbo].[errors] a
WHERE a.id = 3711
order by id desc

the result id casted as XML directly is:

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

My goal is to have the value of <message> TAG... "bla bla bla"

I tried with

DECLARE @XML as xml;
SELECT
@XML = CAST(a.response as XML)
FROM [db].[dbo].[errors] a
WHERE a.id = 3711
order by id desc

SELECT @XML .query('/error/message[last()]') as v

My result is BLANC.

Thanks in advice, ALEN

Answer 1

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);  

Answer 2

Hi @Alen Cappelletti ,

Please also refer below:

;WITH    XMLNAMESPACES ('http://schemas.microsoft.com/ado/2007/08/dataservices/metadata' AS m)  
SELECT  @xml.query('data(/*/m:message)') as v  

Output:
bla bla bla

You could declare the namespaces using WITH XMLNAMESPACES.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

And another example more confusion for me...
XML come from a POST... into a DB.
Now I've extracted only a single row.

DECLARE @XML XML =
N'<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<env:Header />
<env:Body>
<getConsegna_Ritiro xmlns="http://www.boomi.com/connector/wss">
<elementRequest>
<Sistema>SFDC</Sistema>
<Processo>CONSEGNA</Processo>
<Causale />
<IdWorkOrder>0WO4I000000QZuQWAW</IdWorkOrder>
<IdCase />
<IdOrdine>a0i4I00000FvecQQAR</IdOrdine>
<ListWorkOrderItem>
<TipologiaProdotto>Smart Home</TipologiaProdotto>
<NomeProdotto>Kit Illuminazione intelligente Premium</NomeProdotto>
<CodiceArticolo>SKU-A-00000907</CodiceArticolo>
<Quantita>1</Quantita>
</ListWorkOrderItem>
</elementRequest>
</getConsegna_Ritiro>
</env:Body>
</env:Envelope>'
--SELECT @XML

/*
Which namespace I have to use when like in this example I have 3 in header and 1 in body...
I tried with the one in body 'cause I need the values in these nodes
<NomeProdotto>Kit Illuminazione intelligente Premium</NomeProdotto>
<CodiceArticolo>SKU-A-00000907</CodiceArticolo>
but it's more complex this kind of XML and I don't know how to access

*/
;WITH XMLNAMESPACES (DEFAULT 'http://www.boomi.com/connector/wss')
SELECT c.value('(getConsegna_Ritiro/elementRequest/ListWorkOrderItem/CodiceArticolo/text())[1]','VARCHAR(100)') AS [message]
FROM @XML .nodes('/env') AS t(c);