#1
  1. Senior Water Wizard
    ASP Mastermind (5000+ posts)

    Join Date
    Aug 2004
    Location
    Cape Town, RSA
    Posts
    10,233
    Rep Power
    862

    SQLXMLBulkLoad: Data not importing


    Hi everyone, I need to use to SQLXMLBulkLoad to import loads of XML files, at the moment I have done some coding, its not giving any errors, its just not importing any data into the I_Album table.
    It stays empty after running the script?
    here is the table structure:
    Code:
    CREATE TABLE [I_Album] (
    	[AlbumID] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[MusicType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Title] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[KeyTitle] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[MainReleaseID] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[ProductFormAttributeID] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[p_MainGenreAttribute] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Performer] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[PerformerCount] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[OriginalReleaseDate] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Rank] [int] NULL ,
    	[Rating] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[n_Attributes] [int] NULL ,
    	[n_Associations] [int] NULL ,
    	[n_Releases] [int] NULL ,
    	[n_Tracks] [int] NULL ,
    	[n_Documents] [int] NULL ,
    	[r_Action] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
    ) ON [PRIMARY]
    GO
    the xsd file:
    Code:
    <?xml version="1.0" encoding="utf-8"?>
    <xsd:schema id="MData" xmlns="" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
    	elementFormDefault="qualified">
      <xsd:element name="MData" sql:is-constant="true">
        <xsd:complexType>
          <xsd:choice minOccurs="0" maxOccurs="unbounded">
            <xsd:element name="FileInfo">
              <xsd:complexType>
                <xsd:sequence>
                  <xsd:element name="Copyright" type="xsd:string" minOccurs="0" />
                </xsd:sequence>
              </xsd:complexType>
            </xsd:element>
            <xsd:element name="MusicAlbums">
              <xsd:complexType>
                <xsd:sequence>
                  <xsd:element name="MusicAlbum" minOccurs="0" maxOccurs="unbounded" sql:relation="I_Album">
                    <xsd:complexType>
                      <xsd:sequence>
                        <xsd:element name="AlbumID" type="xsd:string" minOccurs="0" sql:field="AlbumID" />
                        <xsd:element name="Type" type="xsd:string" minOccurs="0" sql:field="Type" />
                        <xsd:element name="MusicType" type="xsd:string" minOccurs="0" sql:field="MusicType" />
                        <xsd:element name="Title" type="xsd:string" minOccurs="0" sql:field="Title" />
                        <xsd:element name="KeyTitle" type="xsd:string" minOccurs="0" sql:field="KeyTitle" />
                        <xsd:element name="MainReleaseID" type="xsd:string" minOccurs="0" sql:field="MainReleaseID" />
                        <xsd:element name="ProductFormAttributeID" type="xsd:string" minOccurs="0" sql:field="ProductFormAttributeID" />
                        <xsd:element name="p_MainGenreAttributeID" type="xsd:string" minOccurs="0" sql:field="p_MainGenreAttribute" />
                        <xsd:element name="Performer" type="xsd:string" minOccurs="0" sql:field="Performer" />
                        <xsd:element name="PerformerCount" type="xsd:string" minOccurs="0" sql:field="PerformerCount" />
                        <xsd:element name="OriginalReleaseDate" type="xsd:string" minOccurs="0" sql:field="OriginalReleaseDate" />
                        <xsd:element name="Rank" type="xsd:string" minOccurs="0" sql:field="Rank" />
                        <xsd:element name="Rating" type="xsd:string" minOccurs="0" sql:field="Rating" />
                        <xsd:element name="n_Attributes" type="xsd:string" minOccurs="0" sql:field="n_Attributes" />
                        <xsd:element name="n_Associations" type="xsd:string" minOccurs="0" sql:field="n_Associations" />
                        <xsd:element name="n_Releases" type="xsd:string" minOccurs="0" sql:field="n_Releases" />
                        <xsd:element name="n_Tracks" type="xsd:string" minOccurs="0" sql:field="n_Tracks" />
                        <xsd:element name="n_Documents" type="xsd:string" minOccurs="0" sql:field="n_Documents" />
                        <xsd:element name="Action" type="xsd:string" minOccurs="0" sql:field="r_Action" />
                      </xsd:sequence>
                    </xsd:complexType>
                  </xsd:element>
                </xsd:sequence>
              </xsd:complexType>
            </xsd:element>
          </xsd:choice>
        </xsd:complexType>
      </xsd:element>
    </xsd:schema>
    and a short extract of the XML
    Code:
    <?xml version="1.0" encoding="utf-8"?>
    <MData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <FileInfo>
    <Copyright>xxxxx</Copyright>
    </FileInfo>
    <MusicAlbums>
    <MusicAlbum>
      <AlbumID>MW0000000109</AlbumID>
      <Type>A</Type>
      <MusicType>P</MusicType>
      <Title>In a Dancing Mood</Title>
      <KeyTitle>In a Dancing Mood</KeyTitle>
      <MainReleaseID>MR0000904322</MainReleaseID>
      <ProductFormAttributeID>MA0000001928</ProductFormAttributeID>
      <p_MainGenreAttributeID>MA0000002674</p_MainGenreAttributeID>
      <Performer>Dave Brubeck</Performer>
      <PerformerCount>1</PerformerCount>
      <OriginalReleaseDate>2001-??-??</OriginalReleaseDate>
      <Rank>0</Rank>
      <Rating> </Rating>
      <n_Attributes>5</n_Attributes>
      <n_Associations>3</n_Associations>
      <n_Releases>1</n_Releases>
      <n_Tracks>2</n_Tracks>
      <n_Documents>0</n_Documents>
      <Action>A</Action>
    </MusicAlbum>
    <MusicAlbum>
      <AlbumID>MW0000000213</AlbumID>
      <Type>A</Type>
      <MusicType>P</MusicType>
      <Title>Take Five</Title>
      <KeyTitle>Take Five</KeyTitle>
      <MainReleaseID>MR0000904151</MainReleaseID>
      <ProductFormAttributeID>MA0000001928</ProductFormAttributeID>
      <p_MainGenreAttributeID>MA0000002674</p_MainGenreAttributeID>
      <Performer>Dave Brubeck</Performer>
      <PerformerCount>1</PerformerCount>
      <OriginalReleaseDate>2001-??-??</OriginalReleaseDate>
      <Rank>6</Rank>
      <Rating> </Rating>
      <n_Attributes>4</n_Attributes>
      <n_Associations>1</n_Associations>
      <n_Releases>1</n_Releases>
      <n_Tracks>8</n_Tracks>
      <n_Documents>0</n_Documents>
      <Action>A</Action>
    </MusicAlbum>
    </MusicAlbums>
    </MData>
    and this is the script
    Code:
    Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
    	objBL.Execute "D:\xxxxx\Music\Album_Map.xsd", "D:\xxxxx\Music\Album.xml"
    thanks for any help you can provide, this is the first time I am working with this, have NO clue why its not importing.

    Thanks
    NF

    PS. I have set the ErrorLogFile property, but there is no file created.
    Last edited by nofriends; March 11th, 2009 at 05:22 AM.
    Look! Its a ShemZilla


  2. #2
  3. Senior Water Wizard
    ASP Mastermind (5000+ posts)

    Join Date
    Aug 2004
    Location
    Cape Town, RSA
    Posts
    10,233
    Rep Power
    862
    anyone worked with this before?
  4. #3
  5. Contributing User
    ASP High Scholar (3500 - 3999 posts)

    Join Date
    Jul 2005
    Location
    Oxford UK
    Posts
    3,897
    Rep Power
    992
    hi
    i modified your xsd like this
    Code:
    <?xml version="1.0" encoding="utf-8"?>
    <xsd:schema id="MData" xmlns="" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema" elementFormDefault="qualified">
    	<xsd:element name="MusicAlbum" sql:relation="I_Album">
    		<xsd:complexType>
    			<xsd:sequence>
    				<xsd:element name="AlbumID" type="xsd:string"/>
    				<xsd:element name="Type" type="xsd:string"/>
    				<xsd:element name="MusicType" type="xsd:string"/>
    				<xsd:element name="Title" type="xsd:string"/>
    				<xsd:element name="KeyTitle" type="xsd:string"/>
    				<xsd:element name="MainReleaseID" type="xsd:string"/>
    				<xsd:element name="ProductFormAttributeID" type="xsd:string"/>
    				<xsd:element name="p_MainGenreAttributeID" type="xsd:string" sql:field="p_MainGenreAttribute"/>
    				<xsd:element name="Performer" type="xsd:string"/>
    				<xsd:element name="PerformerCount" type="xsd:string"/>
    				<xsd:element name="OriginalReleaseDate" type="xsd:string"/>
    				<xsd:element name="Rank" type="xsd:string"/>
    				<xsd:element name="Rating" type="xsd:string"/>
    				<xsd:element name="n_Attributes" type="xsd:string"/>
    				<xsd:element name="n_Associations" type="xsd:string"/>
    				<xsd:element name="n_Releases" type="xsd:string"/>
    				<xsd:element name="n_Tracks" type="xsd:string"/>
    				<xsd:element name="n_Documents" type="xsd:string"/>
    				<xsd:element name="Action" type="xsd:string" sql:field="r_Action"/>
    			</xsd:sequence>
    		</xsd:complexType>
    	</xsd:element>
    </xsd:schema>

    and modified xml like this
    Code:
    <?xml version="1.0" encoding="utf-8"?>
    <MusicAlbums>
    <MusicAlbum>
      <AlbumID>MW0000000109</AlbumID>
      <Type>A</Type>
      <MusicType>P</MusicType>
      <Title>In a Dancing Mood</Title>
      <KeyTitle>In a Dancing Mood</KeyTitle>
      <MainReleaseID>MR0000904322</MainReleaseID>
      <ProductFormAttributeID>MA0000001928</ProductFormAttributeID>
      <p_MainGenreAttributeID>MA0000002674</p_MainGenreAttributeID>
      <Performer>Dave Brubeck</Performer>
      <PerformerCount>1</PerformerCount>
      <OriginalReleaseDate>2001-??-??</OriginalReleaseDate>
      <Rank>0</Rank>
      <Rating> </Rating>
      <n_Attributes>5</n_Attributes>
      <n_Associations>3</n_Associations>
      <n_Releases>1</n_Releases>
      <n_Tracks>2</n_Tracks>
      <n_Documents>0</n_Documents>
      <Action>A</Action>
    </MusicAlbum>
    <MusicAlbum>
      <AlbumID>MW0000000213</AlbumID>
      <Type>A</Type>
      <MusicType>P</MusicType>
      <Title>Take Five</Title>
      <KeyTitle>Take Five</KeyTitle>
      <MainReleaseID>MR0000904151</MainReleaseID>
      <ProductFormAttributeID>MA0000001928</ProductFormAttributeID>
      <p_MainGenreAttributeID>MA0000002674</p_MainGenreAttributeID>
      <Performer>Dave Brubeck</Performer>
      <PerformerCount>1</PerformerCount>
      <OriginalReleaseDate>2001-??-??</OriginalReleaseDate>
      <Rank>6</Rank>
      <Rating> </Rating>
      <n_Attributes>4</n_Attributes>
      <n_Associations>1</n_Associations>
      <n_Releases>1</n_Releases>
      <n_Tracks>8</n_Tracks>
      <n_Documents>0</n_Documents>
      <Action>A</Action>
    </MusicAlbum>
    </MusicAlbums>
    and modified vbs code like this .after all this its working at my end.
    Code:
    Set objBL =Server.CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
    objBL.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=;password=;Initial Catalog=dbname;Data Source=your database source"
    
    objBL.Execute "D:\xxxxx\Music\Album_Map.xsd", "D:\xxxxx\Music\Album.xml"

    Comments on this post

    • nofriends agrees : thank you!!!
    • micky agrees
    Last edited by Guddu; March 11th, 2009 at 01:26 PM. Reason: u can add your sql:field if column is different in your table against to schema
    Nothing is Impossible bcoz IMPOSSIBLE itself says..
    I M POSSIBLE........................
    Be cool !!!!!!!!
  6. #4
  7. Senior Water Wizard
    ASP Mastermind (5000+ posts)

    Join Date
    Aug 2004
    Location
    Cape Town, RSA
    Posts
    10,233
    Rep Power
    862
    Guddu, you are my hero

    all I need to do was remove the two levels of unnecessary XSD Elements for MData and MusicAlbums, didn't change anything else and it worked like a dream.

    Thanks a million!!!!

    Comments on this post

    • micky agrees
  8. #5
  9. Contributing User
    ASP High Scholar (3500 - 3999 posts)

    Join Date
    Jul 2005
    Location
    Oxford UK
    Posts
    3,897
    Rep Power
    992
    glad your problem solved NF.

    cheers!!

Similar Threads

  1. Importing data from website
    By bdhtexas in forum Programming Help
    Replies: 0
    Last Post: August 5th, 2006, 07:17 PM
  2. Mail Merge with Access table as data source - PLEASE HELP
    By Jeronimo6972 in forum Microsoft Access Help
    Replies: 0
    Last Post: March 10th, 2005, 08:01 PM
  3. importing online data in local database
    By umeshgaikwad in forum Microsoft SQL Server
    Replies: 1
    Last Post: September 16th, 2004, 10:01 AM
  4. Replies: 3
    Last Post: April 22nd, 2004, 09:53 AM
  5. Importing Data
    By stevie17 in forum Microsoft SQL Server
    Replies: 2
    Last Post: April 2nd, 2004, 05:35 AM

IMN logo majestic logo threadwatch logo seochat tools logo