Loadingf of XML file to SQL Server

[johnnykenshien]

hello to all,

Im trying to load xml file to sql server but I dont have enough knowledge how to create schema xml, as of now im using vbscript program to upload my xml,

here is my vbscript codings,

Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "provider=SQLOLEDB.1;data source=codevelopment;database=Johnny;uid=restorer;pwd="
objBL.ErrorLogFile = "c:\error.log"
objBL.Execute "c:\customermapping.xml", "c:\customers.xml"
Set objBL = Nothing

here is my customers.xml;

<ROOT>
<Customers CustomerId="1111" CompanyName="Test 2" City="NY" />
<Customers CustomerId="1234" CompanyName="Test 1" City="PH" />
<Customers CustomerId="1432" CompanyName="Test 3" City="ML" />
</ROOT>

and here is my xml schema;

<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" >

<ElementType name="ROOT" sql:is-constant="1">
<element type="Customers" />
</ElementType>

<ElementType name="Customers" sql:relation="Customer">
<element type="CustomerId" sql:field="CustomerId" />
<element type="CompanyName" sql:field="CompanyName" />
<element type="City" sql:field="City" />
</ElementType>

</Schema>

any help will be appreciated...

[johnnykenshien]

Sorry for my last post im using there vbscript, anyway here is my vb.net codes, please help us. thanks

Imports SQLXMLBULKLOADLib

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim objbl As New SQLXMLBulkLoad4

objbl.ConnectionString = "provider=SQLOLEDB.1;data source=codevelopment;database=Johnny;uid=restorer;pwd="
objbl.ErrorLogFile = "C:\error.log"
'objBL.KeepIdentity = False
objbl.Execute("c:\customermapping.xml", "c:\customers.xml")
objbl = Nothing
End Sub

End Class

here is my customers.xml

<ROOT>
<Customers CustomerId="1111" CompanyName="Sean Chai" City="NY" />
<Customers CustomerId="1234" CompanyName="Johntags" City="PH" />
<Customers CustomerId="1432" CompanyName="Test" City="ML" />
</ROOT>

here is my CustomerMapping xml

<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" >

<ElementType name="CustomerId" dt:type="int" />
<ElementType name="CompanyName" dt:type="string" />
<ElementType name="City" dt:type="string" />

<ElementType name="ROOT" sql:is-constant="1">
<element type="Customers" />
</ElementType>

<ElementType name="Customers" sql:relation="Customer">
<element type="CustomerId" sql:field="CustomerId" />
<element type="CompanyName" sql:field="CompanyName" />
<element type="City" sql:field="City" />
</ElementType>

</Schema>