在Transact-SQL中使用OLE自动化对象编写XML

I try to compose XML-document using OLE Automation Objects in MS SQL SERVER.

我尝试使用MS SQL SERVER中的OLE自动化对象编写XML文档。

DECLARE @xml_out INT
DECLARE @xml_string VARCHAR(8000)
DECLARE @root_elem INT
DECLARE @parent_child_node        INT
DECLARE @child_elem INT 
DECLARE @append_child INT   

EXECUTE sp_OACreate 'MSXML2.DOMDocument.6.0',@xml_out OUTPUT

EXECUTE sp_OAMethod @xml_out,'createElement',@root_elem OUTPUT,'root'
EXECUTE sp_OAMethod @xml_out,'documentElement.appendChild',@parent_child_node OUTPUT,@root_elem 

EXEC sp_OAGetErrorInfo @parent_child_node

EXECUTE sp_OAMethod @xml_out,'createElement',@child_elem OUTPUT,'child'
EXECUTE sp_OAMethod @root_elem,'appendChild',@append_child OUTPUT,@child_elem

set @xml_string = ''
EXECUTE sp_OAGetProperty @xml_out,'xml',@xml_string OUTPUT
print 'result XML:'
print @xml_string
EXECUTE sp_OADestroy @xml_out

But I've got an error when I try to append child to my document:

但是当我尝试将子项附加到我的文档时,我遇到了错误:

So my document will not be composed. The expected result is next:

所以我的文件不会被编写。预期结果如下:

<root>
  <child/>
</root>

Could anybody say me what's wrong?

谁能说我有什么不对?

1 个解决方案

#1

You can call appendChild() method on the XML document directly without having to access documentElement property first :

您可以直接在XML文档上调用appendChild()方法,而无需先访问documentElement属性:

EXECUTE sp_OAMethod @xml_out,'appendChild',@parent_child_node OUTPUT,@root_elem