存储过程如下:
CREATE PROCEDURE ImportOrder @xml ntext
AS
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @xml
SELECT * INTO rders
FROM OPENXML(@idoc,'test',3)
with (OrderNO INTEGER,
state varchar(30),
Delivery varchar(100),
Dimension varchar(30),
Product varchar (30),
ProcuctID integer,
UnitPrice money,
Quantity float)
EXEC sp_xml_removedocument
GO
我想利用这个存储过程将test.xml中的信息提取出来后在数据库back中生成一个名为rders的新表
在VB中调用这个存储过程的代码如下:
Private Sub cmd_trans_Click()
Dim strXMLDim cmd
Dim con
Dim xdoc 'As MSXML2.DOMDocumentConst adCmdStoreProc = 4Set xdoc = CreateObject("MSXML2.DOMDocument")xdoc.validateOnParse = True
xdoc.async = False
xdoc.setProperty "ServerHTTPRequest", True
If (xdoc.Load("D:\test.xml")) Then
strXML = xdoc.xml
Else
MsgBox xdoc.parseError.reason
End IfSet con = CreateObject("ADODB.Connection")
con.Open "PROVIDER=SQLOLEDB;INTEGRATED SECURITY=SSPI;" & "DATA SOURCE=(local);INITIAL CATALOG=back;"
'strXML = Replace(strXML, "encoding=""utf-8""", "")
con.CursorLocation = adUseClient
Set cmd = CreateObject("ADODB.Command")With cmd
Set .ActiveConnection = con
.CommandText = "ImportOrder"
.CommandType = adCmdStoredProc
.Parameters("@xml").Value = strXML
.Execute
End With
Set xdoc = NothingSet cmd = Nothing
Set con = Nothing
MsgBox "File is created for you."
End Sub
问题是:程序运行后可以在数据库中生成一个名为rders的新表,但只有表的结构,即test.xml文件中的信息并没有写进去,不明白是为什么!!!
是我的存储过程写的有问题,还是调用过程有问题?
我是第一次接触这方面的内容,也是照着已有的例子写,有的代码还是似懂非懂,还望高手指点!!!!
CREATE PROCEDURE ImportOrder @xml ntext
AS
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @xml
SELECT * INTO rders
FROM OPENXML(@idoc,'test',3)
with (OrderNO INTEGER,
state varchar(30),
Delivery varchar(100),
Dimension varchar(30),
Product varchar (30),
ProcuctID integer,
UnitPrice money,
Quantity float)
EXEC sp_xml_removedocument
GO
我想利用这个存储过程将test.xml中的信息提取出来后在数据库back中生成一个名为rders的新表
在VB中调用这个存储过程的代码如下:
Private Sub cmd_trans_Click()
Dim strXMLDim cmd
Dim con
Dim xdoc 'As MSXML2.DOMDocumentConst adCmdStoreProc = 4Set xdoc = CreateObject("MSXML2.DOMDocument")xdoc.validateOnParse = True
xdoc.async = False
xdoc.setProperty "ServerHTTPRequest", True
If (xdoc.Load("D:\test.xml")) Then
strXML = xdoc.xml
Else
MsgBox xdoc.parseError.reason
End IfSet con = CreateObject("ADODB.Connection")
con.Open "PROVIDER=SQLOLEDB;INTEGRATED SECURITY=SSPI;" & "DATA SOURCE=(local);INITIAL CATALOG=back;"
'strXML = Replace(strXML, "encoding=""utf-8""", "")
con.CursorLocation = adUseClient
Set cmd = CreateObject("ADODB.Command")With cmd
Set .ActiveConnection = con
.CommandText = "ImportOrder"
.CommandType = adCmdStoredProc
.Parameters("@xml").Value = strXML
.Execute
End With
Set xdoc = NothingSet cmd = Nothing
Set con = Nothing
MsgBox "File is created for you."
End Sub
问题是:程序运行后可以在数据库中生成一个名为rders的新表,但只有表的结构,即test.xml文件中的信息并没有写进去,不明白是为什么!!!
是我的存储过程写的有问题,还是调用过程有问题?
我是第一次接触这方面的内容,也是照着已有的例子写,有的代码还是似懂非懂,还望高手指点!!!!
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
非常感谢你的回复!!!
那我将原来的OPENXML语句改成这样: OPENXML(@idoc,'/Inorder/Item',3)可以吗?改后的程序还是只生成一个空的数据库表格。我是想用外部定义的一个.xml文件,不想在存储过程中定义.xml文件。
我的test.xml文件源码如下:
<?xml version="1.0" encoding="GB2312"?><InOrder OrderNo="1234">
<state>未处理</state>
<OrderDate>2006-4-25</OrderDate>
<Delivery>北京快运公司</Delivery>
<Dimension>千克</Dimension>
<Item>
<Product ProductID="1" UnitPrice="18">彩电</Product>
<Quantity>2</Quantity>
</Item>
<Item>
<Product ProductID="2" UnitPrice="19">冰箱</Product>
<Quantity>2</Quantity>
</Item></InOrder>
我一直觉得在引用存储过程的时候,参数是不是写的有问题。
没有别的人回复了吗:(
--------------------------------------------------------------------
SELECT *
FROM OPENXML(@idoc,'/InOrder',3)
with (OrderNO int,
state varchar(30),
Delivery varchar(100),
Dimension varchar(30),
Product varchar (30),
ProcuctID int,
UnitPrice money,
Quantity float)
想问我加载XML文档的过程是否有问题?就是这个
If (xdoc.Load("D:\test.xml")) Then
strXML = xdoc.xml
strXML是用来作为参数@xml的值的,如果加载不成功,应该就得不到内容吧
------------------------------------------------------------------------------------
这是自然,你可以在存储过程里检查数据是否加载,如果数据为空则表示加载失败。
另外想冒昧的问一句:可以线下交流吗?我这儿上CSDN特别慢的,我们过几天就要交程序了,所以比较着急。我QQ:68573422。
AS
BEGIN
IF(@xml IS NULL)
BEGIN
PRINT '@xml is null'
RETURN
END
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @xml
SELECT * INTO rders
FROM OPENXML(@idoc,'test',3)
with (OrderNO INTEGER,
state varchar(30),
Delivery varchar(100),
Dimension varchar(30),
Product varchar (30),
ProcuctID integer,
UnitPrice money,
Quantity float) EXEC sp_xml_removedocument @idoc SELECT * FROM rders DROP TABLE rders
END
GOEXEC ImportOrder null
GODROP PROCEDURE ImportOrder
GO
Run-time error'3265'
在对应所需名称或序数的集合未找到项目然后好像是说 .Parameters("@xml").Value = strXML这句代码有问题
CREATE PROCEDURE ImportOrder @xml ntext
AS
BEGIN
IF(@xml IS NULL)
BEGIN
PRINT '@xml is null'
RETURN
ENDDECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @xml
SELECT * INTO rders
FROM OPENXML(@idoc,'/InOrder',3)
with (OrderNO INTEGER,
state varchar(30),
Delivery varchar(100),
Dimension varchar(30),
Product varchar (30),
ProcuctID integer,
UnitPrice money,
Quantity float)
EXEC sp_xml_removedocument
SELECT * FROM rders DROP TABLE rders
END
GO
然后生成的新表中就有内容了,不过我想返回的是表的所有内容,FROM OPENXML(@idoc,'/InOrder',3)只能返回<InOrder>...</InOrder>中的内容,用
FROM OPENXML(@idoc,'/',3)返回的又都是NULL,不过总算是可以返回值了,非常感谢你。