sqlsever 针对返回值过长的api ,如何解析返回值

Question

问题遇到的现象和发生背景

外部系统的接口的返回值 存储了 后端报错， 导致返回值过长，这边要求通过sql sever

INSERT INTO @xml(Content)
    EXEC @Result = sp_OAGetProperty @Status, 'responseText'

得到该 response, 但是要求从 response 的 status 做一层逻辑判断，现在没办法拆分，请各位指点一下
(ps: 不太想用程序集外挂代码实现);
返回值示例：

{
    "data":null,
    "errorCode":"DubboServiceLookup",
    "message":"kd.bos.isc.util.except.IscBizException: 
            单据反审核失败！可能存在下游数据。
            \nkd.bos.isc.util.except.IscBizException: 
            单据反审核失败！可能存在下游数据。
            \n\tat kd.bos.isc.util.script.feature.control.loop.Throw$1.eval(Throw.java:52)\n\tat kd.bos.isc.util.script.Util.eval(Util.java:116)\n\tat kd.bos.isc.util.script.statement.Block.eval(Block.java:43)\n\tat kd.bos.isc.util.script.statement.Block.eval(Block.java:36)\n\tat kd.bos.isc.util.script.Util.eval(Util.java:116)\n\tat kd.bos.isc.util.script.feature.control.decision.Decision.eval(Decision.java:47)\n\tat kd.bos.isc.util.script.Util.eval(Util.java:116)\n\tat kd.bos.isc.util.script.statement.Block.eval(Block.java:43)\n\tat kd.bos.isc.util.script.statement.Block.eval(Block.java:36)\n\tat kd.bos.isc.util.script.Util.eval(Util.java:116)\n\tat kd.bos.isc.util.script.parser.Program.eval(Program.java:65)\n\tat kd.bos.openapi.base.script.OpenApiScript.eval(OpenApiScript.java:130)\n\tat kd.bos.openapi.base.script.OpenApiScript.eval(OpenApiScript.java:122)\n\tat kd.bos.openapi.service.script.ScriptApiServiceImpl.execute(ScriptApiServiceImpl.java:35)\n\tat sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\n\tat sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)\n\tat sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\n\tat java.lang.reflect.Method.invoke(Method.java:498)\n\tat kd.bos.service.DispatchServiceImpl.invoke(DispatchServiceImpl.java:48)\n\tat com.alibaba.dubbo.common.bytecode.Wrapper1.invokeMethod(Wrapper1.java)\n\tat com.alibaba.dubbo.rpc.proxy.javassist.JavassistProxyFactory$1.doInvoke(JavassistProxyFactory.java:46)\n\tat com.alibaba.dubbo.rpc.proxy.AbstractProxyInvoker.invoke(AbstractProxyInvoker.java:72)\n\tat com.alibaba.dubbo.rpc.protocol.InvokerWrapper.invoke(InvokerWrapper.java:53)\n\tat com.alibaba.dubbo.rpc.filter.ExceptionFilter.invoke(ExceptionFilter.java:66)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat kd.bos.mservice.rpc.dubbo.filter.RequestContextFilter.lambda$invokeProviderSide$0(RequestContextFilter.java:157)\n\tat kd.bos.mservice.rpc.dubbo.debug.DubboDebugUtil.invoke(DubboDebugUtil.java:46)\n\tat kd.bos.mservice.rpc.dubbo.filter.RequestContextFilter.invokeProviderSide(RequestContextFilter.java:148)\n\tat kd.bos.mservice.rpc.dubbo.filter.RequestContextFilter.invoke(RequestContextFilter.java:62)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.filter.TimeoutFilter.invoke(TimeoutFilter.java:23)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.protocol.dubbo.filter.TraceFilter.invoke(TraceFilter.java:78)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.monitor.support.MonitorFilter.invoke(MonitorFilter.java:75)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat kd.bos.mservice.rpc.dubbo.filter.ProviderMetricFilter.invoke(ProviderMetricFilter.java:94)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat kd.bos.mservice.rpc.dubbo.filter.trace.ProviderSpanFilter.invoke(ProviderSpanFilter.java:84)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat kd.bos.mservice.circuitbreaker.filter.dubbo.CircuitbreakerFilter.lambda$invoke$0(CircuitbreakerFilter.java:34)\n\tat kd.bos.mservice.circuitbreaker.armor.ArmorCircuitbreaker.call(ArmorCircuitbreaker.java:102)\n\tat kd.bos.mservice.circuitbreaker.filter.dubbo.CircuitbreakerFilter.invoke(CircuitbreakerFilter.java:33)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat kd.bos.mservice.rpc.dubbo.filter.ThreadLifeCycleFilter.invoke(ThreadLifeCycleFilter.java:22)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.filter.ContextFilter.invoke(ContextFilter.java:70)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.filter.GenericFilter.invoke(GenericFilter.java:132)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.filter.ClassLoaderFilter.invoke(ClassLoaderFilter.java:38)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.filter.EchoFilter.invoke(EchoFilter.java:38)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.protocol.dubbo.DubboProtocol$1.reply(DubboProtocol.java:89)\n\tat com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.handleRequest(HeaderExchangeHandler.java:102)\n\tat com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.received(HeaderExchangeHandler.java:264)\n\tat com.alibaba.dubbo.remoting.transport.DecodeHandler.received(DecodeHandler.java:52)\n\tat com.alibaba.dubbo.remoting.transport.dispatcher.ChannelEventRunnable.run(ChannelEventRunnable.java:92)\n\tat java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)\n\tat java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)\n\tat java.lang.Thread.run(Thread.java:748)\n,TraceId：428c1cf5fdee720e   \nWebInstance：web-4650852774   \nTime:2022/09/09 10:16:58   \n====Stack====   \njava.lang.RuntimeException: kd.bos.isc.util.except.IscBizException: 单据反审核失败！可能存在下游数据。\nkd.bos.isc.util.except.IscBizException: 单据反审核失败！可能存在下游数据。\n\tat kd.bos.isc.util.script.feature.control.loop.Throw$1.eval(Throw.java:52)\n\tat kd.bos.isc.util.script.Util.eval(Util.java:116)\n\tat kd.bos.isc.util.script.statement.Block.eval(Block.java:43)\n\tat kd.bos.isc.util.script.statement.Block.eval(Block.java:36)\n\tat kd.bos.isc.util.script.Util.eval(Util.java:116)\n\tat kd.bos.isc.util.script.feature.control.decision.Decision.eval(Decision.java:47)\n\tat kd.bos.isc.util.script.Util.eval(Util.java:116)\n\tat kd.bos.isc.util.script.statement.Block.eval(Block.java:43)\n\tat kd.bos.isc.util.script.statement.Block.eval(Block.java:36)\n\tat kd.bos.isc.util.script.Util.eval(Util.java:116)\n\tat kd.bos.isc.util.script.parser.Program.eval(Program.java:65)\n\tat kd.bos.openapi.base.script.OpenApiScript.eval(OpenApiScript.java:130)\n\tat kd.bos.openapi.base.script.OpenApiScript.eval(OpenApiScript.java:122)\n\tat kd.bos.openapi.service.script.ScriptApiServiceImpl.execute(ScriptApiServiceImpl.java:35)\n\tat sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\n\tat sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)\n\tat sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\n\tat java.lang.reflect.Method.invoke(Method.java:498)\n\tat kd.bos.service.DispatchServiceImpl.invoke(DispatchServiceImpl.java:48)\n\tat com.alibaba.dubbo.common.bytecode.Wrapper1.invokeMethod(Wrapper1.java)\n\tat com.alibaba.dubbo.rpc.proxy.javassist.JavassistProxyFactory$1.doInvoke(JavassistProxyFactory.java:46)\n\tat com.alibaba.dubbo.rpc.proxy.AbstractProxyInvoker.invoke(AbstractProxyInvoker.java:72)\n\tat com.alibaba.dubbo.rpc.protocol.InvokerWrapper.invoke(InvokerWrapper.java:53)\n\tat com.alibaba.dubbo.rpc.filter.ExceptionFilter.invoke(ExceptionFilter.java:66)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat kd.bos.mservice.rpc.dubbo.filter.RequestContextFilter.lambda$invokeProviderSide$0(RequestContextFilter.java:157)\n\tat kd.bos.mservice.rpc.dubbo.debug.DubboDebugUtil.invoke(DubboDebugUtil.java:46)\n\tat kd.bos.mservice.rpc.dubbo.filter.RequestContextFilter.invokeProviderSide(RequestContextFilter.java:148)\n\tat kd.bos.mservice.rpc.dubbo.filter.RequestContextFilter.invoke(RequestContextFilter.java:62)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.filter.TimeoutFilter.invoke(TimeoutFilter.java:23)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.protocol.dubbo.filter.TraceFilter.invoke(TraceFilter.java:78)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.monitor.support.MonitorFilter.invoke(MonitorFilter.java:75)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat kd.bos.mservice.rpc.dubbo.filter.ProviderMetricFilter.invoke(ProviderMetricFilter.java:94)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat kd.bos.mservice.rpc.dubbo.filter.trace.ProviderSpanFilter.invoke(ProviderSpanFilter.java:84)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat kd.bos.mservice.circuitbreaker.filter.dubbo.CircuitbreakerFilter.lambda$invoke$0(CircuitbreakerFilter.java:34)\n\tat kd.bos.mservice.circuitbreaker.armor.ArmorCircuitbreaker.call(ArmorCircuitbreaker.java:102)\n\tat kd.bos.mservice.circuitbreaker.filter.dubbo.CircuitbreakerFilter.invoke(CircuitbreakerFilter.java:33)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat kd.bos.mservice.rpc.dubbo.filter.ThreadLifeCycleFilter.invoke(ThreadLifeCycleFilter.java:22)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.filter.ContextFilter.invoke(ContextFilter.java:70)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.filter.GenericFilter.invoke(GenericFilter.java:132)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.filter.ClassLoaderFilter.invoke(ClassLoaderFilter.java:38)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.filter.EchoFilter.invoke(EchoFilter.java:38)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.protocol.dubbo.DubboProtocol$1.reply(DubboProtocol.java:89)\n\tat com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.handleRequest(HeaderExchangeHandler.java:102)\n\tat com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.received(HeaderExchangeHandler.java:264)\n\tat com.alibaba.dubbo.remoting.transport.DecodeHandler.received(DecodeHandler.java:52)\n\tat com.alibaba.dubbo.remoting.transport.dispatcher.ChannelEventRunnable.run(ChannelEventRunnable.java:92)\n\tat java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)\n\tat java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)\n\tat java.lang.Thread.run(Thread.java:748)\n\n\tat com.alibaba.dubbo.rpc.filter.ExceptionFilter.invoke(ExceptionFilter.java:115)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat kd.bos.mservice.rpc.dubbo.filter.RequestContextFilter.lambda$invokeProviderSide$0(RequestContextFilter.java:157)\n\tat kd.bos.mservice.rpc.dubbo.debug.DubboDebugUtil.invoke(DubboDebugUtil.java:46)\n\tat kd.bos.mservice.rpc.dubbo.filter.RequestContextFilter.invokeProviderSide(RequestContextFilter.java:148)\n\tat kd.bos.mservice.rpc.dubbo.filter.RequestContextFilter.invoke(RequestContextFilter.java:62)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.filter.TimeoutFilter.invoke(TimeoutFilter.java:23)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.protocol.dubbo.filter.TraceFilter.invoke(TraceFilter.java:78)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.monitor.support.MonitorFilter.invoke(MonitorFilter.java:75)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat kd.bos.mservice.rpc.dubbo.filter.ProviderMetricFilter.invoke(ProviderMetricFilter.java:94)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat kd.bos.mservice.rpc.dubbo.filter.trace.ProviderSpanFilter.invoke(ProviderSpanFilter.java:84)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat kd.bos.mservice.circuitbreaker.filter.dubbo.CircuitbreakerFilter.lambda$invoke$0(CircuitbreakerFilter.java:34)\n\tat kd.bos.mservice.circuitbreaker.armor.ArmorCircuitbreaker.call(ArmorCircuitbreaker.java:102)\n\tat kd.bos.mservice.circuitbreaker.filter.dubbo.CircuitbreakerFilter.invoke(CircuitbreakerFilter.java:33)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat kd.bos.mservice.rpc.dubbo.filter.ThreadLifeCycleFilter.invoke(ThreadLifeCycleFilter.java:22)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.filter.ContextFilter.invoke(ContextFilter.java:70)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.filter.GenericFilter.invoke(GenericFilter.java:132)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.filter.ClassLoaderFilter.invoke(ClassLoaderFilter.java:38)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.filter.EchoFilter.invoke(EchoFilter.java:38)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.protocol.dubbo.DubboProtocol$1.reply(DubboProtocol.java:89)\n\tat com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.handleRequest(HeaderExchangeHandler.java:102)\n\tat com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.received(HeaderExchangeHandler.java:264)\n\tat com.alibaba.dubbo.remoting.transport.DecodeHandler.received(DecodeHandler.java:52)\n\tat com.alibaba.dubbo.remoting.transport.dispatcher.ChannelEventRunnable.run(ChannelEventRunnable.java:92)\n\tat java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)\n\tat java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)\n\tat java.lang.Thread.run(Thread.java:748)\n",
    "status":false
}

Answer 1

substring 行不行