Pass XML file to SQL stored procedure

[yingwen]

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

[iburyak]

Do following :


[PHP]
EXEC InsertXML2 'text of XML File here'[/PHP]

[iburyak]

I don't think your procedure works thou.

But if yes then, sorry.

[yingwen]

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

[iburyak]

What server do you use?

[iburyak]

It should be something like this:

1. Create proc which accepts parameter as a varchar:

[PHP]CREATE PROCEDURE InsertXML
@xmlFile varchar(8000)
AS
DECLARE @iTree int

............[/PHP]


2. Call procedure from vb.net

[PHP]object.execute "InsertXML '<?xml version="1.0" ?>
<Registration>

</Registration>'"[/PHP]


You can store text of an XML File in a variable first

[PHP]object.execute "InsertXML '" & XML_Variable_Here & "'" [/PHP]


Good Luck.

[yingwen]

What server do you use?

I am using SQL Server 2000

[yingwen]

Thank you very much for your quick responses.

I have another problem here. How can I turn a XML file into a xml string.

I have tried streamreader, xmltextreader, the out put results only give me the text() part of xml file(all xml tags are not included).

In other words How can I convert xml file into
A string =
"<?xml version="1.0"?><Registration
xmlns="NZL:govt:IRD:KiwiSaver:B2BInterfaces:Regist erMember:v1.0"
xmlns:xAL="urn:oasis:names:tc:ciq:xal:3"
xmlns:xNL="urn:oasis:names:tc:ciq:xnl:3
</Registration>"

Thank you very much.

[iburyak]

Just open file like any text file, don't worry that it is an XML and get entire text into a variable.

If you use Server 2000 you have a danger to overflow maximum size of a character variable which is 8000. I managed to work around this problem but it requires some programming skills on your side.

SQL doesn't really care of any name spaces as long as it is well formed document with root node and children nodes.

I have to go now. Will talk tomorrow if you need more help.

Have a good day.

[yingwen]

Thank you very much for your reply.


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.

The problem is how can I read (more complicated than above) entire xml file into a string?

Thank you.

[iburyak]

This code works fine:

[PHP]Dim sr As StreamReader = File.OpenText("C:\test.xml")
Dim str As String = sr.ReadToEnd()[/PHP]