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...

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>

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.