Need help with XML Bulk Load

I have a source xml file which I think is fairly complex. It s an EDIFACT
D96A INVRPT file.
I just need to bring the data within into a single table
CREATE TABLE InventoryOnHand (
InventoryDate nvarchar(10) DEFAULT (getdate()),
Barcode nvarchar(50),
Quantity float,
Warehouse nvarchar(50)
)
I've been fiddling with creating a schema to read the data, but I keep
getting various errors.
If anyone wants to help, let me know & I'll email you a sample source file
and my attempts at a schema. I'm getting desperate - I've spent so many
hours mucking around I'm at the end of my tether, so any assistance is
greatly appreciated!
cheers
DannyPlease send me your files and I'll take a look.
Regards,
--
Monica Frintu
"dc" wrote:

> I have a source xml file which I think is fairly complex. It s an EDIFACT
> D96A INVRPT file.
> I just need to bring the data within into a single table
> CREATE TABLE InventoryOnHand (
> InventoryDate nvarchar(10) DEFAULT (getdate()),
> Barcode nvarchar(50),
> Quantity float,
> Warehouse nvarchar(50)
> )
> I've been fiddling with creating a schema to read the data, but I keep
> getting various errors.
> If anyone wants to help, let me know & I'll email you a sample source file
> and my attempts at a schema. I'm getting desperate - I've spent so many
> hours mucking around I'm at the end of my tether, so any assistance is
> greatly appreciated!
> cheers
> Danny
>
>|||Monica,
Thanks for your offer. You can you ensure your email address is correct -
I'm getting NDRs.
cheers
Danny
dannyc@.accolade.com.au
"Monica Frintu [MSFT]" <MonicaFrintuMSFT@.discussions.microsoft.com> wrote in
message news:66FD8513-C76B-48B1-80AC-7BD50ED994F1@.microsoft.com...
> Please send me your files and I'll take a look.
> Regards,
> --
> Monica Frintu
>
> "dc" wrote:
>|||Monica,
This might help instead
My table is thus:
CREATE TABLE AA_InventoryOnHand (
InventoryDate nvarchar(10),
Barcode nvarchar(50) ,
Quantity float
)
My sample XML file is long, but copying to notepad and saving as
V3_INVRPT.XML should help someone to diagnose. Go down to the
============== line
<?xml version="1.0" encoding="UTF-8"?>
<EDIFACT_D96A_INVRPT xmlns="http://holoncorp.com/xml/EDIFACT/D96A/INVRPT"
xmlns:v3="http://holoncorp.com/xml/EDIFACT/D96A/INVRPT">
<UNB UNB010_0001_syntaxIndentifier="UNOA" UNB010_0002_syntaxVersion="3"
UNB020_0004_senderIdentification="VISA Sydney"
UNB020_0007_partnerIdentificationCodeQua
lifier="ZZ"
UNB030_0007_partnerIdentificationCodeQua
lifier="ZZ"
UNB030_0010_recipientIdentification="Barilla"
UNB040_0017_dateOfPreparation="070510" UNB040_0019_timeOfPreparation="1017"
UNB050_0020_interchangeControlReference=
"1020"/>
<INVRPT>
<UNH UNH010_0062_referenceNumber="0001" UNH020_0051_controllingAgency="UN"
UNH020_0052_versionNumber="D" UNH020_0054_releaseNumber="96A"
UNH020_0057_associationAssignedCode="EAN005"
UNH020_0065_typeIdentifier="INVRPT"/>
<BGM BGM010_1001_messageName="35" BGM020_1004_messageNumber="1020"
BGM030_1225_messageFunction="9"/>
<DTM DTM010_2005_dateTimePeriodQualifier="366"
DTM010_2379_dateTimePeriodFormatQualifie
r="102"
DTM010_2380_dateTimePeriod="20070510"/>
<GRP2>
<NAD NAD010_3035_partyQualifier="GY" NAD020_3039_partyIdIdentification="VISA
Sydney" NAD020_3055_codeListResponsibleAgency="86"/>
<GRP4>
<CTA CTA010_3139_contactFunctionCode="WH"
CTA020_3412_departmentOrEmployeeName="Jim Vikas"/>
<COM COM010_3148_communicationAddressIdentifi
er=""
COM020_3155_communicationAddressQualifie
r="TE"/>
<COM COM010_3148_communicationAddressIdentifi
er=""
COM020_3155_communicationAddressQualifie
r="EM"/>
</GRP4>
</GRP2>
<GRP2>
<NAD NAD010_3035_partyQualifier="GM"
NAD020_3039_partyIdIdentification="Cantarella"
NAD020_3055_codeListResponsibleAgency="86"/>
</GRP2>
<GRP9>
<LIN LIN030_7140_itemNumber="841158000029"
LIN030_7143_itemNumberTypeCode="EN"/>
<GRP12>
<INV INV040_4503_inventoryBalanceMethodCode="1"/>
<QTY QTY010_6060_quantity="144" QTY010_6063_quantityQualifier="17"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="170"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="253"
QTY010_6411_measurementUnitCode="EA"/>
</GRP12>
</GRP9>
<GRP9>
<LIN LIN030_7140_itemNumber="841158000043"
LIN030_7143_itemNumberTypeCode="EN"/>
<GRP12>
<INV INV040_4503_inventoryBalanceMethodCode="1"/>
<QTY QTY010_6060_quantity="166" QTY010_6063_quantityQualifier="17"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="2" QTY010_6063_quantityQualifier="170"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="253"
QTY010_6411_measurementUnitCode="EA"/>
</GRP12>
</GRP9>
<GRP9>
<LIN LIN030_7140_itemNumber="AZZURRA" LIN030_7143_itemNumberTypeCode="EN"/>
<GRP12>
<INV INV040_4503_inventoryBalanceMethodCode="1"/>
<QTY QTY010_6060_quantity="1000" QTY010_6063_quantityQualifier="17"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="30" QTY010_6063_quantityQualifier="170"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="253"
QTY010_6411_measurementUnitCode="EA"/>
</GRP12>
</GRP9>
<GRP9>
<LIN LIN030_7140_itemNumber="fab" LIN030_7143_itemNumberTypeCode="EN"/>
<GRP12>
<INV INV040_4503_inventoryBalanceMethodCode="1"/>
<QTY QTY010_6060_quantity="10" QTY010_6063_quantityQualifier="17"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="1" QTY010_6063_quantityQualifier="170"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="253"
QTY010_6411_measurementUnitCode="EA"/>
<GIN GIN010_7405_identityNumberQualifier="BX"
GIN020_7402_identityNumberRange="13"/>
</GRP12>
</GRP9>
<GRP9>
<LIN LIN030_7140_itemNumber="fab" LIN030_7143_itemNumberTypeCode="EN"/>
<GRP12>
<INV INV040_4503_inventoryBalanceMethodCode="1"/>
<QTY QTY010_6060_quantity="100" QTY010_6063_quantityQualifier="17"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="20" QTY010_6063_quantityQualifier="170"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="253"
QTY010_6411_measurementUnitCode="EA"/>
<GIN GIN010_7405_identityNumberQualifier="BX"
GIN020_7402_identityNumberRange="12"/>
</GRP12>
</GRP9>
<GRP9>
<LIN LIN030_7140_itemNumber="INSTANT" LIN030_7143_itemNumberTypeCode="EN"/>
<GRP12>
<INV INV040_4503_inventoryBalanceMethodCode="1"/>
<QTY QTY010_6060_quantity="1000" QTY010_6063_quantityQualifier="17"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="30" QTY010_6063_quantityQualifier="170"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="253"
QTY010_6411_measurementUnitCode="EA"/>
</GRP12>
</GRP9>
<GRP9>
<LIN LIN030_7140_itemNumber="KING OSCAR"
LIN030_7143_itemNumberTypeCode="EN"/>
<GRP12>
<INV INV040_4503_inventoryBalanceMethodCode="1"/>
<QTY QTY010_6060_quantity="1000" QTY010_6063_quantityQualifier="17"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="40" QTY010_6063_quantityQualifier="170"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="253"
QTY010_6411_measurementUnitCode="EA"/>
</GRP12>
</GRP9>
<GRP9>
<LIN LIN030_7140_itemNumber="sard brisling"
LIN030_7143_itemNumberTypeCode="EN"/>
<GRP12>
<INV INV040_4503_inventoryBalanceMethodCode="1"/>
<QTY QTY010_6060_quantity="1000" QTY010_6063_quantityQualifier="17"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="30" QTY010_6063_quantityQualifier="170"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="253"
QTY010_6411_measurementUnitCode="EA"/>
</GRP12>
</GRP9>
<UNT UNT010_0074_numberOfSegments="54" UNT020_0062_referenceNumber="0001"/>
</INVRPT>
<UNZ UNZ010_0020_interchangeControlReference=
"1020"
UNZ010_0036_interchangeControlCount="1"/>
</EDIFACT_D96A_INVRPT>
=======================
And here is the schema file I've written.
<?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="EDIFACT_D96A_INVRPT" sql:is-constant="1">
<element type="UNB" />
<element type="LIN" />
<element type="QTY" />
</ElementType>
<ElementType name="UNB" sql:relation="AA_InventoryOnHand" >
<AttributeType name="UNB040_0017_dateOfPreparation" dt:type="string"
/>
<attribute type="UNB040_0017_dateOfPreparation"
sql:field="InventoryDate" />
</ElementType>
<ElementType name="LIN" sql:relation="AA_InventoryOnHand" >
<AttributeType name="LIN030_7140_itemNumber" dt:type="string" />
<attribute type="LIN030_7140_itemNumber" sql:field="Barcode" />
</ElementType>
<ElementType name="QTY" sql:relation="AA_InventoryOnHand" >
<AttributeType name="QTY010_6060_quantity" dt:type="float" />
<attribute type="QTY010_6060_quantity" sql:field="Quantity" />
</ElementType>
</Schema>
==============================
And here's a vbs file I'm running
Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "provider=SQLOLEDB.1;data
source=DANNYCVM;database=MyDemoDB;uid=sa
;pwd=MysaPassword"
objBL.ErrorLogFile = "c:\error.log"
objBL.Execute "c:\Invmapping.xml", "c:\V3_INVRPT.xml"
Set objBL = Nothing
=============================
The table should be populated thus:
070510 841158000029 144
070510 841158000043 166
070510 841158000043 2
etc
"dc" <dannyc@.accoalde.com.au> wrote in message
news:uns4XWxkHHA.1624@.TK2MSFTNGP06.phx.gbl...
>I have a source xml file which I think is fairly complex. It s an EDIFACT
>D96A INVRPT file.
> I just need to bring the data within into a single table
> CREATE TABLE InventoryOnHand (
> InventoryDate nvarchar(10) DEFAULT (getdate()),
> Barcode nvarchar(50),
> Quantity float,
> Warehouse nvarchar(50)
> )
> I've been fiddling with creating a schema to read the data, but I keep
> getting various errors.
> If anyone wants to help, let me know & I'll email you a sample source file
> and my attempts at a schema. I'm getting desperate - I've spent so many
> hours mucking around I'm at the end of my tether, so any assistance is
> greatly appreciated!
> cheers
> Danny
>