Retrieve the value of an element using XPath in t-sql

Question

I have a row in a table with six columns. One of the columns is called MessageContent and is an nvarchar(max) containing an XML document. I want to retrieve all six of the columns from the table:

SELECT col1
      ,col2
      ,col3
      ,MessageContent (element: ErrorMessage)
      ,col5
      ,col6
from tablea;

Is this even possible? The examples I've seen all involve returning XML and I just want the value of element: ErrorMessage.

It is possible. It would be great if you could provide a minimal reproducible sample: (1) DDL and sample data population, i.e. CREATE table() plus INSERT statements. (2) What you need to do, i.e. logic. (3) Desired output based on the sample data. (4) Your SQL Server version (SELECT @@version;) — Yitzhak Khabinsky, Jan 08 '20 at 15:00
SQL Server has a pretty good XML support. It even has a data type for xml content, called - you guessed it - XML. Cast your column to xml and follow the instructions here: https://stackoverflow.com/questions/899313/select-values-from-xml-field-in-sql-server-2008 — Zohar Peled, Jan 08 '20 at 15:34

Yitzhak Khabinsky · Answer 1 · 2020-01-08T21:05:45.177

Here is a solution. Two points to pay attention to: (1) namespace handling (2) TRY_CAST() to handle 'bad' XML in the DB table.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY(1,1) PRIMARY KEY, MessageContent NVARCHAR(MAX));
INSERT INTO @tbl
VALUES (N'<?xml version="1.0"?>
<Acknowledgement xmlns="http://localhost/MeetingBrokerServices">
    <DocumentId>60051951-8f28-47d3-8fd8-0ba89b537c87</DocumentId>
    <TransactionId>62820a70-97f5-42b0-922e-a5f0908b9c8f</TransactionId>
    <DocumentDate>2019-10-10T04:00:00.7475266Z</DocumentDate>
    <StatusCode>NPE</StatusCode>
    <Success>false</Success>
    <Errors>
        <Error>
            <Code>301</Code>
            <ErrorText>Invalid LocationIdentifier was received in the request. Valid LocationIdentifier must be populated and sent for this request to succeed. Request Details: ExternalRfpId: SecondaryExternalRfpId: RfpId: 12499772 SiteId: LocationIdentifierType: MeetingBroker LocationId: ExternalBookingId: 111.11 MbUserGuid: 625bb5f9-0bc7-4c7f-900a-a6436555ea19 RequestMetaDataGuid: BizTalk MessageId: c6e05156-4a35-4be4-b9fe-209173049539 Please see WebServiceTransactionLog and RequestMetaData for details.</ErrorText>
            <ErrorType>Critical</ErrorType>
        </Error>
    </Errors>
</Acknowledgement>');
-- DDL and sample data population, end

;WITH XMLNAMESPACES(DEFAULT 'http://localhost/MeetingBrokerServices'), rs AS 
(
    SELECT *
        , TRY_CAST(MessageContent AS XML) AS [config]
    FROM @tbl
)
SELECT ID
    , col.value('(ErrorText/text())[1]','VARCHAR(4000)') AS ErrorText
FROM rs AS tbl
    CROSS APPLY tbl.[config].nodes('/Acknowledgement/Errors/Error') AS tab(col);

Retrieve the value of an element using XPath in t-sql

1 Answers1