none
Importing Xml to Sql Server using VB.NET

    Question

  • 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:

     

    <CCB>

    <TEXT>

    ....

    </TEXT>

    <HMLT>

    <RecordId>123456</RecordId>

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

    <CrDetails>

        <CrCode>--</CrCode>

        <CrHours>0.000000</CrHours>

    </CrDetails>

    <UnitOfMeasure>CY</UnitOfMeasure>

    <Price>101.000000</Price>

    <LPrice>0.000000</LPrice>

    <EPrice>0.000000</EPrice>

    </HMLT>

    <HMLT>

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

    <CrDetails>

        <CrCode>B1</CrCode>

        <CrHours>0.110000</CrHours>

    </CrDetails>

    <UnitOfMeasure>LF</UnitOfMeasure>

    <Price>1.390000</Price>

    <LPrice>3.230000</LPrice>

    </HMLT>

    <HMLT>

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

    <CrDetails>

        <CrCode>B1</CrCode>

        <CrHours>0.120000</CrHours>

    </CrDetails>

    <UnitOfMeasure>LF</UnitOfMeasure>

    <Price>2.090000</Price>

    <LPrice>3.520000</LPrice>

    </HMLT>

    </CCB>

     

    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
      6)UnitOfMeasure

    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>

     

       <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" />

       </ElementType>

    </Schema>

     

    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!

    Thanks!

    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
    http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=89&SiteID=1
    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!

    Thanks!

    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
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Thursday, May 26, 2011 8:20 AM
  • Hi,

    Check this nice blog http://dvprez.blogspot.com/2009/10/importing-xml-into-sql-server-2008.html

     

    Saturday, January 21, 2012 5:30 AM