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

Alen Cappelletti 686 Reputation points
2020-09-16T15:03:10.323+00:00

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

No comments
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 20,011 Reputation points
    2020-09-16T16:29:28.947+00:00

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

2 additional answers

Sort by: Most helpful
  1. MelissaMa-MSFT 24,121 Reputation points Microsoft Employee
    2020-09-17T02:32:41.157+00:00

    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.


  2. Alen Cappelletti 686 Reputation points
    2020-09-30T12:06:55.307+00:00

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

    No comments