如何在Microsoft Sql中获得' ResponseDescription '值?

Question

我要查询的列的XML数据是这样的

<soap:Envelope xmlns:soap="http://google.com/">
<soap:Body>
<ns2:response xmlns:ns2="http://stackoverflow.com/">
<trackingId>1375321435</trackingId>
<responseCode>202</responseCode>
<responseDescription>Request completed successfully</responseDescription>
<detail>&lt;?xml version="1.0" encoding="UTF-8" standalone="yes"?> &lt;LoanProcessResponse>     &lt;ResponseCode>E4284&lt;/ResponseCode>     &lt;ResponseDescription>The minimum value date allowed for this account must be greater than [08-12-2022].: acctDisburseTranLA.valueDate&lt;/ResponseDescription>     &lt;status>FAILURE&lt;/status> &lt;/LoanProcessResponse></detail>
</ns2:response>
</soap:Body>
</soap:Envelope>

and my query looks like the below

WITH XMLNAMESPACES('http://google.com/' AS soap, 'http://stackoverflow.com/' AS ns2)
SELECT
timeSent, 
accountNo,
isSuccessful,
try_convert(xml, responsePayload).query('/soap:Envelope/soap:Body/ns2:response/detail/LoanProcessResponse/ResponseDescription') AS response
from my_table
where accountNo = '000000008'
ORDER BY timesent desc;

Can you help me get the value of the  <ResponseDescription> without the tag?

Answer 1

请尝试以下解决方案。

最好对responsePayload使用XML数据类型列。

/p>

-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, responsePayload NVARCHAR(MAX));
INSERT @tbl (responsePayload) VALUES
(N'<soap:Envelope xmlns:soap="http://google.com/">
<soap:Body>
<ns2:response xmlns:ns2="http://stackoverflow.com/">
<trackingId>1375321435</trackingId>
<responseCode>202</responseCode>
<responseDescription>Request completed successfully</responseDescription>
<detail>&lt;?xml version="1.0" encoding="UTF-8" standalone="yes"?> &lt;LoanProcessResponse>     &lt;ResponseCode>E4284&lt;/ResponseCode>     &lt;ResponseDescription>The minimum value date allowed for this account must be greater than [08-12-2022].: acctDisburseTranLA.valueDate&lt;/ResponseDescription>     &lt;status>FAILURE&lt;/status> &lt;/LoanProcessResponse></detail>
</ns2:response>
</soap:Body>
</soap:Envelope>');
-- DDL and sample data population, end
;WITH XMLNAMESPACES('http://google.com/' AS soap, 'http://stackoverflow.com/' AS ns2)
SELECT ID 
, responseDescription = TRY_CAST(responsePayload AS XML)
.value('(/soap:Envelope/soap:Body/ns2:response/responseDescription/text())[1]', 'VARCHAR(200)')
FROM @tbl;

ID	responseDescription
1	请求成功完成