将XML文件传递给SQL存储过程 [英] Pass XML file to SQL stored procedure
本文介绍了将XML文件传递给SQL存储过程的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在SQL Server中编写了一个存储过程:
CREATE PROCEDURE InsertXML2 @xmlFile varchar(2000)AS
DECLARE @iTree int
SELECT *
来自OPENXML(@iTree,'n:Registration / nefaultEnrolment'',3)
WITH(
....
)
EXEC sp_xml_removedocument @iTree
GO
我怎样才能通过这个存储过程在VB.NET中的XML文件?
非常感谢
I Wrote a stored procedure in SQL Server:
CREATE PROCEDURE InsertXML2 @xmlFile varchar(2000) AS
DECLARE @iTree int
SELECT *
FROM OPENXML (@iTree, ''n:Registration/nefaultEnrolment'',3)
WITH (
....
)
EXEC sp_xml_removedocument @iTree
GO
How can I pass the XML file to this stored Procedure in VB.NET?
Thanks a lot
推荐答案
请执行以下操作:
[PHP]
EXEC InsertXML2''XML文件文本''[/ PHP]
Do following :
[PHP]
EXEC InsertXML2 ''text of XML File here''[/PHP]
我不认为你的程序有效。
但如果是,那么抱歉。
I don''t think your procedure works thou.
But if yes then, sorry.
它可以在以下代码中正常工作:
CREATE PROCEDURE InsertXML AS
DECLARE @iTree int
DECLARE @xmlFile varchar(2000)
SET @xmlFile =''<?xml version =" 1.0" ?>
<注册
< /注册>''
- 创建一个内部XML文档的表示。
EXEC sp_xml_preparedocument @iTree OUTPUT,@ xmlFile,
''< root xmlns:n =" NZL:govt:IRD:KiwiSaver: B2BInterfaces:Regi sterMember:v1.0"
xmlns:xAL =" urn:oasis:names:tc:ciq:xal:3"
xmlns:xNL =" urn:oasis:names:tc:ciq:xnl:3"
xmlns:xPIL =" urn:oasis:names:tc: CIQ:xpil:3英寸
xmlns:xsi =" http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation =" NZL:govt:IRD:KiwiSaver:B2BInte rfaces:RegisterMember:v1.0 ../RegisterMember.xsd"/>''
- 执行使用OPENXML行集提供程序的SELECT语句。
SELECT *
FROM OPENXML(@iTree,'n:Registration / n:DefaultEnrolment '',3)
WITH(
DateSent datetime''。/ @ DateSent'',
Number decimal''n:Scheme / @Number'',
ActiveType char(2)''n:Scheme / @ActiveType'',
AddressLine1 varchar(30)''child :: n:MemberAddress / xAL:FreeTextAddress / xAL:AddressLine [position()= 1]'',
AddressLine2 varchar(30)''child :: n:MemberAddress / xAL:FreeTextAddress / xAL:AddressLine [position( } = 2]'',
BirthDateTime datetime''xPIL:BirthInfo / @ xPIL:BirthDateTime'',
EmployerName varchar(20)''n :雇主/ n:雇主/ n:OrganizationName / xNL:NameElement / text()''
)
EXE C sp_xml_removedocument @iTree
GO
如何将xml文件传递到
CREATE PROCEDURE InsertXML @fileName varchar(2000)在VB.NET中
?
提前致谢
It works fine in following code:
CREATE PROCEDURE InsertXML AS
DECLARE @iTree int
DECLARE @xmlFile varchar(2000)
SET @xmlFile=''<?xml version="1.0" ?>
<Registration
</Registration>''
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @iTree OUTPUT, @xmlFile,
''<root xmlns:n="NZL:govt:IRD:KiwiSaver:B2BInterfaces:Regi sterMember:v1.0"
xmlns:xAL="urn:oasis:names:tc:ciq:xal:3"
xmlns:xNL="urn:oasis:names:tc:ciq:xnl:3"
xmlns:xPIL="urn:oasis:names:tc:ciq:xpil:3"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="NZL:govt:IRD:KiwiSaver:B2BInte rfaces:RegisterMember:v1.0 ../RegisterMember.xsd"/>''
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@iTree, ''n:Registration/n:DefaultEnrolment'',3)
WITH (
DateSent datetime ''./@DateSent'',
Number decimal ''n:Scheme/@Number'',
ActiveType char(2) ''n:Scheme/@ActiveType'',
AddressLine1 varchar(30) ''child::n:MemberAddress/xAL:FreeTextAddress/xAL:AddressLine[position() = 1]'',
AddressLine2 varchar(30) ''child::n:MemberAddress/xAL:FreeTextAddress/xAL:AddressLine[position() = 2]'',
BirthDateTime datetime ''xPIL:BirthInfo/@xPIL:BirthDateTime'',
EmployerName varchar(20) ''n:Employers/n:Employer/n:OrganizationName/xNL:NameElement/text()''
)
EXEC sp_xml_removedocument @iTree
GO
How can I pass the xml file into
CREATE PROCEDURE InsertXML @fileName varchar(2000) AS
in VB.NET?
Thanks in advance
这篇关于将XML文件传递给SQL存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文