469,323 Members | 1,539 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,323 developers. It's quick & easy.

How to pass xml file to stored Procedure

17
I Wrote a stored procedure in SQL Server:

CREATE PROCEDURE InsertXML2 @xmlFile varchar(2000) AS
DECLARE @iTree int
SELECT *
FROM OPENXML (@iTree, 'n:Registration/n:DefaultEnrolment',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
Apr 1 '07 #1
5 14200
dorinbogdan
839 Expert 512MB
Hi,

Did you solve the problem?

If not, please try to use a stored procedure like:
Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE dbo.TestXMLIN
  2. (
  3. @XMLDOC AS TEXT
  4. )
  5. AS
  6. DECLARE @xmlHandle INT
  7.  
  8. EXEC sp_xml_preparedocument @xmlHandle OUTPUT, @xmlDoc
  9.  
  10. SELECT *
  11. FROM OPENXML (@xmlHandle, '/P/C', 2) WITH
  12. (
  13. MajorCode VARCHAR(10) './MJ',
  14. MinorCode VARCHAR(10) './MI'
  15. )
  16. )OXML
  17. EXEC sp_xml_removedocument @xmlHandle 
(the sample is taken from this link)
Apr 2 '07 #2
yingwen
17
Thank you for your reply.

I still have the problem to pass the xml file.

I knew xml file's path and file name, is there any way I can just pass these information rather than pass the content of the xml file?

Actually, I have tried to pass the the content of the xml file, but the problem is, in vb.net, I couldn't find any way to read entire file into a string.

I have tried streamreader, xmldocument, xmlreader, but they only read the text part of xml.

E.g. for a simple xml file:

<Root>
<book>title1</book>
</Root>

The output is only "title1", but I want the whole thing.

Is there any way I can do this?

Thank you again.
Apr 2 '07 #3
dorinbogdan
839 Expert 512MB
See this link.
Apr 3 '07 #4
See this link.
Hi!

I just want to ask. Instead of loading the XML file, is there a way wherein the stored procedure can directly access the contents of the file? Because I'm worried that frequent loading of the XML file (everytime a user needs some data) may affect the server's performance. What I'm currently thinking of right now is to load the contents of the file to the database. But the problem is, the contents of the XML file changes daily and I'm not sure if we are allowed to use this solution.

Can anyone advise?

Thank you very much.
Apr 22 '07 #5
dorinbogdan
839 Expert 512MB
I'm not sure if it is possible, however see if this link helps.
Apr 23 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by Zlatko Matić | last post: by
4 posts views Thread by CsharpGuy | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Gurmeet2796 | last post: by
reply views Thread by mdpf | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.