Problems with SQLXML bulk load

[vicpada]

Hi all,

I am newbie in all the stuff about xml importing into sql server.


What I try to do is simple. It is take an xml file and drop it into a
table. I am using VS2005, SQLXML 4.0 and SQL Server 2000 (I think
there is no problem of compatibility)


When I run my program using the SQLXMLBulkLoad4Class class,
everythings seems to run perfect and there is no errors. But when I
check my DB there isnt any record inserted.


My schema is:


<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="table1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ele1" type="xsd:string"/>
<xsd:element name="ele2" type="xsd:string"/>
<xsd:element name="ele3" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>


My xml:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<ENGROLE>
<EROLE>
<ele1>dieg01p</ele1>
<ele2>IE01</ele2>
<ele3>IEL01</ele3>
</EROLE>
.
.
.
</ENGROLE>


and my table definition where I try to insert:


CREATE TABLE [dbo].[table1](
[ele1] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ele2] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ele3] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
) ON [PRIMARY]


As you see it is very simple, but I cannot get it work. Can anyone
give a hand?


Thanks!!

[kenobewan]

I see the create table, but there is not connection or insertion into the database.

[vicpada]

my code from asp.net to insert is the following:

class Class1
{
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main(string[] args)
{
//
// TODO: Add code to start application here
//

try
{
SQLXMLBulkLoad4Class bulkLoad = new SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class();
bulkLoad.ErrorLogFile = @"c:\prxml\error.xml";
bulkLoad.BulkLoad = true;
bulkLoad.KeepIdentity = false;
bulkLoad.XMLFragment = true;
bulkLoad.ConnectionString = "provider=SQLOLEDB;server=server1;database=DB1;uid =user;pwd=pwd;Trust_Connection=True";
bulkLoad.Execute(@"c:\prxml\schema.xml",@"c:\prxml \caciroles_20070206034617.xml");
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
}

[kenobewan]

Here is an article that may help:
How to import XML into SQL Server with the XML Bulk Load component

[vicpada]

Thank you. I already saw it.

I found out what happened. At the was that the fields in the XML file were in capital letter whereas in the schema were in small letter.

When I changed that everything seemed to work perfectly.

:)

[tcovarru]

This is a good, very simple example that I would like to use to help me learn ASP.Net. I am more interested in bulk loading XML to SQL Server but would like to use this as an example. Not versed in this, how do I execute the code (class Class1)?