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 SQLXMLBulkLoad4 Class 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:complexTyp e>
<xsd:sequence >
<xsd:element name="ele1" type="xsd:strin g"/>
<xsd:element name="ele2" type="xsd:strin g"/>
<xsd:element name="ele3" type="xsd:strin g"/>
</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_Gene ral_CP1_CI_AS NULL,
[ele2] [nvarchar](255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL,
[ele3] [nvarchar](255) COLLATE SQL_Latin1_Gene ral_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
{
SQLXMLBulkLoad4 Class bulkLoad = new SQLXMLBULKLOADL ib.SQLXMLBulkLo ad4Class();
bulkLoad.ErrorL ogFile = @"c:\prxml\erro r.xml";
bulkLoad.BulkLo ad = true;
bulkLoad.KeepId entity = false;
bulkLoad.XMLFra gment = true;
bulkLoad.Connec tionString = "provider=SQLOL EDB;server=serv er1;database=DB 1;uid=user;pwd= pwd;Trust_Conne ction=True";
bulkLoad.Execut e(@"c:\prxml\sc hema.xml",@"c:\ prxml\caciroles _20070206034617 .xml");
}
catch (Exception ex)
{
Console.WriteLi ne(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)?