Msgstr "Error in srv_paramset." after executing sp_OAGetProperty
I am trying to get node value of type
node. It should return string
"my_type", but an empty string is returned.
declare @v_msg int
DECLARE @loadXML_result INT
declare @v_line varchar(4000)
declare @nodelist int
DECLARE @node INT
DECLARE @childnode INT
DECLARE @CHILDNODE_nodevalue VARCHAR(MAX)
set @v_line =
'<message>
<type>my_type</type>
</message>';
EXECUTE sp_OACreate 'MSXML2.DOMDocument.6.0',@v_msg OUTPUT
EXECUTE sp_OAMethod @v_msg,'loadXML',@loadXML_result OUTPUT,@v_line
EXECUTE sp_OAMethod @v_msg,'getElementsByTagName',@nodelist OUTPUT,'type'
EXECUTE sp_OAMethod @nodelist,'Item',@node OUTPUT,0
EXECUTE sp_OAGetProperty @node,'firstChild',@childnode OUTPUT
print concat('child_node ',@childnode)
EXECUTE sp_OAGetProperty @childnode,'nodeValue',@CHILDNODE_nodevalue OUTPUT
print concat ('CHILDNODE_nodevalue ',@CHILDNODE_nodevalue)
Further checking the execution status sp_OAGetProperty
indicates that an error has occurred:
DECLARE @hr INT
EXECUTE @hr = sp_OAGetProperty @node,'Text',@CHILDNODE_nodevalue OUTPUT
--Check status of the previous execution of sp_OAGetProperty
EXEC sp_OAGetErrorInfo @node
Output EXEC sp_OAGetErrorInfo @node
above:
source to share
Possible alternative:
If you can afford to use the datatype XML
and nodes()
, you can try this:
declare @v_line XML, @CHILDNODE_nodevalue VARCHAR(MAX)
set @v_line =
'<message>
<type>my_type</type>
</message>';
select @CHILDNODE_nodevalue = x.value('text()[1]', 'varchar(max)')
from @v_line.nodes('//type') as T(x)
print @CHILDNODE_nodevalue
Decision:
As for the original problem, I'm not familiar with OLE SQL Server Automation at all. But a little research shows that sp_OAGetProperty
it is somehow incompatible with the datatype VARCHAR(MAX)
as the output variable. Changing the datatype of the output variable - @CHILDNODE_nodevalue
for this case - to a fixed length VARCHAR
immediately resolves the problem for me. No further changes are required to the original request.
You can also save multiple lines EXECUTE sp_
using MSXML SelectSingleNode()
instead getElementsByTagName()
for this specific task:
declare @v_msg int
DECLARE @loadXML_result INT
declare @v_line varchar(4000)
DECLARE @node INT
DECLARE @CHILDNODE_nodevalue VARCHAR(1000)
--^^^^^^^^^^^^^
--try not to use VARCHAR(MAX)
set @v_line =
'<message>
<type>my_type</type>
</message>';
EXECUTE sp_OACreate 'MSXML2.DOMDocument.6.0',@v_msg OUTPUT
EXECUTE sp_OAMethod @v_msg,'loadXML',@loadXML_result OUTPUT,@v_line
EXECUTE sp_OAMethod @v_msg,'SelectSingleNode',@node OUTPUT,'//type'
EXECUTE sp_OAGetProperty @node,'Text',@CHILDNODE_nodevalue OUTPUT
print concat('CHILDNODE_nodevalue ',@CHILDNODE_nodevalue)
source to share