CODEWITHSUNDEEP
sql-serverxmlt-sqlxpath
Bob Frasca
Bob Frasca

Reputation: 1

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

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.

Upvotes: 0

Views: 189

Answers (1)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22187

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

Upvotes: 1

Related Questions