Importing Xml to Sql Server using VB.NET


  • Hello,


    I have an xml document that I need to import into a sql server 2005 table. At the moment I am using the SQLXMLBulkLoad object to do this. (I am up for other/better suggestions to do this). I am having some problems. First let me layout the scenario. Below is a snippet of my xml file:








    <Description>Using 2,000 pieces for every 1</Description>











    <Description indent="1">4&quot; x 4&quot; per LF</Description>










    <Description indent="1">4&quot; x 6&quot; per LF</Description>











    The table has the following columns that all NULL except for the RowID column :

    1) RowID - Primary Key, Identity

    2) RecordId
      3) Description
      4) CrCode
      5) CrHours

    7) MPrice

    8) LPrice


    Below is the schema file to map this to my sql table:


    <Schema xmlns="urnTongue Tiedchemas-microsoft-com:xml-data"
            xmlnsBig Smilet="urnTongue Tiedchemas-microsoft-com:xmlBig Smileatatypes" 
            xmlnsTongue Tiedql="urnTongue Tiedchemas-microsoft-com:xml-sql" >


     <ElementType name="RecordId" dt:type="string" /> 
       <ElementType name="Description" dt:type="string" />
       <ElementType name="CrCode" dt:type="string" />
       <ElementType name="CrHours" dt:type="float" />
       <ElementType name="UnitOfMeasure" dt:type="string" />
       <ElementType name="MPrice" dt:type="float" />
       <ElementType name="LPrice" dt:type="float" />


       <ElementType name="CCB" sql:is-constant="1">
          <element type="HMLT" />


       <ElementType name="HMLT" sql:relation="mySqlTable">
          <element type="RecordId" sql:field="RecordId" />
          <element type="Description" sql:field="Description" />
          <element type="CrCode" sql:field="CrCode" />
          <element type="CrHours" sql:field="CrHours" />
          <element type="UnitOfMeasure" sql:field="UnitOfMeasure" />
          <element type="MPrice" sql:field="MPrice" />
          <element type="LPrice" sql:field="LPrice" />




    And here is the code snippet that I use:


    'Dim objBL As Object

    'objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")

    'objBL.ConnectionString = "provider=SQLOLEDB;data source=myServer;database=myDB;uid=sa;pwd=SaPwd;"

    'objBL.ErrorLogFile = "C:\error.log"

    'objBL.Execute("C:\mySchemaFile.xml", "C:\myXMLFile.xml")

    'objBL = Nothing


    This works great but the problem is I can't get the data for the sub-elements CrCode and CrHours. How can I change my schema to pull that data?


    I have another problem, but maybe it will be resolved once this is resolved.


    Any help would be appreciated!


    Monday, June 25, 2007 1:53 PM

All replies

  • not sure if you already know this but there is a separate forum on XML in SQL server -- there's a lot of expertise on bulk load there
    Saturday, June 28, 2008 4:58 AM
  • I am getting error " Can not create active x Component"  While Reading This

    Any help would be appreciated!


    Thursday, May 26, 2011 8:17 AM
  • hi,

    Not that much information. Please post a concise and complete example. Post the complete error message.

    As your subject contains VB.NET I assume that your code stops with an exception. At which point?

    You're error message may indicate a broken MSXML installation, when you're using it. Try to reinstall/repair it.

    Microsoft MVP Office Access
    Thursday, May 26, 2011 8:20 AM
  • Hi,

    Check this nice blog


    Saturday, January 21, 2012 5:30 AM