BulkLoad Problem

Anyone know why this will not work... when I run the script it just inserts
one blank row... No Data !
The Script...
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.3.0")
objBL.ConnectionString = "provider=SQLOLEDB;data
source=localhost;database=SqlXmlTest;integrated security=SSPI"
objBL.ErrorLogFile = "c:\error.log"
objBL.Execute "C:\Schema1.xml", "C:\Test.xml"
set objBL=Nothing
The Schema...
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Invoices" sql:relation="Invoices" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Invoice" type="xsd:string" />
<xsd:element name="Supplier" type="xsd:string" />
<xsd:element name="NetAmount" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
The Data...
<?xml version="1.0"?><Invoices><Invoice id="4F10OKGRR91" Supplier="501122"
NetAmount="52.17" /><Invoice id="4F10OPPRR41" Supplier="501122"
NetAmount="100.00" /><Invoice id="4F18LXLDE51" Supplier="176696"
NetAmount="5150.00" /><Invoice id="4F18LXXCJ72" Supplier="176696"
NetAmount="1000.00" /><Invoice id="4F18O9TJM31" Supplier="176580"
NetAmount="593.60" /><Invoice id="4F21JBLHK81" Supplier="100900"
NetAmount="17990.00" /><Invoice id="4F21K2SPU51" Supplier="100900"
NetAmount="449.50" /><Invoice id="4F21OXHKI11" Supplier="503972"
NetAmount="382.10" /><Invoice id="4F22IMTLO61" Supplier="176534"
NetAmount="669.86" /><Invoice id="4F22L1JCD54" Supplier="176596"
NetAmount="150.00" /><Invoice id="4F22L1OQF72" Supplier="176542"
NetAmount="4.66" /><Invoice id="4F22L1UQJ51" Supplier="176696"
NetAmount="1200.00" /><Invoice id="4F22L1VSM43" Supplier="176542"
NetAmount="20.00" /><Invoice id="4F23NIPEO42" Supplier="176696"
NetAmount="1200.00" /><Invoice id="4F23NIYCG21" Supplier="176542"
NetAmount="100.00" /><Invoice id="4F28PTYGJ51" Supplier="176696"
NetAmount="5150.00" /><Invoice id="4F28PUOST42" Supplier="503302"
NetAmount="105.00" /><Invoice id="4G01LBMTJ81" Supplier="176696"
NetAmount="" /><Invoice id="4H20KTFGH31" Supplier="948650"
NetAmount="3083.14" /><Invoice id="4H20KTFVC32" Supplier="948650"
NetAmount="22536.02" /><Invoice id="4H20NTSPE91" Supplier="503302"
NetAmount="107.10" /><Invoice id="4H23IVGMK51" Supplier="176157"
NetAmount="40.54" /><Invoice id="4H30JEIMH21" Supplier="948650"
NetAmount="68.66" /></Invoices>
The Table...
CREATE TABLE [dbo].[Invoices] (
[Invoice] [char] (50) COLLATE Latin1_General_BIN NULL ,
[Supplier] [char] (50) COLLATE Latin1_General_BIN NULL ,
[NetAmount] [char] (50) COLLATE Latin1_General_BIN NULL
) ON [PRIMARY]
Your schema doesn't actually describe your XML. Try something like this:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Invoices" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Invoice" sql:relation="Invoices" />
<xsd:complexType>
<xsd:attribute name="id" type="xsd:string" sql:field=
"Invoice"/>
<xsd:attribute name="Supplier" type="xsd:string" sql:field=
"Supplier"/>
<xsd:attribute name="NetAmount" type="xsd:string"
sql:field="NetAmount"/>
</xsd:complexType>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Rob C" <RobC@.discussions.microsoft.com> wrote in message
news:72538FD0-A75B-483F-A549-37C993C21432@.microsoft.com...
Anyone know why this will not work... when I run the script it just inserts
one blank row... No Data !
The Script...
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.3.0")
objBL.ConnectionString = "provider=SQLOLEDB;data
source=localhost;database=SqlXmlTest;integrated security=SSPI"
objBL.ErrorLogFile = "c:\error.log"
objBL.Execute "C:\Schema1.xml", "C:\Test.xml"
set objBL=Nothing
The Schema...
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Invoices" sql:relation="Invoices" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Invoice" type="xsd:string" />
<xsd:element name="Supplier" type="xsd:string" />
<xsd:element name="NetAmount" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
The Data...
<?xml version="1.0"?><Invoices><Invoice id="4F10OKGRR91" Supplier="501122"
NetAmount="52.17" /><Invoice id="4F10OPPRR41" Supplier="501122"
NetAmount="100.00" /><Invoice id="4F18LXLDE51" Supplier="176696"
NetAmount="5150.00" /><Invoice id="4F18LXXCJ72" Supplier="176696"
NetAmount="1000.00" /><Invoice id="4F18O9TJM31" Supplier="176580"
NetAmount="593.60" /><Invoice id="4F21JBLHK81" Supplier="100900"
NetAmount="17990.00" /><Invoice id="4F21K2SPU51" Supplier="100900"
NetAmount="449.50" /><Invoice id="4F21OXHKI11" Supplier="503972"
NetAmount="382.10" /><Invoice id="4F22IMTLO61" Supplier="176534"
NetAmount="669.86" /><Invoice id="4F22L1JCD54" Supplier="176596"
NetAmount="150.00" /><Invoice id="4F22L1OQF72" Supplier="176542"
NetAmount="4.66" /><Invoice id="4F22L1UQJ51" Supplier="176696"
NetAmount="1200.00" /><Invoice id="4F22L1VSM43" Supplier="176542"
NetAmount="20.00" /><Invoice id="4F23NIPEO42" Supplier="176696"
NetAmount="1200.00" /><Invoice id="4F23NIYCG21" Supplier="176542"
NetAmount="100.00" /><Invoice id="4F28PTYGJ51" Supplier="176696"
NetAmount="5150.00" /><Invoice id="4F28PUOST42" Supplier="503302"
NetAmount="105.00" /><Invoice id="4G01LBMTJ81" Supplier="176696"
NetAmount="" /><Invoice id="4H20KTFGH31" Supplier="948650"
NetAmount="3083.14" /><Invoice id="4H20KTFVC32" Supplier="948650"
NetAmount="22536.02" /><Invoice id="4H20NTSPE91" Supplier="503302"
NetAmount="107.10" /><Invoice id="4H23IVGMK51" Supplier="176157"
NetAmount="40.54" /><Invoice id="4H30JEIMH21" Supplier="948650"
NetAmount="68.66" /></Invoices>
The Table...
CREATE TABLE [dbo].[Invoices] (
[Invoice] [char] (50) COLLATE Latin1_General_BIN NULL ,
[Supplier] [char] (50) COLLATE Latin1_General_BIN NULL ,
[NetAmount] [char] (50) COLLATE Latin1_General_BIN NULL
) ON [PRIMARY]
|||Oops! Sorry, I meant:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Invoices" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Invoice" sql:relation="Invoices">
<xsd:complexType>
<xsd:attribute name="id" type="xsd:string" sql:field="Invoice"
/>
<xsd:attribute name="Supplier" type="xsd:string"
sql:field="Supplier" />
<xsd:attribute name="NetAmount" type="xsd:string"
sql:field="NetAmount" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:u0GX%23FYpEHA.3392@.TK2MSFTNGP15.phx.gbl...
Your schema doesn't actually describe your XML. Try something like this:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Invoices" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Invoice" sql:relation="Invoices" />
<xsd:complexType>
<xsd:attribute name="id" type="xsd:string" sql:field=
"Invoice"/>
<xsd:attribute name="Supplier" type="xsd:string" sql:field=
"Supplier"/>
<xsd:attribute name="NetAmount" type="xsd:string"
sql:field="NetAmount"/>
</xsd:complexType>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Rob C" <RobC@.discussions.microsoft.com> wrote in message
news:72538FD0-A75B-483F-A549-37C993C21432@.microsoft.com...
Anyone know why this will not work... when I run the script it just inserts
one blank row... No Data !
The Script...
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.3.0")
objBL.ConnectionString = "provider=SQLOLEDB;data
source=localhost;database=SqlXmlTest;integrated security=SSPI"
objBL.ErrorLogFile = "c:\error.log"
objBL.Execute "C:\Schema1.xml", "C:\Test.xml"
set objBL=Nothing
The Schema...
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Invoices" sql:relation="Invoices" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Invoice" type="xsd:string" />
<xsd:element name="Supplier" type="xsd:string" />
<xsd:element name="NetAmount" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
The Data...
<?xml version="1.0"?><Invoices><Invoice id="4F10OKGRR91" Supplier="501122"
NetAmount="52.17" /><Invoice id="4F10OPPRR41" Supplier="501122"
NetAmount="100.00" /><Invoice id="4F18LXLDE51" Supplier="176696"
NetAmount="5150.00" /><Invoice id="4F18LXXCJ72" Supplier="176696"
NetAmount="1000.00" /><Invoice id="4F18O9TJM31" Supplier="176580"
NetAmount="593.60" /><Invoice id="4F21JBLHK81" Supplier="100900"
NetAmount="17990.00" /><Invoice id="4F21K2SPU51" Supplier="100900"
NetAmount="449.50" /><Invoice id="4F21OXHKI11" Supplier="503972"
NetAmount="382.10" /><Invoice id="4F22IMTLO61" Supplier="176534"
NetAmount="669.86" /><Invoice id="4F22L1JCD54" Supplier="176596"
NetAmount="150.00" /><Invoice id="4F22L1OQF72" Supplier="176542"
NetAmount="4.66" /><Invoice id="4F22L1UQJ51" Supplier="176696"
NetAmount="1200.00" /><Invoice id="4F22L1VSM43" Supplier="176542"
NetAmount="20.00" /><Invoice id="4F23NIPEO42" Supplier="176696"
NetAmount="1200.00" /><Invoice id="4F23NIYCG21" Supplier="176542"
NetAmount="100.00" /><Invoice id="4F28PTYGJ51" Supplier="176696"
NetAmount="5150.00" /><Invoice id="4F28PUOST42" Supplier="503302"
NetAmount="105.00" /><Invoice id="4G01LBMTJ81" Supplier="176696"
NetAmount="" /><Invoice id="4H20KTFGH31" Supplier="948650"
NetAmount="3083.14" /><Invoice id="4H20KTFVC32" Supplier="948650"
NetAmount="22536.02" /><Invoice id="4H20NTSPE91" Supplier="503302"
NetAmount="107.10" /><Invoice id="4H23IVGMK51" Supplier="176157"
NetAmount="40.54" /><Invoice id="4H30JEIMH21" Supplier="948650"
NetAmount="68.66" /></Invoices>
The Table...
CREATE TABLE [dbo].[Invoices] (
[Invoice] [char] (50) COLLATE Latin1_General_BIN NULL ,
[Supplier] [char] (50) COLLATE Latin1_General_BIN NULL ,
[NetAmount] [char] (50) COLLATE Latin1_General_BIN NULL
) ON [PRIMARY]
|||Thank you ! I am very grateful for your help.
Cheers,
Rob
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:OomSYYYpEHA.3716@.TK2MSFTNGP10.phx.gbl...
> Oops! Sorry, I meant:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="Invoices" sql:is-constant="1">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="Invoice" sql:relation="Invoices">
> <xsd:complexType>
> <xsd:attribute name="id" type="xsd:string" sql:field="Invoice"
> />
> <xsd:attribute name="Supplier" type="xsd:string"
> sql:field="Supplier" />
> <xsd:attribute name="NetAmount" type="xsd:string"
> sql:field="NetAmount" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> Cheers,
> Graeme
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
>
> "Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
> news:u0GX%23FYpEHA.3392@.TK2MSFTNGP15.phx.gbl...
> Your schema doesn't actually describe your XML. Try something like this:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="Invoices" sql:is-constant="1" >
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="Invoice" sql:relation="Invoices" />
> <xsd:complexType>
> <xsd:attribute name="id" type="xsd:string" sql:field=
> "Invoice"/>
> <xsd:attribute name="Supplier" type="xsd:string" sql:field=
> "Supplier"/>
> <xsd:attribute name="NetAmount" type="xsd:string"
> sql:field="NetAmount"/>
> </xsd:complexType>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
>
> "Rob C" <RobC@.discussions.microsoft.com> wrote in message
> news:72538FD0-A75B-483F-A549-37C993C21432@.microsoft.com...
> Anyone know why this will not work... when I run the script it just
> inserts
> one blank row... No Data !
> The Script...
> set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.3.0")
> objBL.ConnectionString = "provider=SQLOLEDB;data
> source=localhost;database=SqlXmlTest;integrated security=SSPI"
> objBL.ErrorLogFile = "c:\error.log"
> objBL.Execute "C:\Schema1.xml", "C:\Test.xml"
> set objBL=Nothing
> The Schema...
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="Invoices" sql:relation="Invoices" >
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="Invoice" type="xsd:string" />
> <xsd:element name="Supplier" type="xsd:string" />
> <xsd:element name="NetAmount" type="xsd:string" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> The Data...
> <?xml version="1.0"?><Invoices><Invoice id="4F10OKGRR91" Supplier="501122"
> NetAmount="52.17" /><Invoice id="4F10OPPRR41" Supplier="501122"
> NetAmount="100.00" /><Invoice id="4F18LXLDE51" Supplier="176696"
> NetAmount="5150.00" /><Invoice id="4F18LXXCJ72" Supplier="176696"
> NetAmount="1000.00" /><Invoice id="4F18O9TJM31" Supplier="176580"
> NetAmount="593.60" /><Invoice id="4F21JBLHK81" Supplier="100900"
> NetAmount="17990.00" /><Invoice id="4F21K2SPU51" Supplier="100900"
> NetAmount="449.50" /><Invoice id="4F21OXHKI11" Supplier="503972"
> NetAmount="382.10" /><Invoice id="4F22IMTLO61" Supplier="176534"
> NetAmount="669.86" /><Invoice id="4F22L1JCD54" Supplier="176596"
> NetAmount="150.00" /><Invoice id="4F22L1OQF72" Supplier="176542"
> NetAmount="4.66" /><Invoice id="4F22L1UQJ51" Supplier="176696"
> NetAmount="1200.00" /><Invoice id="4F22L1VSM43" Supplier="176542"
> NetAmount="20.00" /><Invoice id="4F23NIPEO42" Supplier="176696"
> NetAmount="1200.00" /><Invoice id="4F23NIYCG21" Supplier="176542"
> NetAmount="100.00" /><Invoice id="4F28PTYGJ51" Supplier="176696"
> NetAmount="5150.00" /><Invoice id="4F28PUOST42" Supplier="503302"
> NetAmount="105.00" /><Invoice id="4G01LBMTJ81" Supplier="176696"
> NetAmount="" /><Invoice id="4H20KTFGH31" Supplier="948650"
> NetAmount="3083.14" /><Invoice id="4H20KTFVC32" Supplier="948650"
> NetAmount="22536.02" /><Invoice id="4H20NTSPE91" Supplier="503302"
> NetAmount="107.10" /><Invoice id="4H23IVGMK51" Supplier="176157"
> NetAmount="40.54" /><Invoice id="4H30JEIMH21" Supplier="948650"
> NetAmount="68.66" /></Invoices>
> The Table...
> CREATE TABLE [dbo].[Invoices] (
> [Invoice] [char] (50) COLLATE Latin1_General_BIN NULL ,
> [Supplier] [char] (50) COLLATE Latin1_General_BIN NULL ,
> [NetAmount] [char] (50) COLLATE Latin1_General_BIN NULL
> ) ON [PRIMARY]
>
>
>