Insert XML into SQL Database table

[abathurst]

Hello,

I am having trouble inserting data from a XML file into a table in a SQL data base. The worst thing is it isn't coming up with an error but nothing is being inserted into the database

Here is the condensed version of the XML code I am using to test with:

<?xml version="1.0" encoding="UTF-8"?>
<propertyList date="2009-01-01-12:30:00" username="xmluser" password="xmlpassword">
	<!-- Current listing -->
	<residential modTime="2009-01-01-12:30:00" status="current">
		<agentID>XNWXNW</agentID>
		<uniqueID>ABCD1234</uniqueID>
		<authority value="exclusive"/>
		<underOffer value="no"/>
		<listingAgent>
			<name>Mr. John Doe</name>
			<telephone type="BH">05 1234 5678</telephone>
			<email>[e-mail]</email>
		</listingAgent>
		<listingAgent>
			<name>Mrs Jane Dow</name>
			<telephone type="BH">05 1234 5678</telephone>
			<email>[e-mail]</email>
		</listingAgent>
	</residential>
</propertyList>

And the "insert" code:

Imports System.IO
Imports System.Data
Imports System.Xml
Imports System.Data.SqlClient

Partial Class InsertXML
    Inherits System.Web.UI.Page

    Private Sub InsertXML()

        Dim connetionString As String
        Dim command As SqlCommand
        Dim adpter As New SqlDataAdapter
        Dim ds As New DataSet
        Dim xmlFile As XmlReader
        Dim sql As String

        Dim name As String
        Dim telephone As String
        Dim email As Double

        Dim myConnection As SqlConnection = New SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))

        xmlFile = XmlReader.Create("sample1.xml", New XmlReaderSettings())
        ds.ReadXml(xmlFile)
        Dim i As Integer
        myConnection.Open()
        For i = 0 To ds.Tables(0).Rows.Count - 1
            name = ds.Tables(0).Rows(i).Item(0)
            telephone = ds.Tables(0).Rows(i).Item(1)
            email = ds.Tables(0).Rows(i).Item(2)
            sql = "insert into Realestate values(" & name & ",'" & telephone & "'," & email & ")"
            command = New SqlCommand(sql, myConnection)
            adpter.InsertCommand = command
            adpter.InsertCommand.ExecuteNonQuery()
        Next
        myConnection.Close()
    End Sub
End Class

As I said there isn't any error coming up so im completely stumped...

Any help would be great.

[hericles]

I'd check the values you are getting out of the data set (name, telephone, email) and confirm they aren't empty strings.

[abathurst]

Thanks hericles. I found out that it wasn't finding the xml file at all so I put in the direct path "C:\...\sample1.xml" and now I'm coming up with this error:

For security reasons DTD is prohibited in this XML document. To enable DTD processing set the ProhibitDtd property on XmlReaderSettings to false and pass the settings into XmlReader.Create method. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Xml.XmlException: For security reasons DTD is prohibited in this XML document. To enable DTD processing set the ProhibitDtd property on XmlReaderSettings to false and pass the settings into XmlReader.Create method.

Source Error: 


Line 45: 
Line 46:         xmlFile = XmlReader.Create("C:\Inetpub\clients.rangemedia.com.au\GDL\sample1.xml", New XmlReaderSettings())
Line 47:         ds.ReadXml(xmlFile)
Line 48:         Dim i As Integer
Line 49:         myConnection.Open()

[hericles]

try creating your settings rather than using new:

XmlReaderSettings settings = new XmlReaderSettings();
settings.ValidationType = ValidationType.Schema;

xmlFile = XmlReader.Create("sample1.xml", settings)

See if that helps.

[abathurst]

Its coming up with a different error message now.

Compilation Error 
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. 

Compiler Error Message: BC30108: 'XmlReaderSettings' is a type and cannot be used as an expression.

Source Error:

 

Line 44:         Dim myConnection As SqlConnection = New SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))
Line 45: 
Line 46:         XmlReaderSettings(settings = New XmlReaderSettings())
Line 47:         settings.ValidationType = ValidationType.Schema
Line 48:

AND this is the code:

Private Sub InsertXML()

        Dim connetionString As String
        Dim command As SqlCommand
        Dim adpter As New SqlDataAdapter
        Dim ds As New DataSet
        Dim xmlFile As XmlReader
        Dim sql As String

        Dim name As String
        Dim telephone As String
        Dim email As Double

        Dim myConnection As SqlConnection = New SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))

        XmlReaderSettings(settings = New XmlReaderSettings())
        settings.ValidationType = ValidationType.Schema

        xmlFile = XmlReader.Create("C:\Inetpub\clients.rangemedia.com.au\GDL\sample1.xml", settings)
        ds.ReadXml(xmlFile)
        Dim i As Integer
        myConnection.Open()
        For i = 0 To ds.Tables(0).Rows.Count - 1
            name = ds.Tables(0).Rows(i).Item(0)
            telephone = ds.Tables(0).Rows(i).Item(1)
            email = ds.Tables(0).Rows(i).Item(2)
            sql = "insert into Realestate values(" & name & ",'" & telephone & "'," & email & ")"
            command = New SqlCommand(sql, myConnection)
            adpter.InsertCommand = command
            adpter.InsertCommand.ExecuteNonQuery()
        Next
        myConnection.Close()
    End Sub
End Class

[hericles]

You have altered the code I posted up from this:

XmlReaderSettings settings = new XmlReaderSettings();

to this:

XmlReaderSettings (settings = new XmlReaderSettings());

Remove the brackets around the settings and it should work.

[abathurst]

Yeah I changed that back. Visual Studio added it in when i accidently deleted the ";" at the end.

It's still coming up with the same error:

Compilation Error 
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. 

Compiler Error Message: BC30108: 'XmlReaderSettings' is a type and cannot be used as an expression.

Source Error:

 

Line 44:         Dim myConnection As SqlConnection = New SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))
Line 45: 
Line 46:         XmlReaderSettings settings = New XmlReaderSettings();
Line 47:         settings.ValidationType = ValidationType.Schema;
Line 48:

[hericles]

OMG, I gave you C# code for a VB.net project... Sorry about that. I only just noticed what language you were using.

Dim settings As New XmlReaderSettings()
settings.ValidationType = ValidationType.Schema

That will most likely fix your problem.

[abathurst]

No its my fault for not explaining what code I was using.

I changed the code as per your above post but coming up with the same error message as at the start.

I'll post both the error message and code and highlight the troubled area.

For security reasons DTD is prohibited in this XML document. To enable DTD processing set the ProhibitDtd property on XmlReaderSettings to false and pass the settings into XmlReader.Create method. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Xml.XmlException: For security reasons DTD is prohibited in this XML document. To enable DTD processing set the ProhibitDtd property on XmlReaderSettings to false and pass the settings into XmlReader.Create method.

Source Error: 


Line 26: 
Line 27:         xmlFile = XmlReader.Create("C:\Inetpub\clients.rangemedia.com.au\GDL\sample1.xml", settings)
Line 28:         ds.ReadXml(xmlFile)
Line 29:         Dim i As Integer
Line 30:         myConnection.Open()

Imports System.IO
Imports System.Data
Imports System.Xml
Imports System.Data.SqlClient

Partial Class InsertXML
    Inherits System.Web.UI.Page

    Private Sub InsertXML(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim connetionString As String
        Dim command As SqlCommand
        Dim adpter As New SqlDataAdapter
        Dim ds As New DataSet
        Dim xmlFile As XmlReader
        Dim sql As String

        Dim name As String
        Dim telephone As String
        Dim email As Double

        Dim myConnection As SqlConnection = New SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))

        Dim settings As New XmlReaderSettings()
        settings.ValidationType = ValidationType.Schema

        xmlFile = XmlReader.Create("C:\Inetpub\clients.rangemedia.com.au\GDL\sample1.xml", settings)
        ds.ReadXml(xmlFile)        
        Dim i As Integer
        myConnection.Open()
        For i = 0 To ds.Tables(0).Rows.Count - 1
            name = ds.Tables(0).Rows(i).Item(0)
            telephone = ds.Tables(0).Rows(i).Item(1)
            email = ds.Tables(0).Rows(i).Item(2)
            sql = "insert into Realestate values(" & name & ",'" & telephone & "'," & email & ")"
            command = New SqlCommand(sql, myConnection)
            adpter.InsertCommand = command
            adpter.InsertCommand.ExecuteNonQuery()
        Next
        myConnection.Close()
    End Sub

End Class

[hericles]

What version of VS are you using? i only ask because the ProhibitDtd method is obsolete according to MSDN. There isn't any DTD declarations in your XML are there? The sample you posted up didn't include any DTD schema.

[abathurst]

I am using Visual Studio 2005.

No what's above is what I am currenly using for the xml.

The original which I cut down just for testing did have this in it:

<!DOCTYPE propertyList SYSTEM "http://reaxml.realestate.com.au/propertyList.dtd">

and when that is added to the one i am currently using the same error still occurs

[hericles]

OK, MSDN says you can set the ProhibitDTD property to false and pass through the settings yourself to the create() method. Also the create() method can accept nothing as the settings.
So, if you set
settings.ProhibitDtd = false

and then pass the settings into the create() method - which doesn't require a change to your code - it might work. You can read more (but not much) here:
http://msdn.microsoft.com/en-us/library/system.xml.xmlreadersettings.prohibitdtd.aspx

Hope that helps, I want to know what fixes it now too:)

[abathurst]

Well it fixed that problem, but then came up with another error:

Input string was not in a correct format. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.FormatException: Input string was not in a correct format.

Source Error: 


Line 33:             name = ds.Tables(0).Rows(i).Item(0)
Line 34:             telephone = ds.Tables(0).Rows(i).Item(1)
Line 35:             email = ds.Tables(0).Rows(i).Item(2)
Line 36:             sql = "insert into RealestateTest values(" & name & ",'" & telephone & "'," & email & ")"
Line 37:             command = New SqlCommand(sql, myConnection)

[hericles]

If you go back and check your variable declaration you have email declared as a double when it should be a string.

[abathurst]

How silly of me.

Its coming up with a whole new bunch of errors. The first was this on:

Cannot find column 1. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.IndexOutOfRangeException: Cannot find column 1.

Source Error: 


Line 32:         For i = 0 To ds.Tables(0).Rows.Count - 1
Line 33:             name = ds.Tables(0).Rows(i).Item(0)
Line 34:             telephone = ds.Tables(0).Rows(i).Item(1)Line 35:             email = ds.Tables(0).Rows(i).Item(2)
Line 36:             sql = "insert into Realestate values(" & name & ",'" & telephone & "'," & email & ")"

I had some other little errors that i fixed and this is now the xml code:

<?xml version="1.0" encoding="UTF-8"?>
<propertyList>
	<RealestateTest>
		<listingAgent>
			<name>Mr. John Doe</name>
			<telephone type="BH">05 1234 5678</telephone>
			<email>[e-mail]</email>
		</listingAgent>
		<listingAgent>
			<name>Mrs Jane Dow</name>
			<telephone type="BH">05 1234 5678</telephone>
			<email>[e-mail]</email>
		</listingAgent>
	</RealestateTest>
</propertyList>

and the vb.net code:

Imports System.IO
Imports System.Data
Imports System.Xml
Imports System.Data.SqlClient

Partial Class InsertXML
    Inherits System.Web.UI.Page

    Private Sub InsertXML(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim connetionString As String
        Dim command As SqlCommand
        Dim adpter As New SqlDataAdapter
        Dim ds As New DataSet
        Dim xmlFile As XmlReader
        Dim sql As String

        Dim name As String
        Dim telephone As String
        Dim email As String

        Dim myConnection As SqlConnection = New SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))

        Dim settings As New XmlReaderSettings()
        settings.ValidationType = ValidationType.Schema
        settings.ProhibitDtd = False

        xmlFile = XmlReader.Create("C:\Inetpub\clients.rangemedia.com.au\GDL\xml\sample1.xml", settings)
        ds.ReadXml(xmlFile)
        Dim i As Integer
        myConnection.Open()
        For i = 0 To ds.Tables(0).Rows.Count - 1
            name = ds.Tables(0).Rows(i).Item("name")
            telephone = ds.Tables(0).Rows(i).Item("telephone")
            email = ds.Tables(0).Rows(i).Item("email")
            sql = "insert into RealestateTest values(" & name & ",'" & telephone & "'," & email & ")"
            command = New SqlCommand(sql, myConnection)
            adpter.InsertCommand = command
            adpter.InsertCommand.ExecuteNonQuery()
        Next
        myConnection.Close()
    End Sub

End Class

And I even created a new table called "RealestateTest" where there are only three columns name, telephone, email all varchar(100) and I'm getting this error:

Column 'name' does not belong to table RealestateTest. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.ArgumentException: Column 'name' does not belong to table RealestateTest.

Source Error: 


Line 31:         myConnection.Open()
Line 32:         For i = 0 To ds.Tables(0).Rows.Count - 1
Line 33:             name = ds.Tables(0).Rows(i).Item("name")
Line 34:             telephone = ds.Tables(0).Rows(i).Item("telephone")
Line 35:             email = ds.Tables(0).Rows(i).Item("email")

I'm completely stumped as to why this is happening? sorry to be such a pain.

[stbuchok]

Take a look at OpenXML - http://msdn.microsoft.com/en-us/library/ms186918.aspx

Use a stored procedure similar to this one (replace everything that looks like <<something>>):

Use <<DataBaseName>>
Go

If Exists (Select 1 From dbo.SysObjects Where ID = Object_ID(N'dbo.<<StoredProcedureName>>') And ObjectProperty(ID, N'IsProcedure') = 1) 
Begin
	Print 'Drop Procedure <<StoredProcedureName>>'
	Drop Procedure dbo.<<StoredProcedureName>>
End
Go

Print 'Creating Procedure <<StoredProcedureName>>'

Set Quoted_Identifier Off 
Set Ansi_Nulls On 
Go

Create Procedure dbo.<<StoredProcedureName>>
	@XMLString	NText
As

        Declare	@iDoc	Int

	Exec sp_xml_preparedocument @iDoc Output, @XMLString
	
	Insert Into <<TableName>>
			(param1, param2, param3, param4)
	Select	param1, param2, param3, param4
	From	OpenXml(@iDoc, '/root/node')
			With(param1 datatype,
			     param2 datatype,
			     param3 datatype,
			     param4 datatype)
	
	Exec sp_xml_removedocument @iDoc


Go

Grant Exec On dbo.<<StoredProcedureName>> To <<UserName>>
Go

[abathurst]

Alright, so this is what I came up with. It's not coming up with any errors but nothing is being passed into the table. I've tried playing with a few things with no result.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER Procedure [dbo].[InsertXML]
	@XMLString	NText
As

        Declare	@iDoc	int

	Exec sp_xml_preparedocument @iDoc Output, @XMLString
	
	Insert Into RealestateTest
			(name, telephone, email)
	Select	name, telephone, email
	From	OpenXml(@iDoc, '/root/node')
			With(name varchar(50),
			     telephone varchar(50),
			     email varchar(50))
	
	Exec sp_xml_removedocument @iDoc

Go

Grant Exec On dbo.InsertXML To ma

Go

[stbuchok]

Sorry, the 'root/node' will need to be changed to match your XML. Like I said, you'll want to read up on OpenXML for SQL in order to better understand how it works.

[abathurst]

Yeah I did think that was the case and did try changing the 'root/node'. Well, I'll read more on it and try to get it working. Thanks for all your help.

[crishlay]

Please try below link for your XML data insert problem

http://www.codeproject.com/KB/XML/csreadxml1.aspx